Parent Id

[Gene Vital]

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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruno Wolff III]

On Thu, Oct 09, 2003 at 14:26:21 -0400,
Gene Vital <ge*******@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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Gene Vital]

have an example :)

Bruno Wolff III wrote:

On Thu, Oct 09, 2003 at 14:26:21 -0400,
Gene Vital <ge*******@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.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bruno Wolff III]

On Thu, Oct 09, 2003 at 15:55:27 -0400,
Gene Vital <ge*******@karibe.com> wrote:

have an example :)

Bruno Wolff III wrote:

On Thu, Oct 09, 2003 at 14:26:21 -0400,
Gene Vital <ge*******@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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Gene Vital]

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 <ge*******@karibe.com> wrote:

have an example :)

Bruno Wolff III wrote:

On Thu, Oct 09, 2003 at 14:26:21 -0400,
Gene Vital <ge*******@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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

On Thu, Oct 09, 2003 at 17:09:33 -0400,
Gene Vital <ge*******@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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Gene Vital]

Bruno Wolff III wrote:

On Thu, Oct 09, 2003 at 17:09:33 -0400,
Gene Vital <ge*******@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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org