1

I know this question is kind of silly, I need to run these queries to see if the values are out of sync, but I get an error like 'relation does not exist':

 SELECT MAX(the_primary_key) FROM the_table;   
 SELECT nextval('the_primary_key_sequence');

I have a table named "Audit" with the primary key column 'auditID'. When I run the first query I got the result:

SELECT MAX('auditID') FROM "Audit";
   max
---------
 auditID
 (1 row)

but the max should be more than 10000.

Then I run the second query and I get the error 'relation "the_primary_key_sequence" or "Audit_auditID_seq" does not exist'. How can I check if any primary key sequence exists or not?

Error:

select setval('Audit_auditID_seq', 171832, true);
ERROR:  relation "audit_auditid_seq" does not exist
LINE 1: select setval('Audit_auditID_seq', 171832, true);
ccy
  • 341
  • 6
  • 18
  • This is another nice example why one should avoid those dreaded quoted identifiers. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names –  Jan 15 '21 at 12:57

2 Answers2

6

I figure out all my questions, the quote mark is really sensitive in this case;

 SELECT MAX("auditID") FROM "Audit"; 
   max
--------
 171832
 (1 row)

 SELECT nextval('"Audit_auditID_seq"');
 ----
 139801

and finally make the value as the same:

 select setval('"Audit_auditID_seq"', 171832, true);

If you need to find out the sequence, use

 \d "table_name";
ccy
  • 341
  • 6
  • 18
0

If you don't know the sequence name, but your column is a serial (or identity in modern Postgres versions) you can use:

select setval(pg_get_serial_sequence('"Audit"', 'auditID'), max("auditID"))
from "Audit";

Online example