Re: Serial data type

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/interactive/functions-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/