Обсуждение: Serial data type
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 ===
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/
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/
-----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-----