Re: view table pkey values

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
> ------------------------------------------------------------------