Re: view table pkey values

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(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------