CODEWITHSUNDEEP
postgresqlpostgresql-11
Paul Ostrowski
Paul Ostrowski

Reputation: 1966

pg_get_serial_sequence returns ERROR: column "---" of relation "---" does not exist

Using Postgres 11.5, I'm trying to manually insert some data (with specific key values) into a table that has a SERIAL PRIMARY KEY field, and then adjust the sequence of the SERIAL primary key field to return a value higher than the current max value. But I'm getting an error. Here is a simple table

CREATE TABLE FOO (
  FOO_ID SERIAL PRIMARY KEY,
  VAL     INTEGER
);

INSERT INTO FOO (VAL) VALUES (4);
SELECT * FROM FOO;
-- returns one record with a FOO_ID of 1, as expected

INSERT INTO FOO (FOO_ID, VAL) VALUES (5, 5);
SELECT * FROM FOO WHERE VAL=5;
-- returns one record with a FOO_ID of 5, as expected

Next, I want to update the sequence so my next few inserts without explicit FOO_IDs wont collide with the one with an ID of 5:

SELECT setval(pg_get_serial_sequence('FOO', 'FOO_ID'), (SELECT COALESCE(MAX(FOO_ID), 0) FROM FOO));

but this portion of the above SQL is failing:

SELECT pg_get_serial_sequence('FOO', 'FOO_ID');

with this error: ERROR: column "FOO_ID" of relation "foo" does not exist SQL state: 42703

What am I doing wrong, or is there a better way to do this? It may be worth noting that I do have some tables for which my setval statement is correcting the table's sequence, but it doesn't work on all tables, and not on this simplified case.

Upvotes: 3

Views: 2419

Answers (2)

SommerEngineering
SommerEngineering

Reputation: 1700

In my case the problem was the same, but the solution was slightly different. I hope that my answer will help others. The lower case of the table name did not help, because the application uses table names where the first character is capitalized (Foo instead of foo). In an e-mail from 2004 (link) I got the right hint:

SELECT PG_GET_SERIAL_SEQUENCE('"Foo"', 'Foo_id');

So the table name must be in double quotes, surrounded by single quotes.

Upvotes: 14

Paul Ostrowski
Paul Ostrowski

Reputation: 1966

I just figured out its a case sensitivity issue. 

SELECT pg_get_serial_sequence('foo', 'foo_id');

works!

Upvotes: 1

Related Questions