I have several tables that require auto-generated Ids. I have noticed the
serial and bigserial data types (or pseudo-types). These seem like they make
things much simpler, but if you use this, how can you find out the the value
of the serial column after you insert a row? Do you have to lookup the
primary key or is it stored in a session variable or some other place? Is
it better to define the sequence manually and just select it out by hand
before doing the insert?

Thanks,


Jed S. Walker

am 13.04.2005, um 9:30:09 -0600 mailte Walker, Jed S folgendes:
> I have several tables that require auto-generated Ids. I have noticed the
> serial and bigserial data types (or pseudo-types). These seem like they make
> things much simpler, but if you use this, how can you find out the the value
> of the serial column after you insert a row? Do you have to lookup the

Please read the manual about currval().


> primary key or is it stored in a session variable or some other place? Is

In a squence, a extra database object.


> it better to define the sequence manually and just select it out by hand
> before doing the insert?

No. Why?

test_db=# create table seq_test (id serial, name varchar);
HINWEIS: CREATE TABLE erstellt implizit eine Sequenz >>seq_test_id_seq<< f?r die >>serial<<-Spalte >>seq_test.id<<
CREATE TABLE
test_db=# insert into seq_test (name) values ('Andreas');
INSERT 373930 1
test_db=# insert into seq_test (name) values ('Anja');
INSERT 373931 1
test_db=# select * from seq_test;
id | name
----+---------
1 | Andreas
2 | Anja
(2 Zeilen)

test_db=#




Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Wed, Apr 13, 2005 at 09:30:09AM -0600, Walker, Jed S wrote:
>
> I have several tables that require auto-generated Ids. I have noticed the
> serial and bigserial data types (or pseudo-types). These seem like they make
> things much simpler, but if you use this, how can you find out the the value
> of the serial column after you insert a row? Do you have to lookup the
> primary key or is it stored in a session variable or some other place?

See "Sequence Manipulation Functions" in the "Functions and Operators"
chapter of the documentation. This is also mentioned in the FAQ.

http://www.postgresql.org/docs/8.0/i...-sequence.html
http://www.postgresql.org/docs/faqs.FAQ.html#4.11.2

> Is it better to define the sequence manually and just select it out by
> hand before doing the insert?

That depends on how you define "better." Whether you define the
sequence manually or not doesn't affect how you can use it: in
either case you can explicitly obtain a value from it, and in either
case you can define a column to have a default value that comes
from the sequence.

One effect of defining a serial column is that recent versions of
PostgreSQL know about the dependency between the table and the
sequence, so if you drop the table then the sequence automatically
gets dropped too, and if you try to drop a sequence then you'll get
an error if a table depends on it.

Whether you insert first or get the sequence value first seldom
matters; it's usually personal preference. An exception is when
you're not sure that separate SQL statements will be run over the
same connection (e.g., if you're using a connection pool), in which
case you'll probably need to obtain the sequence value first --
otherwise you might get an error or the wrong value when you query
for the sequence value from the last insert.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(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