Error referencing sequence: "relation \"<sequence>\" does not exist"

I am doing Upsert like the following in Typescript using a sequence in PostgreSQL:

let SAVE_CUSTOMER = `
INSERT INTO "db"."customers" ("id", "name")
VALUES (NEXTVAL('customers_sq'), $1)
ON CONFLICT ("id") DO UPDATE
  SET "name" = $1;
`;


function saveCustomer(name: string) {
  datasource.query(SAVE_CUSTOMER, name)
}

Where:

"db" is the name of the database
"customers" is the name of the table
"id" is the primary key in the table
"name" is the other column in the table

I am getting the following error message:

error: i: relation \"customers_sq\" does not exist

Things I have tried that also don't work:

Updating it to NEXTVAL('db'.'customers_sq')
Updating it to NEXTVAL("db"."customers_sq")
Updating it to NEXTVAL("customers_sq")

I can see the sequence (also in all lower case) in my Postgres database viewer. Any tips on how to debug?

Solution

The function nextval() expects a single argument of type regclass, an "object identifier type", basically an alias for the base data type oid. The manual:

All of the OID alias types for objects that are grouped by namespace accept schema-qualified names, and will display schema-qualified names on output if the object would not be found in the current search path without being qualified. For example, myschema.mytable is acceptable input for regclass (if there is such a table). That value might be output as myschema.mytable, or just mytable, depending on the current search path.

So if your schema name is db and the sequence name is customers_sq - both surprising choices! - then this is the correct call:

SELECT nextval('db.customers_sq');

Note the single argument in single-quotes.
If any "illegal" names are involved, you'll need double-quotes. See:

Are PostgreSQL column names case-sensitive?

While the schema is listed in the search_path of the current session, schema-qualifying the object name is optional. But schema-qualifying is never a bad idea to begin with. See:

How does the search_path influence identifier resolution and the "current schema"

With a proper serial or IDENTITY id column, you wouldn't typically call nextval() and insert manually. Just omit the column from the target list of INSERT, and it will be taken care of. See:

NotNullViolation Error When Inserting Data into PostgreSQL Table with Non-Nullable ID Column

How to debug?

If it's about a SQUENCE that's owned by the column, which is the case for serial or IDENTITY columns, use the function pg_get_serial_sequence(table text, column text) to get a definitive answer:

SELECT pg_get_serial_sequence('db.customers', 'id');

The result is suitably formatted for passing to the sequence functions.

To test that an object of the given name exists, without burning sequence numbers, try one of:

SELECT 'db.customers_sq'::regclass;
SELECT to_regclass('db.customers_sq');

The first raises an exception if the object does not exist, the second just returns null. To be precise, the name might resolve to any table-like object: table, view, etc. To be sure it exists and is a sequence:

SELECT *
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname = 'db'            -- schema name
AND    c.relname = 'customers_sq'  -- sequence name
AND    c.relkind = 'S';            -- is type sequence

Or, for a short, boolean answer:

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   WHERE  c.relnamespace = 'db'::regnamespace  -- schema name
   AND    c.relname = 'customers_sq'  -- sequence name
   AND    c.relkind = 'S'             -- is type sequence
   );

See: