Thread: view table pkey values
Hello,
Is it possible to select or otherwise view a table's primary key values?
I'm troubleshooting the following error:
ERROR: duplicate key value violates unique constraint "foo_pkey"
The insert that yields the error seems innocuous enough:
INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2);
It seems as if there's a sequence (foo_pkey) that's got some weird
values in it. The table itself looks like this:
CREATE TABLE foo (
foo_id SERIAL PRIMARY KEY,
color_id INTEGER NOT NULL REFERENCES color(color_id) ON DELETE NO
ACTION,
ordinal INTEGER DEFAULT NULL,
person_id INTEGER NOT NULL REFERENCES person(person_id) ON DELETE
SET NULL ON UPDATE CASCADE,
created timestamp DEFAULT CURRENT_TIMESTAMP);
Thanks in advance,
Scott
On 24/08/2009 17:31, Scott Frankel wrote: > Is it possible to select or otherwise view a table's primary key values? [snip] > CREATE TABLE foo ( > foo_id SERIAL PRIMARY KEY, select foo_id from foo; ? ....or am I missing something? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On 24/08/2009 17:31, Scott Frankel wrote: > The insert that yields the error seems innocuous enough: > > INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2); > > It seems as if there's a sequence (foo_pkey) that's got some weird > values in it. The table itself looks like this: > > > CREATE TABLE foo ( > foo_id SERIAL PRIMARY KEY, If the sequence's current value is lower than the highest foo_id in the table, then you'll get collisions - I'd imagine that's what's happening to you. You can fix that by using setval() to set the sequence value to a number higher than any currently in foo_id. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Hi Ray,
On Aug 24, 2009, at 9:48 AM, Raymond O'Donnell wrote:
> On 24/08/2009 17:31, Scott Frankel wrote:
>> The insert that yields the error seems innocuous enough:
>>
>> INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019,
>> 2);
>>
>> It seems as if there's a sequence (foo_pkey) that's got some weird
>> values in it. The table itself looks like this:
>>
>>
>> CREATE TABLE foo (
>> foo_id SERIAL PRIMARY KEY,
>
>
> If the sequence's current value is lower than the highest foo_id in
> the
> table, then you'll get collisions
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.
> You can fix that by using setval() to set the sequence value to
> a number higher than any currently in foo_id.
Aha! So the explicit foo_id value cannot exceed the internal
sequence, foo_foo_id_seq value? They should actually be the same,
unless there've been insert errors, right?
Is there a command that lists the values for the internal,
foo_foo_id_seq, sequence?
Thanks!
Scott
>
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
Scott Frankel <leknarf@pacbell.net> writes:
> Is there a command that lists the values for the internal,
> foo_foo_id_seq, sequence?
select * from foo_foo_id_seq;
The usual way to get into this sort of trouble is to load a bunch of
data into the table while explicitly specifying ID values. It will
take the data (as long as it doesn't conflict with existing IDs)
but nothing happens to the sequence. pg_dump knows it has to update
the sequence too, but a lot of other tools don't; and even with pg_dump
a selective restore can mess things up.
regards, tom lane
Got it! Yes, this started happening after loading from a pg_dump. Thanks for the explanation! Scott On Aug 24, 2009, at 10:52 AM, Tom Lane wrote: > Scott Frankel <leknarf@pacbell.net> writes: >> Is there a command that lists the values for the internal, >> foo_foo_id_seq, sequence? > > select * from foo_foo_id_seq; > > The usual way to get into this sort of trouble is to load a bunch of > data into the table while explicitly specifying ID values. It will > take the data (as long as it doesn't conflict with existing IDs) > but nothing happens to the sequence. pg_dump knows it has to update > the sequence too, but a lot of other tools don't; and even with > pg_dump > a selective restore can mess things up. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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
------------------------------------------------------------------
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