Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 3:48 PM, Merlin Moncure wrote: > On Tue, Dec 2, 2014 at 12:40 PM, Nelson Green > wrote: > > In the line: IF LOWER(_unit) NOT IN (SELECT interval_unit ..., I would > > rather > > query a catalog table for the interval unit names if possible. That would > > then > > compensate for any changes to those values in the future. > > > > When I meant do this in C, I was referring to rewriting this function in > C > > instead of Pl/pgSQL. > > > > I hope this helps you understand what I am asking, and apologies for not > > being > > more specific up front. > > I was the one that was confused -- heh. I mis-understood the original > email and thought you were trying to validate interval output vs > interval input. > > merlin > But you took time to work with me, and I appreciate that. Thanks, Nelson
Re: [GENERAL] [Solved] Programmatic access to interval units
On Tue, Dec 2, 2014 at 2:25 PM, Adrian Klaver
wrote:
> On 12/02/2014 10:40 AM, Nelson Green wrote:
>
>> On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure > <mailto:mmonc...@gmail.com>> wrote:
>>
>
>
>> Hi Merlin,
>>
>> I'm afraid I'm only confusing things, so let me give an example of what I
>> am
>> trying to do:
>>
>> -- Example
>>
>> CREATE OR REPLACE FUNCTION check_interval(_period TEXT, _unit TEXT)
>> RETURNS INTERVAL
>> AS $$
>> DECLARE
>>_DEFAULT_INTERVAL INTERVAL:= '1 HOUR';
>>
>>BEGIN
>> -- Create a temporary table that maintains the time intervals:
>> CREATE TEMPORARY TABLE interval_period
>> (
>> interval_unitTEXT NOT NULL
>> );
>>
>> INSERT INTO interval_period
>> VALUES
>> ('microsecond'),
>> ('microseconds'),
>> ('millisecond'),
>> ('milliseconds'),
>> ('second'),
>> ('seconds'),
>> ('minute'),
>> ('minutes'),
>> ('hour'),
>> ('hours'),
>> ('day'),
>> ('days'),
>> ('week'),
>> ('weeks'),
>> ('month'),
>> ('months'),
>> ('year'),
>> ('years'),
>> ('decade'),
>> ('decades'),
>> ('century'),
>> ('centurys'),
>> ('millennium'),
>> ('millenniums');
>>
>> IF _period !~ '[1-9]\d*'
>> THEN
>> DROP TABLE interval_period;
>> RETURN _DEFAULT_INTERVAL;
>> END IF;
>>
>> IF LOWER(_unit) NOT IN (SELECT interval_unit
>> FROM interval_period)
>> THEN
>> DROP TABLE interval_period;
>> RETURN _DEFAULT_INTERVAL;
>> END IF;
>>
>> DROP TABLE interval_period;
>> RETURN CAST(CONCAT(_period, _unit) AS INTERVAL);
>>
>>END;
>> $$
>> LANGUAGE PLPGSQL;
>> -- End Example
>>
>>
>> In the line: IF LOWER(_unit) NOT IN (SELECT interval_unit ..., I would
>> rather
>> query a catalog table for the interval unit names if possible. That
>> would then
>> compensate for any changes to those values in the future.
>>
>> When I meant do this in C, I was referring to rewriting this function in C
>> instead of Pl/pgSQL.
>>
>> I hope this helps you understand what I am asking, and apologies for not
>> being
>> more specific up front.
>>
>
> Would it not be easier to just try the CAST and then catch the exception
> and handle it:
>
> http://www.postgresql.org/docs/9.3/interactive/plpgsql-
> control-structures.html#PLPGSQL-ERROR-TRAPPING
>
Thanks Adrian, for putting my head back on straight.
Not only would that be at least as easy, I have done similar error trapping
in
other functions. Not to sure how I got off on this tangent and then stuck
with
it. Guess I was trying to make this way harder than it needed to be, or I
had
way too much turkey over the past holiday?
And a big thanks to everyone that took time to work with me too.
Regards,
Nelson
>
>
>> Regards,
>> Nelson
>>
>> merlin
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure wrote:
> On Tue, Dec 2, 2014 at 9:48 AM, Nelson Green
> wrote:
> > On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure
> wrote:
> >> On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green
> >> wrote:
> >> > Good morning list,
> >> >
> >> > According to the documentation for interval data type inputs, the unit
> >> > can
> >> > be one of microsecond, millisecond, second, minute, hour, day, week,
> >> > month,
> >> > year, decade, century, or millennium. Are these units stored in a
> >> > catalog
> >> > somewhere? I would like to access them programmatically if possible,
> to
> >> > validate input for a function I am developing.
> >>
> >> if you're writing C, you can use libpqtypes to do this. It exposes the
> >> interval as a C structure.
> >>
> >> typedef struct
> >> {
> >> int years;
> >> int mons;
> >> int days;
> >> int hours;
> >> int mins;
> >> int secs;
> >> int usecs;
> >> } PGinterval;
> >>
> >
> > Thanks Merlin. I am not writing this in C, rather I am using Pl/pgSQL.
> > Apologies
> > for not mentioning that up front. I was hoping to do a SELECT ... WHERE
> IN
> > query form a catalog relation.
> >
> > That being said, maybe it is time for me to get back into C? I haven't
> done
> > much
>
> well, maybe: that's a different question. I wasn't sure what exactly
> you wanted to verify and how.
Hi Merlin,
I'm afraid I'm only confusing things, so let me give an example of what I am
trying to do:
-- Example
CREATE OR REPLACE FUNCTION check_interval(_period TEXT, _unit TEXT)
RETURNS INTERVAL
AS $$
DECLARE
_DEFAULT_INTERVAL INTERVAL:= '1 HOUR';
BEGIN
-- Create a temporary table that maintains the time intervals:
CREATE TEMPORARY TABLE interval_period
(
interval_unitTEXT NOT NULL
);
INSERT INTO interval_period
VALUES
('microsecond'),
('microseconds'),
('millisecond'),
('milliseconds'),
('second'),
('seconds'),
('minute'),
('minutes'),
('hour'),
('hours'),
('day'),
('days'),
('week'),
('weeks'),
('month'),
('months'),
('year'),
('years'),
('decade'),
('decades'),
('century'),
('centurys'),
('millennium'),
('millenniums');
IF _period !~ '[1-9]\d*'
THEN
DROP TABLE interval_period;
RETURN _DEFAULT_INTERVAL;
END IF;
IF LOWER(_unit) NOT IN (SELECT interval_unit
FROM interval_period)
THEN
DROP TABLE interval_period;
RETURN _DEFAULT_INTERVAL;
END IF;
DROP TABLE interval_period;
RETURN CAST(CONCAT(_period, _unit) AS INTERVAL);
END;
$$
LANGUAGE PLPGSQL;
-- End Example
In the line: IF LOWER(_unit) NOT IN (SELECT interval_unit ..., I would
rather
query a catalog table for the interval unit names if possible. That would
then
compensate for any changes to those values in the future.
When I meant do this in C, I was referring to rewriting this function in C
instead of Pl/pgSQL.
I hope this helps you understand what I am asking, and apologies for not
being
more specific up front.
Regards,
Nelson
merlin
>
Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 10:16 AM, Melvin Davidson
wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *I'm pretty sure the interval values are buried in the code, but there is
> nothing to prevent you from creating your own reference table. :) CREATE
> TABLE time_intervals( time_interval_name varchar(15) NOT NULL,
> CONSTRAINT time_intervals_pk PRIMARY KEY (time_interval_name));INSERT INTO
> time_intervalsVALUES('microsecond'),('millisecond'),('second'),('minute'),('hour'),('day'),('week'),('month'),('year'),('decade'),('century'),('millennium');*
>
> *SELECT * FROM time_intervals;*
>
Thanks Melvin,
Actually I've already hard-coded a temporary table into the function so
that I
can move forward with the development, but wanted to make that part more
dynamic, which is what prompted my first question.
Regards,
Nelson
>
> On Tue, Dec 2, 2014 at 10:48 AM, Nelson Green
> wrote:
>
>> On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure
>> wrote:
>>
>>> On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green
>>> wrote:
>>> > Good morning list,
>>> >
>>> > According to the documentation for interval data type inputs, the unit
>>> can
>>> > be one of microsecond, millisecond, second, minute, hour, day, week,
>>> month,
>>> > year, decade, century, or millennium. Are these units stored in a
>>> catalog
>>> > somewhere? I would like to access them programmatically if possible, to
>>> > validate input for a function I am developing.
>>>
>>> if you're writing C, you can use libpqtypes to do this. It exposes the
>>> interval as a C structure.
>>>
>>> typedef struct
>>> {
>>> int years;
>>> int mons;
>>> int days;
>>> int hours;
>>> int mins;
>>> int secs;
>>> int usecs;
>>> } PGinterval;
>>>
>>>
>>> merlin
>>>
>>
>> Thanks Merlin. I am not writing this in C, rather I am using Pl/pgSQL.
>> Apologies
>> for not mentioning that up front. I was hoping to do a SELECT ... WHERE IN
>> query form a catalog relation.
>>
>> That being said, maybe it is time for me to get back into C? I haven't
>> done much
>> in C in many years, but this simple validation function might not be a bad
>> jumping off point. If I do not get the response I was hoping for I may
>> just do
>> that.
>>
>> Regards,
>> Nelson
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
Re: [GENERAL] Programmatic access to interval units
On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure wrote:
> On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green
> wrote:
> > Good morning list,
> >
> > According to the documentation for interval data type inputs, the unit
> can
> > be one of microsecond, millisecond, second, minute, hour, day, week,
> month,
> > year, decade, century, or millennium. Are these units stored in a catalog
> > somewhere? I would like to access them programmatically if possible, to
> > validate input for a function I am developing.
>
> if you're writing C, you can use libpqtypes to do this. It exposes the
> interval as a C structure.
>
> typedef struct
> {
> int years;
> int mons;
> int days;
> int hours;
> int mins;
> int secs;
> int usecs;
> } PGinterval;
>
>
> merlin
>
Thanks Merlin. I am not writing this in C, rather I am using Pl/pgSQL.
Apologies
for not mentioning that up front. I was hoping to do a SELECT ... WHERE IN
query form a catalog relation.
That being said, maybe it is time for me to get back into C? I haven't done
much
in C in many years, but this simple validation function might not be a bad
jumping off point. If I do not get the response I was hoping for I may just
do
that.
Regards,
Nelson
[GENERAL] Programmatic access to interval units
Good morning list, According to the documentation for interval data type inputs, the unit can be one of microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, or millennium. Are these units stored in a catalog somewhere? I would like to access them programmatically if possible, to validate input for a function I am developing. Thanks, Nelson
Re: [GENERAL] password in recovery.conf [SOLVED]
On Fri, Sep 26, 2014 at 6:40 PM, John R Pierce wrote: > On 9/26/2014 4:32 PM, Nelson Green wrote: > >> >> Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became >> obvious >> that I was doing something wrong. And yes, in the end you were right. >> Doubling >> the quote does indeed work. >> >> It turns out it this particular password also had a \ in it, and my >> console >> width wrapped right before it, putting it as the first character on the >> next >> line, where I just didn't notice it until a few minutes ago. I changed >> that to >> a ^ for the time being, and then doubled the quote whereupon it all >> worked. I >> will certainly look into how to escape the backslash too, but that's for >> next >> week at this point. >> > > I'd consider using `mkpasswd -l 15 -s 0` just to avoid any such > problems. 15 random alphanumerics is already plenty complex, 62^15th > possible combinations, without needing to mix in special characters. > > $ mkpasswd -l 15 -s 0 > eec1kj7ZsthlYmh > Thanks John. We use apg which has similar options. But alas, I must comply with organizational password policies. Regards, Nelson
Re: [GENERAL] password in recovery.conf [SOLVED]
On Fri, Sep 26, 2014 at 6:46 PM, Adrian Klaver wrote: > On 09/26/2014 04:32 PM, Nelson Green wrote: > >> On Fri, Sep 26, 2014 at 5:51 PM, Adrian Klaver >> > > Doubling the quote seems to work here. >> >> >> Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became >> obvious >> that I was doing something wrong. And yes, in the end you were right. >> Doubling >> the quote does indeed work. >> >> It turns out it this particular password also had a \ in it, and my >> console >> width wrapped right before it, putting it as the first character on the >> next >> line, where I just didn't notice it until a few minutes ago. I changed >> that to >> a ^ for the time being, and then doubled the quote whereupon it all >> worked. I >> will certainly look into how to escape the backslash too, but that's for >> next >> week at this point. >> > > aklaver@panda:~> psql 'dbname=test user=test_user password=test\\pwd' > psql (9.0.17) > Type "help" for help. > > test=> Thanks again Adrian! Figures it's that easy. Confession time. When I'm trying to work through something like this where different iterations are going to be tried, I sit down and spell them out first. But since I was remoted in and things were going so slow (and I was pretty tired), I just tried different combinations on the single quote. When I noticed the backslash I tried to double it, but with no luck. However, in all honesty I don't know what I was doing with the single quote at that particular moment. Bottom line is I probably shot myself in the foot in several ways with this one. I appreciate the patience with me. Nelson
Re: [GENERAL] password in recovery.conf [SOLVED]
On Fri, Sep 26, 2014 at 5:51 PM, Adrian Klaver wrote: > On 09/26/2014 12:58 PM, Nelson Green wrote: > >> Hello all, >> >> I am setting up a streaming replication stand-by, and the replication >> role password has a single quote in it. I am unable to properly >> reference the password in the conninfo setting of recovery.conf so it >> will authenticate to the master. Doubling the quote gives me a syntax >> error, and escaping it or quoting it with double-quotes gives me an >> authentication error. The password is correct because I can copy it from >> the recovery.conf and supply it when prompted by pg_basebackup, so if I >> may, what is the proper way to handle single quotes within the conninfo >> string? >> > > > Doubling the quote seems to work here. > Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became obvious that I was doing something wrong. And yes, in the end you were right. Doubling the quote does indeed work. It turns out it this particular password also had a \ in it, and my console width wrapped right before it, putting it as the first character on the next line, where I just didn't notice it until a few minutes ago. I changed that to a ^ for the time being, and then doubled the quote whereupon it all worked. I will certainly look into how to escape the backslash too, but that's for next week at this point. Apologies for the noise. Just been one of those days. Thanks, Nelson
[GENERAL] password in recovery.conf
Hello all, I am setting up a streaming replication stand-by, and the replication role password has a single quote in it. I am unable to properly reference the password in the conninfo setting of recovery.conf so it will authenticate to the master. Doubling the quote gives me a syntax error, and escaping it or quoting it with double-quotes gives me an authentication error. The password is correct because I can copy it from the recovery.conf and supply it when prompted by pg_basebackup, so if I may, what is the proper way to handle single quotes within the conninfo string? Obviously I can change the password, but we use an automated password generator so I'd like to not have to keep generating passwords, and checking them, until I get one that will work, unless that my only option. Thanks, Nelson
Re: [GENERAL] Employee modeling question
On Fri, Sep 5, 2014 at 11:39 AM, Rich Shepard wrote: > On Fri, 5 Sep 2014, John McKown wrote: > > They are excellent. They are _not_ for beginners. The "For Smarties" >> portion is not just a play against the "For Dummies" series. Joe does some >> high powered SQL. >> > > For the purpose of developing an employee schema with departments for > some, his "SQL For Smarties" provides very sound advice on how to proceed. > Having separate company, department, and employee tables is a given. But, > you might need many-to-many tables to keep track of the complex > relationships. This is all covered in the chapters on DDL (Data Definition > Language) and is separate from the chapters on DML (Data Manipulation > Language). > > Good luck, > > Rich > Thank you Rich, and apologies for the delay in getting back to this. Sometimes my job has a bad habit of getting in the way of getting work done. I've been through the first four or five chapters of the SQL For Smarties book, and I've glanced at the other two books we have, but I didn't find anything especially enlightening (and I was surprised at the number of typographical errors in the content). I have also read through the other references I was given. Although I have not completely hashed this whole situation out, I am leaning towards an exclusivity constraint on department and business, where one of the columns will be required to be null, and a check constraint on the business column that will not allow businesses that are referenced in the department table. This seems to meet all of my rules and requirements, and will also work in the case of external contracts applying to a business or a department. If this plan changes dramatically I will update this posting, and I do appreciate the advice that I received from you and everyone else. I especially appreciate being given pointers to information sources as opposed to receiving pat answers without explanations. Reading and learning will prove much more beneficial in the long run. Well, back to work. Gotta go explain to someone why two separate and unrelated tables won't model their multi step workflow too well (OK not at all really). I just love how people that can populate a spreadsheet think that makes them into data professionals. Nelson > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Employee modeling question
On Fri, Sep 5, 2014 at 9:46 AM, John McKown wrote: > On Fri, Sep 5, 2014 at 7:52 AM, Nelson Green > wrote: > > > > Thanks Robin. Ironically enough, our little local library has three > books by > > Joe Celko, so looks like I may have a weekend of reading ahead of me. > > I'm impressed that your library has him. But, unless you are _very_ > good with SQL, you're going to need more than a single weekend. Unless > by weekend you mean 48 hours of constant reading. I have a lot of his > books. They are excellent. They are _not_ for beginners. The "For > Smarties" portion is not just a play against the "For Dummies" > series. Joe does some high powered SQL. I am not, and never really > will be, an SQL expert. I love his books. But, at least for me, they > are an intense read. > > > > > Nelson > > > -- > There is nothing more pleasant than traveling and meeting new people! > Genghis Khan > > Maranatha! <>< > John McKown > Thanks for the warning John. No, I hold no illusion that I will completely solve this problem through two days of reading. What I meant is that I will have two days away from work to get some work done. What I hope to have by the start of next week is a better idea of what I am up against. I am glad that I realized this simple problem will require a complex solution before I got too far along, and I really appreciate the learning opportunities that have been offered here. Nelson
Re: [GENERAL] Employee modeling question
> > > I could store the department and business attributes with the >> > employee, but without proper constraints the referenced department >> > could conceivably not correspond to the referenced business. Or I >> > could ensure that all businesses have at least one department, >> > defaulting to the business when the business has no department, but >> > then I'd be storing duplicate data. The other alternative I've come up >> > with is an exclusive constraint where the employee instance can only >> > reference a department or a business, but not both. >> > >> > None of these solutions seems ideal, although the exclusivity solution >> > seems like it would work the best, and I have had to create >> > exclusivity constraints before. So, am I missing a more obvious >> > solution, or am I even on track here? >> >> I’ve found this « Universal Person and Organization Data Model » very >> useful to understand complex questions like that: >> http://www.tdan.com/view-articles/5014 >> >> Hope this helps, >> François Beausoleil >> >> > Thanks François. This is pretty generic stuff, but my first reading has > got me thinking that I should at least pick up some ideas from it. I will > give it a go. And I've got a new web site that I've now known about before, > so thanks for that as well. > > Regards, > Nelson > > > I strongly suggest you read the writings of Joe Celko, he has been > addressing this kind of problem for decades. Ultimately, it is very > important not to confuse behaviour with entities (yes employment is > behavioural) > > Cheers > Thanks Robin. Ironically enough, our little local library has three books by Joe Celko, so looks like I may have a weekend of reading ahead of me. Nelson
Re: [GENERAL] Employee modeling question
On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil wrote: > Hello Nelson, > > Le 2014-09-04 à 10:39, Nelson Green a écrit : > > > Good morning, > > > > Hopefully this is the correct place to ask this type of question. > > > > I am in the early stages of designing a system to track employee > > information, including some aspects of their payroll, one of which is > > the source of the salary funds within the business. I need to make > > this generic enough to accommodate instances where an employee's > > salary is sourced from a department's budget, as well as those > > salaries of employees that are employed by companies too small to be > > departmentalized. So when an employee is employed by a department, the > > department is the source of the employee's salary, and the business is > > the department's parent entity. But when an employee is employed by a > > business that has no departments, the business entity is the source of > > the employee's salary, and the parent entity. I am struggling with the > > correct logical implementation of this scenario. > > > > So basically I have a situation where an employee will most likely be > > associated with a department within a business, but this can not be > > guaranteed, and I'm not 100% sure how to handle this. I am going to > > face the same problem with contracts where a department can out-source > > a function, or a business can. I think there may even be instances > > where a business with departments may out-source functionality that is > > not charged to a department. > > > > I could store the department and business attributes with the > > employee, but without proper constraints the referenced department > > could conceivably not correspond to the referenced business. Or I > > could ensure that all businesses have at least one department, > > defaulting to the business when the business has no department, but > > then I'd be storing duplicate data. The other alternative I've come up > > with is an exclusive constraint where the employee instance can only > > reference a department or a business, but not both. > > > > None of these solutions seems ideal, although the exclusivity solution > > seems like it would work the best, and I have had to create > > exclusivity constraints before. So, am I missing a more obvious > > solution, or am I even on track here? > > I’ve found this « Universal Person and Organization Data Model » very > useful to understand complex questions like that: > http://www.tdan.com/view-articles/5014 > > Hope this helps, > François Beausoleil > > Thanks François. This is pretty generic stuff, but my first reading has got me thinking that I should at least pick up some ideas from it. I will give it a go. And I've got a new web site that I've now known about before, so thanks for that as well. Regards, Nelson
[GENERAL] Employee modeling question
Good morning, Hopefully this is the correct place to ask this type of question. I am in the early stages of designing a system to track employee information, including some aspects of their payroll, one of which is the source of the salary funds within the business. I need to make this generic enough to accommodate instances where an employee's salary is sourced from a department's budget, as well as those salaries of employees that are employed by companies too small to be departmentalized. So when an employee is employed by a department, the department is the source of the employee's salary, and the business is the department's parent entity. But when an employee is employed by a business that has no departments, the business entity is the source of the employee's salary, and the parent entity. I am struggling with the correct logical implementation of this scenario. So basically I have a situation where an employee will most likely be associated with a department within a business, but this can not be guaranteed, and I'm not 100% sure how to handle this. I am going to face the same problem with contracts where a department can out-source a function, or a business can. I think there may even be instances where a business with departments may out-source functionality that is not charged to a department. I could store the department and business attributes with the employee, but without proper constraints the referenced department could conceivably not correspond to the referenced business. Or I could ensure that all businesses have at least one department, defaulting to the business when the business has no department, but then I'd be storing duplicate data. The other alternative I've come up with is an exclusive constraint where the employee instance can only reference a department or a business, but not both. None of these solutions seems ideal, although the exclusivity solution seems like it would work the best, and I have had to create exclusivity constraints before. So, am I missing a more obvious solution, or am I even on track here? Thanks in advance and regards, Nelson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Last inserted row id with complex PK
On Wed, Jan 8, 2014 at 5:39 PM, Alban Hertroys wrote: > On 08 Jan 2014, at 16:54, Nelson Green wrote: > > > I have a projects log table with a three column PK, project_num, > person_num, and sequence, where each new entry for a project/person > combination increments the sequence, which is not an auto incrementing > sequence. Is there any way to retrieve the last entry to the table? For > instance, if the last entry for person 427 on project 15 was sequence > number 125, is that information available to me anywhere? > > I think the answer rather depends on what you mean with this last inserted > row and for what purpose you need it. > > If you want that information right after it’s been inserted (for example > because you need that information in an FK relation), you can use > INSERT..RETURNING to return the values of the relevant PK fields. > Hi Alban, That is almost exactly what I am trying to do (create a FK relation). I am sorry I was so vague, that was not intentional. I just have so many disparate things going on at once and I just wasn't thinking my question through very clearly. I am scripting the input of dummy data, where I have a list of projects and users associated with those projects. My script chooses one pairing at "random" and inserts a dummy log record. I wanted to be able to look at the entry's in the order they were inserted in to verify my sequence trigger is working as intended. The default timestamp gave me that ability, but I think your suggestion of INSERT ... RETURNING would have been a bit more in line with my original thought, where the script was doing an insert to the project table and then logging the PK of that last insert to a temp table. The PK of the temp table is an auto incrementing sequence, but I wasn't sure how to get the PK of the log table from the previous INSERT. Since this is a test system I was able to add the timestamp and then just perform a query, sorting by that. But, the timestamp solution altered the model, the temp table did not. Regards, Nelson > > If that’s not what you’re after, then what is it that determines which > record is the “last one”? If you can’t identify such a record from your > data while you need that information, then something is missing from your > model. > > If you’re planning to add such information to your model based on your > current data, you might be able to get somewhat reliable results looking at > the transaction xid’s that the records were created in. There are a number > of pitfalls to that approach though, the most obvious one being transaction > xid wraparound. Of course, multiple inserts from the same transaction would > (originally) have the same xid, so you wouldn’t be able to determine which > one of those would be the latest (unless they’re for the same > person/project, obviously). > Such information could then be used to add a field with, for example, an > incrementing sequence. > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find there is no forest. > >
Re: [GENERAL] Last inserted row id with complex PK
On Wed, Jan 8, 2014 at 1:24 PM, David Johnston wrote: > Nelson Green wrote > > My apologies, I was not completely clear. I will not know any of the > > columns in advance. The most recent insert is the result of user input > > from > > a web form, so I won't know what project or what user generated the last > > insert. That was why I wandered if that information was stored anywhere > in > > the system. > > I'd probably add either (or both) a table-level auto-sequence field and a > "recordcreationdate" default timestamptz field. > Yeah, default timestamp was the best solution I could come up with, but I was hoping there was a way to access the actual PK of the most recent insert. Looks like timestamp it is. Thanks everyone! > David J. > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Last-inserted-row-id-with-complex-PK-tp5785863p5785901.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Last inserted row id with complex PK
On Wed, Jan 8, 2014 at 10:22 AM, Tom Lane wrote: > Francisco Olarte writes: > > Hi Nelson: > > On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green > wrote: > >> I have a projects log table with a three column PK, project_num, > person_num, > >> and sequence, where each new entry for a project/person combination > >> increments the sequence, which is not an auto incrementing sequence. Is > >> there any way to retrieve the last entry to the table? For instance, if > the > >> last entry for person 427 on project 15 was sequence number 125, is that > >> information available to me anywhere? > > > SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND > > project_num=15 > > Note that this will not work terribly well if there are concurrent > insertions for the same person/project. If that's not an issue, though, > it should be fine. It may be OK even if there are sometimes concurrent > insertions, if you are prepared to retry duplicate-key failures. > Concurrency is not likely to be an issue for this system, but I would certainly plan for it since I can not rule it out. > > > If it's a PK it should have an index and I believe it will be quite fast. > > It will be fast as long as sequence is the low-order column in the index. > > regards, tom lane >
Re: [GENERAL] Last inserted row id with complex PK
On Wed, Jan 8, 2014 at 10:09 AM, Francisco Olarte wrote: > Hi Nelson: > > On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green > wrote: > > I have a projects log table with a three column PK, project_num, > person_num, > > and sequence, where each new entry for a project/person combination > > increments the sequence, which is not an auto incrementing sequence. Is > > there any way to retrieve the last entry to the table? For instance, if > the > > last entry for person 427 on project 15 was sequence number 125, is that > > information available to me anywhere? > > SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND > project_num=15 > Hi Francisco, My apologies, I was not completely clear. I will not know any of the columns in advance. The most recent insert is the result of user input from a web form, so I won't know what project or what user generated the last insert. That was why I wandered if that information was stored anywhere in the system. > > If it's a PK it should have an index and I believe it will be quite fast. > > AAMOF, you could even build the above with max+1 into the insert query > for a new entry and have the values returned using a RETURNING clause. > > Francisco Olarte. >
[GENERAL] Last inserted row id with complex PK
I have a projects log table with a three column PK, project_num, person_num, and sequence, where each new entry for a project/person combination increments the sequence, which is not an auto incrementing sequence. Is there any way to retrieve the last entry to the table? For instance, if the last entry for person 427 on project 15 was sequence number 125, is that information available to me anywhere? Thanks, Nelson
Re: [GENERAL] Inserting rows containing composite foreign keys
> To: pgsql-general@postgresql.org
> Date: Tue, 26 Nov 2013 08:15:45 +0100
>
> Nelson Green, 25.11.2013 23:01:
> > Hello,
> > When inserting a record into the jobs table that references projects by
> > name, do I have to query the projects table twice,
> > once to get the funding source number, and once to get the project sequence
> > number, even though both results will
> > return the same row? Or put another way, is there a way to insert a row
> > into the jobs table without having to
> > perform two sub-queries for the same row, thus avoiding this:
> >
> > INSERT INTO jobs
> >VALUES ((SELECT fundsrc_number FROM projects
> > WHERE project_name = 'proj1-1'),
> >(SELECT project_seq FROM projects
> > WHERE project_name = 'proj1-1'),
> > 1, 'job1-1.1', 'first project 1-1 job');
> >
>
> Use an INSERT based on a SELECT, not based on VALUES:
>
> INSERT INTO projects (fundsrc_number, project_seq, project_name,
> project_desc)
> SELECT fundsrc_number, 1, 'proj1-1', 'first source01 project'
> FROM fundsrc
> WHERE fundsrc_name IN ('source01', 'source02');
>
> INSERT INTO jobs (fundsrc_number, project_seq, job_seq, job_name, job_desc)
> SELECT fundsrc_number, project_seq, 1, 'job1-1.1', 'first project 1-1 job'
> FROM projects
> WHERE project_name = 'proj1-1';
This works perfectly. I could swear I tried something similar and was
unsuccessful, but I can't find any indication that I did in my history or my
notes. Regardless, if you heard a loud smacking noise a few seconds ago that
was my palm hitting my forehead. I appreciate you taking the time to state the
obvious to the obviously blind.
> Note that it's good coding style to always specify the columns in an INSERT
> statement.
> It makes your statements more robust against changes.
I do. I just saved a few keystrokes and a bit of reading for this simple
example. I know to specify and qualify at all times.
Thanks for both tips!
[GENERAL] Inserting rows containing composite foreign keys
Hello, I have a question regarding inserting a row into a child table when the PK of the parent table is a composite key. In this case, I have funding sources that buy projects, and projects generate jobs. Project numbers are not universally unique, but are unique to funding source. Therefore the PK of project entity is a composite of both the funding source FK and a sequentially incrementing number per funding source. The jobs table then has a PK that is an incrementing sequential number per the composite source/project FK: CREATE TABLE fundsrc ( fundsrc_number INTEGER, fundsrc_name TEXT, fundsrc_desc TEXT, PRIMARY KEY (fundsrc_number) ); INSERT INTO fundsrc VALUES (1, 'source01', 'first source'), (2, 'source02', 'second source'); CREATE TABLE projects ( fundsrc_number INTEGER, project_seq INTEGER, project_name TEXT, project_desc TEXT, CONSTRAINT fk_fundsrc FOREIGN KEY (fundsrc_number) REFERENCES fundsrc(fundsrc_number), PRIMARY KEY (fundsrc_number, project_seq) ); CREATE INDEX project_id ON projects(fundsrc_number, project_seq); INSERT INTO projects VALUES ((SELECT fundsrc_number FROM fundsrc WHERE fundsrc_name = 'source01'), 1, 'proj1-1', 'first source01 project'), ((SELECT fundsrc_number FROM fundsrc WHERE fundsrc_name = 'source02'), 1, 'proj2-1', 'first source02 project'); CREATE TABLE jobs ( fundsrc_number INTEGER, project_seq INTEGER, job_seq INTEGER, job_name TEXT, job_desc TEXT, CONSTRAINT fk_project FOREIGN KEY (fundsrc_number, project_seq) REFERENCES projects(fundsrc_number, project_seq), PRIMARY KEY (fundsrc_number, project_seq, job_seq) ); CREATE INDEX job_id ON jobs(fundsrc_number, project_seq, job_seq); When inserting a record into the jobs table that references projects by name, do I have to query the projects table twice, once to get the funding source number, and once to get the project sequence number, even though both results will return the same row? Or put another way, is there a way to insert a row into the jobs table without having to perform two sub-queries for the same row, thus avoiding this: INSERT INTO jobs VALUES ((SELECT fundsrc_number FROM projects WHERE project_name = 'proj1-1'), (SELECT project_seq FROM projects WHERE project_name = 'proj1-1'), 1, 'job1-1.1', 'first project 1-1 job'); Note that the name attributes are not candidate keys because they are to remain changeable. This is not a terrible big deal, but I ask to satisfy my curiosity. Thanks!
Re: [GENERAL] Storing small image files
On Fri, May 10, 2013 at 5:24 AM, Misa Simic wrote: > 2013/5/10 Eduardo Morras > >> >> Hi Nelson. I worked with images and Postgresql, and want to add some >> comments: >> >> On Thu, 9 May 2013 13:40:15 -0500 >> Nelson Green wrote: >> > OK, this is kind of convoluted, but I got a couple of test cases that >> work >> > for me. The steps to make the first one are below. >> > >> > First I took one of the photos and shrunk it real small using GIMP. >> >> If you want to manipulate images automatically, don't use GIMP, use >> ImageMagick(for shell scripts) or OpenCV(for C sourcecode) >> >> > Then I >> > manually converted that to a base64 encoded text file: >> > /usr/bin/base64 < test.jpg > test.64 >> >> If you must to use the pg shell, perhaps coding Misa's function in other >> language (python f.ex.) allows you directly insert the bytea. >> >> > well, with pl/python there is more power - no need to use > lo_largobejects... However solution for him is not in function because of > file is on different machine then Postgres... > > I think Nelson has found solution what works for him... > I did, but I have made note of your and Thomas's suggestions. I just wanted to be able to verify that my DDL had done what I meant with a simple insert and select. The real work will be done via a PHP front-end. Thanks everyone! > > But now, what Thomas Keller suggested sounds as very good approach if user > doesn't want to write his own client code... > > A use hint: disable toast compression for that table, images are already >> compressed, you don't need to waste time with it. > > >> > That outputs a base64 string that matches test.64. Outputting that to a >> > file and then converting it back gives me my image: >> > /usr/bin/base64 -d < output.64 > newtest.jpg >> > >> > Like I said, kind of crazy, but it satisfies me that my basic premise is >> > doable. I'll still get one of the front-end developers to whip out some >> PHP >> > just to be safe. >> > >> > Thanks to all! >> >> --- --- >> Eduardo Morras >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
Re: [GENERAL] Storing small image files
On Fri, May 10, 2013 at 2:59 AM, Eduardo Morras wrote: > > Hi Nelson. I worked with images and Postgresql, and want to add some > comments: > > On Thu, 9 May 2013 13:40:15 -0500 > Nelson Green wrote: > > OK, this is kind of convoluted, but I got a couple of test cases that > work > > for me. The steps to make the first one are below. > > > > First I took one of the photos and shrunk it real small using GIMP. > > If you want to manipulate images automatically, don't use GIMP, use > ImageMagick(for shell scripts) or OpenCV(for C sourcecode) > > > Then I > > manually converted that to a base64 encoded text file: > > /usr/bin/base64 < test.jpg > test.64 > > If you must to use the pg shell, perhaps coding Misa's function in other > language (python f.ex.) allows you directly insert the bytea. > > A use hint: disable toast compression for that table, images are already > compressed, you don't need to waste time with it. > Thanks Eduardo, I should have caught that, but good advice. I appreciate it. > > > That outputs a base64 string that matches test.64. Outputting that to a > > file and then converting it back gives me my image: > > /usr/bin/base64 -d < output.64 > newtest.jpg > > > > Like I said, kind of crazy, but it satisfies me that my basic premise is > > doable. I'll still get one of the front-end developers to whip out some > PHP > > just to be safe. > > > > Thanks to all! > > --- --- > Eduardo Morras >
Re: [GENERAL] Storing small image files
Thanks Misa, for confirming my suspicions about server permissions. Like I said, what I came up will work for my simple needs. I have a script that creates the table, inserts a test row, and successfully retrieves it, which is all I need at this point. I appreciate all the help from everyone. On Thu, May 9, 2013 at 1:49 PM, Misa Simic wrote: > > > > 2013/5/9 Nelson Green > >> Hi Misa, >> >> That gives me a "ERROR: must be superuser to use server-side >> lo_import()" on the client. I think this is enforced to preserve file >> permissions on the server? I appreciate the suggestion, and I will keep it, >> but I think I found a solution that meets my immediate need. >> >> Thanks! >> >> >> On Thu, May 9, 2013 at 12:31 PM, Misa Simic wrote: >> >>> >>> >>> >>> 2013/5/9 Nelson Green >>> >>>> Thanks Karl, but I'm trying to do this from a psql shell. I can't use >>>> the C functions there, can I? >>>> >>>> >>>> On Thu, May 9, 2013 at 11:21 AM, Karl Denninger wrote: >>>> >>>>> On 5/9/2013 11:12 AM, Karl Denninger wrote: >>>>> >>>>> On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: >>>>> >>>>> Take a look here first : >>>>> >>>>> http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html >>>>> >>>>> >>>>> >>>>> then here : >>>>> http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html >>>>> >>>>> >>>>> >>>>> didnt try it myself tho. >>>>> >>>>> >>>>> >>>>> Most of the time people manipulate bytea's using a higher level >>>>> programming lang. >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: >>>>> >>>>> On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios < >>>>> ach...@matrix.gatewaynet.com> wrote: >>>>> >>>>> why not bytea? >>>>> >>>>> >>>>> Hi Achilleas, >>>>> >>>>> Actually I was asking if bytea is the correct datatype, and if so, >>>>> would someone mind providing a simple example of how to insert and >>>>> retrieve >>>>> the image through the psql client. >>>>> >>>>> Let's say I have an employee named Paul Kendell, who's employee ID >>>>> is 880918. Their badge number will be PK00880918, and their badge photo is >>>>> named /tmp/PK00880918.jpg. What would the INSERT statement look like to >>>>> put >>>>> that information into the security_badge table, and what would the SELECT >>>>> statement look like to retrieve that record? >>>>> >>>>> Thanks for your time. >>>>> >>>>> >>>>> >>>>> much more control, much more information, IMHO. >>>>> >>>>> In our DB evolving from an initial 7.1 back in 2001, and currently on >>>>> 9.0, >>>>> >>>>> we have been storing everything binary in bytea's. >>>>> >>>>> >>>>> >>>>> There are downsides in both solutions, you just have to have good >>>>> reasons >>>>> >>>>> to not use bytea. >>>>> >>>>> >>>>> >>>>> On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: >>>>> >>>>> Good morning list, >>>>> >>>>> I am designing a system that will have a table for security badges, >>>>> and we want to store the ID badge photo. These are small files, averaging >>>>> about 500K in size. We have made the decision to store the image as a BLOB >>>>> in the table itself for a variety of reasons. However, I am having trouble >>>>> understanding just how to do that. >>>>> >>>>> The table structures: >>>>> >>>>> CREATE TABLE employee >>>>> ( >>>>>employee_idINTEGER NOT NULL, >>>>>employee_lastname VARCHAR(35) NOT NULL, >>>>>employee_firstname VARCHAR(35)
Re: [GENERAL] Storing small image files
Hi Misa, That gives me a "ERROR: must be superuser to use server-side lo_import()" on the client. I think this is enforced to preserve file permissions on the server? I appreciate the suggestion, and I will keep it, but I think I found a solution that meets my immediate need. Thanks! On Thu, May 9, 2013 at 12:31 PM, Misa Simic wrote: > > > > 2013/5/9 Nelson Green > >> Thanks Karl, but I'm trying to do this from a psql shell. I can't use the >> C functions there, can I? >> >> >> On Thu, May 9, 2013 at 11:21 AM, Karl Denninger wrote: >> >>> On 5/9/2013 11:12 AM, Karl Denninger wrote: >>> >>> On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: >>> >>> Take a look here first : >>> >>> http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html >>> >>> >>> >>> then here : >>> http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html >>> >>> >>> >>> didnt try it myself tho. >>> >>> >>> >>> Most of the time people manipulate bytea's using a higher level >>> programming lang. >>> >>> >>> >>> >>> >>> On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: >>> >>> On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios < >>> ach...@matrix.gatewaynet.com> wrote: >>> >>> why not bytea? >>> >>> >>> Hi Achilleas, >>> >>> Actually I was asking if bytea is the correct datatype, and if so, >>> would someone mind providing a simple example of how to insert and retrieve >>> the image through the psql client. >>> >>> Let's say I have an employee named Paul Kendell, who's employee ID is >>> 880918. Their badge number will be PK00880918, and their badge photo is >>> named /tmp/PK00880918.jpg. What would the INSERT statement look like to put >>> that information into the security_badge table, and what would the SELECT >>> statement look like to retrieve that record? >>> >>> Thanks for your time. >>> >>> >>> >>> much more control, much more information, IMHO. >>> >>> In our DB evolving from an initial 7.1 back in 2001, and currently on >>> 9.0, >>> >>> we have been storing everything binary in bytea's. >>> >>> >>> >>> There are downsides in both solutions, you just have to have good reasons >>> >>> to not use bytea. >>> >>> >>> >>> On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: >>> >>> Good morning list, >>> >>> I am designing a system that will have a table for security badges, and >>> we want to store the ID badge photo. These are small files, averaging about >>> 500K in size. We have made the decision to store the image as a BLOB in the >>> table itself for a variety of reasons. However, I am having trouble >>> understanding just how to do that. >>> >>> The table structures: >>> >>> CREATE TABLE employee >>> ( >>>employee_idINTEGER NOT NULL, >>>employee_lastname VARCHAR(35) NOT NULL, >>>employee_firstname VARCHAR(35) NOT NULL, >>>employee_miCHAR(1), >>>PRIMARY KEY (employee_id) >>> ); >>> >>> CREATE TABLE security_badge >>> ( >>>badge_number CHAR(10)NOT NULL, >>>employee_idINTEGER NOT NULL >>> REFERENCES employee(employee_id), >>>badge_photo, >>>PRIMARY KEY (badge_number) >>> ); >>> >>> What datatype should I use for the badge_photo (bytea?), and what are >>> the commands to insert the picture accessing the server remotely through >>> psql, and to retrieve the photos as well, please? >>> >>> Thanks, >>> Nelson >>> >>> >>> >>> - >>> >>> Achilleas Mantzios >>> >>> IT DEV >>> >>> IT DEPT >>> >>> Dynacom Tankers Mgmt >>> >>> >>> >>> >>> - >>> >>> Achilleas Mantzios >>> >>> IT DEV >>> >>> IT DEPT >>> >>> Dynacom Tankers Mgmt >>> >>> >>> To enco
Re: [GENERAL] Storing small image files
On Thu, May 9, 2013 at 12:05 PM, Nelson Green wrote:
>
>
>
> On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios <
> ach...@matrix.gatewaynet.com> wrote:
>
>> **
>>
>> Take a look here first :
>>
>> http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html
>>
>>
>>
>> then here :
>> http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html
>>
>>
>>
>> didnt try it myself tho.
>>
>>
>>
>> Most of the time people manipulate bytea's using a higher level
>> programming lang.
>>
>
> Thanks Achilleas. I usually do the physical design in vi using sql
> scripts, and I like to include a couple of inserts and selects to make sure
> everything is going according to plan. It looks like I may just have to
> work with a front-end developer for this particular instance. Of all the
> stupid things, in all of my years doing this I've never once had to work
> with storing binary files, other than years ago when I was studying for
> some of the MySQL certs.
>
> If I do happen to come up with a solution that works I will be sure to
> post it here.
>
OK, this is kind of convoluted, but I got a couple of test cases that work
for me. The steps to make the first one are below.
First I took one of the photos and shrunk it real small using GIMP. Then I
manually converted that to a base64 encoded text file:
/usr/bin/base64 < test.jpg > test.64
this created a 113 line base64 file. I then just put those 113 lines into
my insert statement:
INSERT INTO security_badge
VALUES
(
'PK00880918',
(SELECT employee_id
FROM employee
WHERE employee_lastname = 'Kendell' AND
employee_firstname = 'Paul'),
decode('','base64')
);
Then to retrieve the file:
\o /output.64
SELECT ENCODE((SELECT badge_photo
FROM security_badge
WHERE badge_number = 'PK00880918'),'BASE64');
That outputs a base64 string that matches test.64. Outputting that to a
file and then converting it back gives me my image:
/usr/bin/base64 -d < output.64 > newtest.jpg
Like I said, kind of crazy, but it satisfies me that my basic premise is
doable. I'll still get one of the front-end developers to whip out some PHP
just to be safe.
Thanks to all!
>
>
>>
>>
>>
>>
>> On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
>>
>> On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <
>> ach...@matrix.gatewaynet.com> wrote:
>>
>> why not bytea?
>>
>>
>> Hi Achilleas,
>>
>> Actually I was asking if bytea is the correct datatype, and if so, would
>> someone mind providing a simple example of how to insert and retrieve the
>> image through the psql client.
>>
>> Let's say I have an employee named Paul Kendell, who's employee ID is
>> 880918. Their badge number will be PK00880918, and their badge photo is
>> named /tmp/PK00880918.jpg. What would the INSERT statement look like to put
>> that information into the security_badge table, and what would the SELECT
>> statement look like to retrieve that record?
>>
>> Thanks for your time.
>>
>>
>>
>> much more control, much more information, IMHO.
>>
>> In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
>>
>> we have been storing everything binary in bytea's.
>>
>>
>>
>> There are downsides in both solutions, you just have to have good reasons
>>
>> to not use bytea.
>>
>>
>>
>> On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
>>
>> Good morning list,
>>
>> I am designing a system that will have a table for security badges, and
>> we want to store the ID badge photo. These are small files, averaging about
>> 500K in size. We have made the decision to store the image as a BLOB in the
>> table itself for a variety of reasons. However, I am having trouble
>> understanding just how to do that.
>>
>> The table structures:
>>
>> CREATE TABLE employee
>> (
>>employee_idINTEGER NOT NULL,
>>employee_lastname VARCHAR(35) NOT NULL,
>>employee_firstname VARCHAR(35) NOT NULL,
>>employee_miCHAR(1),
>>PRIMARY KEY (employee_id)
>> );
>>
>> CREATE TABLE security_badge
>> (
>>badge_number CHAR(10)NOT NULL,
>>employee_idINTEGER NOT NULL
>> REFERENCES employee(employee_id),
>>badge_photo,
>>PRIMARY KEY (badge_number)
>> );
>>
>> What datatype should I use for the badge_photo (bytea?), and what are the
>> commands to insert the picture accessing the server remotely through psql,
>> and to retrieve the photos as well, please?
>>
>> Thanks,
>> Nelson
>>
>>
>>
>> -
>>
>> Achilleas Mantzios
>>
>> IT DEV
>>
>> IT DEPT
>>
>> Dynacom Tankers Mgmt
>>
>>
>>
>>
>> -
>>
>> Achilleas Mantzios
>>
>> IT DEV
>>
>> IT DEPT
>>
>> Dynacom Tankers Mgmt
>>
>
>
Re: [GENERAL] Storing small image files
Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I? On Thu, May 9, 2013 at 11:21 AM, Karl Denninger wrote: > On 5/9/2013 11:12 AM, Karl Denninger wrote: > > On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: > > Take a look here first : > > http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html > > > > then here : > http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html > > > > didnt try it myself tho. > > > > Most of the time people manipulate bytea's using a higher level > programming lang. > > > > > > On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: > > On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios < > ach...@matrix.gatewaynet.com> wrote: > > why not bytea? > > > Hi Achilleas, > > Actually I was asking if bytea is the correct datatype, and if so, would > someone mind providing a simple example of how to insert and retrieve the > image through the psql client. > > Let's say I have an employee named Paul Kendell, who's employee ID is > 880918. Their badge number will be PK00880918, and their badge photo is > named /tmp/PK00880918.jpg. What would the INSERT statement look like to put > that information into the security_badge table, and what would the SELECT > statement look like to retrieve that record? > > Thanks for your time. > > > > much more control, much more information, IMHO. > > In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, > > we have been storing everything binary in bytea's. > > > > There are downsides in both solutions, you just have to have good reasons > > to not use bytea. > > > > On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: > > Good morning list, > > I am designing a system that will have a table for security badges, and we > want to store the ID badge photo. These are small files, averaging about > 500K in size. We have made the decision to store the image as a BLOB in the > table itself for a variety of reasons. However, I am having trouble > understanding just how to do that. > > The table structures: > > CREATE TABLE employee > ( >employee_idINTEGER NOT NULL, >employee_lastname VARCHAR(35) NOT NULL, >employee_firstname VARCHAR(35) NOT NULL, >employee_miCHAR(1), >PRIMARY KEY (employee_id) > ); > > CREATE TABLE security_badge > ( >badge_number CHAR(10)NOT NULL, >employee_idINTEGER NOT NULL > REFERENCES employee(employee_id), >badge_photo, >PRIMARY KEY (badge_number) > ); > > What datatype should I use for the badge_photo (bytea?), and what are the > commands to insert the picture accessing the server remotely through psql, > and to retrieve the photos as well, please? > > Thanks, > Nelson > > > > - > > Achilleas Mantzios > > IT DEV > > IT DEPT > > Dynacom Tankers Mgmt > > > > > - > > Achilleas Mantzios > > IT DEV > > IT DEPT > > Dynacom Tankers Mgmt > > > To encode: > > > write_conn = Postgresql communication channel in your software that is > open to write to the table > > char*out; > size_tout_length, badge_length; > > badge_length = function-to-get-length-of(badge_binary_data); /* You have > to know how long it is */ > > out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, > &out_length); /* Convert */ > > That function allocates the required memory for the conversion. You now > have an encoded string you can "insert" or "update" with. Once you use it > in an "insert" or "update" function you then must "PQfreemem(out)" to > release the memory that was allocated. > > To recover the data you do: > > PQresult *result; > > result = PQexec(write_conn, "select badge_photo blah-blah-blah"); > > out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece > of the tuple and convert it */ > > "out" now contains the BINARY (decoded) photo data. When done with it you: > > PQfreemem(out) to release the memory that was allocated. > > That's the rough outline -- see here: > > http://www.postgresql.org/docs/current/static/libpq-exec.html > > -- > Karl Denninger > k...@denninger.net > *Cuda Systems LLC* > > Oops -- forgot the second parameter on the "PQunescapebytea" call :-)
Re: [GENERAL] Storing small image files
On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > ** > > Take a look here first : > > http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html > > > > then here : > http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html > > > > didnt try it myself tho. > > > > Most of the time people manipulate bytea's using a higher level > programming lang. > Thanks Achilleas. I usually do the physical design in vi using sql scripts, and I like to include a couple of inserts and selects to make sure everything is going according to plan. It looks like I may just have to work with a front-end developer for this particular instance. Of all the stupid things, in all of my years doing this I've never once had to work with storing binary files, other than years ago when I was studying for some of the MySQL certs. If I do happen to come up with a solution that works I will be sure to post it here. > > > > > On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: > > On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios < > ach...@matrix.gatewaynet.com> wrote: > > why not bytea? > > > Hi Achilleas, > > Actually I was asking if bytea is the correct datatype, and if so, would > someone mind providing a simple example of how to insert and retrieve the > image through the psql client. > > Let's say I have an employee named Paul Kendell, who's employee ID is > 880918. Their badge number will be PK00880918, and their badge photo is > named /tmp/PK00880918.jpg. What would the INSERT statement look like to put > that information into the security_badge table, and what would the SELECT > statement look like to retrieve that record? > > Thanks for your time. > > > > much more control, much more information, IMHO. > > In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, > > we have been storing everything binary in bytea's. > > > > There are downsides in both solutions, you just have to have good reasons > > to not use bytea. > > > > On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: > > Good morning list, > > I am designing a system that will have a table for security badges, and we > want to store the ID badge photo. These are small files, averaging about > 500K in size. We have made the decision to store the image as a BLOB in the > table itself for a variety of reasons. However, I am having trouble > understanding just how to do that. > > The table structures: > > CREATE TABLE employee > ( >employee_idINTEGER NOT NULL, >employee_lastname VARCHAR(35) NOT NULL, >employee_firstname VARCHAR(35) NOT NULL, >employee_miCHAR(1), >PRIMARY KEY (employee_id) > ); > > CREATE TABLE security_badge > ( >badge_number CHAR(10)NOT NULL, >employee_idINTEGER NOT NULL > REFERENCES employee(employee_id), >badge_photo, >PRIMARY KEY (badge_number) > ); > > What datatype should I use for the badge_photo (bytea?), and what are the > commands to insert the picture accessing the server remotely through psql, > and to retrieve the photos as well, please? > > Thanks, > Nelson > > > > - > > Achilleas Mantzios > > IT DEV > > IT DEPT > > Dynacom Tankers Mgmt > > > > > - > > Achilleas Mantzios > > IT DEV > > IT DEPT > > Dynacom Tankers Mgmt >
Re: [GENERAL] Storing small image files
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > ** > > why not bytea? > Hi Achilleas, Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. > much more control, much more information, IMHO. > > In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, > > we have been storing everything binary in bytea's. > > > > There are downsides in both solutions, you just have to have good reasons > > to not use bytea. > > > > On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: > > Good morning list, > > I am designing a system that will have a table for security badges, and we > want to store the ID badge photo. These are small files, averaging about > 500K in size. We have made the decision to store the image as a BLOB in the > table itself for a variety of reasons. However, I am having trouble > understanding just how to do that. > > The table structures: > > CREATE TABLE employee > ( >employee_idINTEGER NOT NULL, >employee_lastname VARCHAR(35) NOT NULL, >employee_firstname VARCHAR(35) NOT NULL, >employee_miCHAR(1), >PRIMARY KEY (employee_id) > ); > > CREATE TABLE security_badge > ( >badge_number CHAR(10)NOT NULL, >employee_idINTEGER NOT NULL > REFERENCES employee(employee_id), >badge_photo, >PRIMARY KEY (badge_number) > ); > > What datatype should I use for the badge_photo (bytea?), and what are the > commands to insert the picture accessing the server remotely through psql, > and to retrieve the photos as well, please? > > Thanks, > Nelson > > > > - > > Achilleas Mantzios > > IT DEV > > IT DEPT > > Dynacom Tankers Mgmt >
[GENERAL] Storing small image files
Good morning list, I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson
