[ADMIN] Can't query the sequence table of a serial primary key.

[Fred Parkinson]

We have moved some databases from 7.x to 8.4
Previously I could query the sequencing table of a 'serial primary key' by simply running
select * from mytable_id_seq;
where mytable has id as a serial primary key and mytable_id_seq is the postgresql relation which manages the key.

Has something significantly changed this in ver 8.4?
When I do \d I see the table and the sequencer under 'List of relations'.
But when I run select * from mytable_id_seq; postgresql responds with:
relation 'mytable_id_seq' does not exist.

Am I confused about something? (Wouldn't be the first time sigh)

Thanks for any help.

Fred

[Kevin Grittner]

Post by Fred Parkinson
When I do \d I see the table and the sequencer under 'List of
relations'. But when I run select * from mytable_id_seq;
postgresql responds with: relation 'mytable_id_seq' does not
exist.

It works for me. Can you provide a small self-contained test case
which demonstrates the problem?

Hopefully you are aware of these functions, which might be easier:

http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html

-Kevin

[Fred Parkinson]

Thanks everyone for your replies.

In my original post I failed to EXACTLY reproduce the problem, in that capital letters were involved which I left out of the description.
For example, the id had been set up as "ID" serial primary key, and the sequencer was consequently named 'mytable_ID_seq' not 'mytable_id_seq' (he was stabbed to death by details!)
When I ran the select statement I DID reproduce the capital letters as they existed, but got the error I described.

In the effort to create a test sample I left out all caps and made everything lower case.

Viola! The problem disappeared! Sanity restored (for now).

That's it, no more caps for me!

Thanks again for everyone's efforts to help.

Fred

Post by Fred Parkinson
When I do \d I see the table and the sequencer under 'List of
relations'. But when I run select * from mytable_id_seq;
postgresql responds with: relation 'mytable_id_seq' does not
exist.

It works for me. Can you provide a small self-contained test case
which demonstrates the problem?

Hopefully you are aware of these functions, which might be easier:

http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html

-Kevin

[Tom Lane]

Post by Fred Parkinson
We have moved some databases from 7.x to 8.4
Previously I could query the sequencing table of a 'serial primary key' by simply running
select * from mytable_id_seq;
where mytable has id as a serial primary key and mytable_id_seq is the postgresql relation which manages the key.

That naming relationship isn't guaranteed, and AFAIR it wasn't
guaranteed in 7.4 either; in case of name collision you'd get something
like mytable_id_seq1 instead.

Post by Fred Parkinson
When I do \d I see the table and the sequencer under 'List of relations'.
relation 'mytable_id_seq' does not exist.

Maybe the sequence is in a schema that's not in your search_path.

regards, tom lane