Re: Serial data type

Thanks Michael,

I see the

execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
new_id = execute("SELECT currval('person_id_seq')");

Would work great for the serial type.

I appreciate your help.

-----Original Message-----
From: Michael Fuhr [mailto:mike(at)fuhr(dot)org]
Sent: Wednesday, April 13, 2005 10:11 AM
To: Walker, Jed S
Cc: 'pgsql-novice(at)postgresql(dot)org'
Subject: Re: [NOVICE] 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/