Re: view table pkey values - Mailing list pgsql-general
From | Scott Frankel |
---|---|
Subject | Re: view table pkey values |
Date | |
Msg-id | 8A72684F-B296-4EC7-ABEB-339F2CA6B0B3@pacbell.net Whole thread Raw |
In response to | Re: view table pkey values (Raymond O'Donnell <rod@iol.ie>) |
List | pgsql-general |
Thanks for the thorough explanation and link to more docs. Very much appreciated! Scott On Aug 24, 2009, at 11:03 AM, Raymond O'Donnell wrote: > On 24/08/2009 18:37, Scott Frankel wrote: >> If I understand how tables are managed internally, there are 2 >> sequences: my explicit foo_id and the internal sequence >> foo_foo_id_seq: >> >> public | foo_foo_id_seq | sequence | pguser | >> >> It's this internal sequence that must be involved in the collision, >> since I'm not specifying an insert value for my explicit foo_id >> column. > > Your column foo_id is just that - a column . It's not a sequence. It's > an integer column which is specified to take it's default value from a > sequence, which Postgres creates for you and names foo_foo_id_seq. > > In fact, "serial" isn't a real type - its syntactic sugar that - > > (i) creates the sequence, named <table name>_<column name>_seq, > (ii) creates the column as type integer, > (iii) makes the sequence to be owned by the column, and > (iv) sets the default value of the column as nextval(<sequence_name>). > > The "serial" pseudo-type just saves you doing all this by hand. > > When you don't enter an explicit value for the "Serial" column, the > specified default value gets entered instead, which is the return > value > of the function nextval('foo_foo_id_seq'). You can of course enter an > explicit value into the column, and then the default is ignored; by > the > same token, the associated sequence doesn't get incremented, so this > can > lead to collisions if you're not careful. > > For example: > > postgres=# create table test(a serial primary key, b text); > > NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for > serial column "test.a" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "test_pkey" for table "test" > CREATE TABLE > > postgres=# insert into test(b) values('This will work'); > INSERT 0 1 > > postgres=# select * from test; > a | b > ---+---------------- > 1 | This will work > (1 row) > > postgres=# select currval('test_a_seq'); > currval > --------- > 1 > (1 row) > > postgres=# insert into test(a, b) values(2, 'This works too'); > INSERT 0 1 > > postgres=# select * from test; > a | b > ---+---------------- > 1 | This will work > 2 | This works too > (2 rows) > > postgres=# select currval('test_a_seq'); > > currval > --------- > 1 > (1 row) > > postgres=# insert into test(b) values('This will bomb'); > > ERROR: duplicate key value violates unique constraint "test_pkey" > > postgres=# select currval('test_a_seq'); > currval > --------- > 2 > (1 row) > > > You can read all about it here: > > http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL > > I hope all this helps. :-) > > Ray. > > ------------------------------------------------------------------ > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > rod@iol.ie > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > ------------------------------------------------------------------ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: