Re: Serial data type - Mailing list pgsql-novice

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Be aware that this can blow up in your face when you use it in
conjunction with a naive connection pool. You're better off to do

SELECT nextval('person_id_seq') AS new_id;

Then...

INSERT INTO person (person_id, name) VALUES (new_id, 'Some guy');

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A


Walker, Jed S wrote:
> 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@fuhr.org]
> Sent: Wednesday, April 13, 2005 10:11 AM
> To: Walker, Jed S
> Cc: 'pgsql-novice@postgresql.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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCXsiYgfzn5SevSpoRApoKAJ9auIO5XcN6/OTts/upTLSH7KbpPQCdHYjd
H+Ic4CCiHeMmHUeDw8ll/DA=
=iXZV
-----END PGP SIGNATURE-----