pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCan't query the sequence table of a serial primary key.
4+ messages / 3 participants
[nested] [flat]
* Can't query the sequence table of a serial primary key.
@ 2012-02-23 19:00 Fred Parkinson <FredP@abag.ca.gov>
2012-02-23 19:34 ` Re: Can't query the sequence table of a serial primary key. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-02-23 19:35 ` Re: Can't query the sequence table of a serial primary key. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Fred Parkinson @ 2012-02-23 19:00 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can't query the sequence table of a serial primary key.
2012-02-23 19:00 Can't query the sequence table of a serial primary key. Fred Parkinson <FredP@abag.ca.gov>
@ 2012-02-23 19:34 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-02-23 20:22 ` Re: Can't query the sequence table of a serial primary key. Fred Parkinson <FredP@abag.ca.gov>
1 sibling, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2012-02-23 19:34 UTC (permalink / raw)
To: Fred Parkinson <FredP@abag.ca.gov>; pgsql-admin
"Fred Parkinson" <FredP@abag.ca.gov> wrote:
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can't query the sequence table of a serial primary key.
2012-02-23 19:00 Can't query the sequence table of a serial primary key. Fred Parkinson <FredP@abag.ca.gov>
2012-02-23 19:34 ` Re: Can't query the sequence table of a serial primary key. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-02-23 20:22 ` Fred Parkinson <FredP@abag.ca.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Fred Parkinson @ 2012-02-23 20:22 UTC (permalink / raw)
To: pgsql-admin; Kevin Grittner <Kevin.Grittner@wicourts.gov>
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
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 02/23/2012 11:34 AM >>>
"Fred Parkinson" <FredP@abag.ca.gov> wrote:
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can't query the sequence table of a serial primary key.
2012-02-23 19:00 Can't query the sequence table of a serial primary key. Fred Parkinson <FredP@abag.ca.gov>
@ 2012-02-23 19:35 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2012-02-23 19:35 UTC (permalink / raw)
To: Fred Parkinson <FredP@abag.ca.gov>; +Cc: pgsql-admin
"Fred Parkinson" <FredP@abag.ca.gov> writes:
> 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.
> 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.
Maybe the sequence is in a schema that's not in your search_path.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-23 20:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-23 19:00 Can't query the sequence table of a serial primary key. Fred Parkinson <FredP@abag.ca.gov>
2012-02-23 19:34 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-02-23 20:22 ` Fred Parkinson <FredP@abag.ca.gov>
2012-02-23 19:35 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox