Re: view table pkey values

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(at)iol(dot)ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general