Thread: Parent Id

Parent Id

From

Gene Vital

Date:

09 October 2003, 18:31:11

when inserting new records into  parent / child tables, what is the best
recommended way of retrieving the pkey value from the parent table when
using auto incrementing fields ?


--
Eugene Vital
Any technology indistinguishable from magic is insufficiently advanced.

Re: Parent Id

From

Bruno Wolff III

Date:

09 October 2003, 18:36:37

On Thu, Oct 09, 2003 at 14:26:21 -0400,
  Gene Vital <genevital@karibe.com> wrote:
> when inserting new records into  parent / child tables, what is the best
> recommended way of retrieving the pkey value from the parent table when
> using auto incrementing fields ?

You want to use currval. currval will return the last value obtained
by nextval in the same session, so it is safe to use without any additional
locking.

Re: Parent Id

From

Gene Vital

Date:

09 October 2003, 20:01:39

have an example :)

Bruno Wolff III wrote:

> On Thu, Oct 09, 2003 at 14:26:21 -0400,
>   Gene Vital <genevital@karibe.com> wrote:
>
>>when inserting new records into  parent / child tables, what is the best
>>recommended way of retrieving the pkey value from the parent table when
>>using auto incrementing fields ?
>
>
> You want to use currval. currval will return the last value obtained
> by nextval in the same session, so it is safe to use without any additional
> locking.
>

--
Eugene Vital
Any technology indistinguishable from magic is insufficiently advanced.

Re: Parent Id

From

Bruno Wolff III

Date:

09 October 2003, 20:13:31

On Thu, Oct 09, 2003 at 15:55:27 -0400,
  Gene Vital <genevital@karibe.com> wrote:
> have an example :)
>
> Bruno Wolff III wrote:
>
> >On Thu, Oct 09, 2003 at 14:26:21 -0400,
> >  Gene Vital <genevital@karibe.com> wrote:
> >
> >>when inserting new records into  parent / child tables, what is the best
> >>recommended way of retrieving the pkey value from the parent table when
> >>using auto incrementing fields ?
> >
> >
> >You want to use currval. currval will return the last value obtained
> >by nextval in the same session, so it is safe to use without any additional
> >locking.
> >

You would do something like:
insert into parent_table (pk, col1, col2) values (default, 'val1', 'val2');
insert into child_table (pk, parent, col1, col2)
  values (default, currval('parent_table_pk_seq'), 'val3', 'val4');

I don't remember when being able to use default in insert statements was
added. You may need to just leave the pk columns off the list. I added them
so that you could see what the normal sequence name looks like. I also
assume that the two pk columns are declared to be of type serial. If not,
then you have to do the sequence and default creation yourself.

Re: Parent Id

From

Gene Vital

Date:

09 October 2003, 21:14:14

ok, I am new to Postgres so could you give a little better explanation
of this  ??

I haven't created any sequence for this I am just using a type serial
field. will I have to create a sequence for it?


Here is my code to create the tables

CREATE TABLE workstations (station_id INT4 PRIMARY KEY, name
VARCHAR(50), description VARCHAR(250))

CREATE TABLE wsoptions (option_id SERIAL PRIMARY KEY, station_id INT4
REFERENCES workstations (station_id)  ON DELETE CASCADE, type
VARCHAR(20), data TEXT)



insert into workstations (name, description)
    values("new", "This is a test")

insert into wsoptions (stations_id, type, data)
    values( ????, "LOCATION", "10th floor outer, office 27")


thanks....

Bruno Wolff III wrote:

> On Thu, Oct 09, 2003 at 15:55:27 -0400,
>   Gene Vital <genevital@karibe.com> wrote:
>
>>have an example :)
>>
>>Bruno Wolff III wrote:
>>
>>
>>>On Thu, Oct 09, 2003 at 14:26:21 -0400,
>>> Gene Vital <genevital@karibe.com> wrote:
>>>
>>>
>>>>when inserting new records into  parent / child tables, what is the best
>>>>recommended way of retrieving the pkey value from the parent table when
>>>>using auto incrementing fields ?
>>>
>>>
>>>You want to use currval. currval will return the last value obtained
>>>by nextval in the same session, so it is safe to use without any additional
>>>locking.
>>>
>
>
> You would do something like:
> insert into parent_table (pk, col1, col2) values (default, 'val1', 'val2');
> insert into child_table (pk, parent, col1, col2)
>   values (default, currval('parent_table_pk_seq'), 'val3', 'val4');
>
> I don't remember when being able to use default in insert statements was
> added. You may need to just leave the pk columns off the list. I added them
> so that you could see what the normal sequence name looks like. I also
> assume that the two pk columns are declared to be of type serial. If not,
> then you have to do the sequence and default creation yourself.
>

--
Eugene Vital
Any technology indistinguishable from magic is insufficiently advanced.

Re: Parent Id

From

Bruno Wolff III

Date:

10 October 2003, 01:23:47

On Thu, Oct 09, 2003 at 17:09:33 -0400,
  Gene Vital <genevital@karibe.com> wrote:
> ok, I am new to Postgres so could you give a little better explanation
> of this  ??
>
> I haven't created any sequence for this I am just using a type serial
> field. will I have to create a sequence for it?

When you use the serial type a sequence is automatically created for you.
The name is tablename_columnname_seq unless that string is too long
(> 64 characters I think). The actual name used gets printed as a notice
when you create the table.

>
> Here is my code to create the tables
>
> CREATE TABLE workstations (station_id INT4 PRIMARY KEY, name
> VARCHAR(50), description VARCHAR(250))
>
> CREATE TABLE wsoptions (option_id SERIAL PRIMARY KEY, station_id INT4
> REFERENCES workstations (station_id)  ON DELETE CASCADE, type
> VARCHAR(20), data TEXT)
>
>
>
> insert into workstations (name, description)
>     values("new", "This is a test")
>
> insert into wsoptions (stations_id, type, data)
>     values( ????, "LOCATION", "10th floor outer, office 27")

The second insert should be:
insert into wsoptions (stations_id, type, data)
    values( currval('workstations_station_id_seq'),
    'LOCATION', '10th floor outer, office 27')

Also note that you need to use single quotes for data values. Double
quotes are used for the names of database objects.

Re: Parent Id

From

Gene Vital

Date:

10 October 2003, 13:08:57


Bruno Wolff III wrote:

> On Thu, Oct 09, 2003 at 17:09:33 -0400,
>   Gene Vital <genevital@karibe.com> wrote:
>
>>ok, I am new to Postgres so could you give a little better explanation
>>of this  ??
>>
>>I haven't created any sequence for this I am just using a type serial
>>field. will I have to create a sequence for it?
>
>
> When you use the serial type a sequence is automatically created for you.
> The name is tablename_columnname_seq unless that string is too long
> (> 64 characters I think). The actual name used gets printed as a notice
> when you create the table.

I found that in the docs after I sent out the last post.
thanks for the feed back tho :)

>
>
>>Here is my code to create the tables
>>
>>CREATE TABLE workstations (station_id INT4 PRIMARY KEY, name
>>VARCHAR(50), description VARCHAR(250))
>>
>>CREATE TABLE wsoptions (option_id SERIAL PRIMARY KEY, station_id INT4
>>REFERENCES workstations (station_id)  ON DELETE CASCADE, type
>>VARCHAR(20), data TEXT)
>>
>>
>>
>>insert into workstations (name, description)
>>    values("new", "This is a test")
>>
>>insert into wsoptions (stations_id, type, data)
>>    values( ????, "LOCATION", "10th floor outer, office 27")
>
>
> The second insert should be:
> insert into wsoptions (stations_id, type, data)
>     values( currval('workstations_station_id_seq'),
>     'LOCATION', '10th floor outer, office 27')
>
> Also note that you need to use single quotes for data values. Double
> quotes are used for the names of database objects.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Eugene Vital
Any technology indistinguishable from magic is insufficiently advanced.