1

My database has fallen out of sync, which lead me to this question: How to reset postgres' primary key sequence when it falls out of sync? (Copied Below)

However, I don't quite understand some things here: What is 'your_table_id_seq'? I have no clue where to find this. Doing some digging, I found a table called pg_sequence in pg_catalog. Is it related to this? I can't see any way to relate that data back to my table though.

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;
aroooo
  • 4,726
  • 8
  • 47
  • 81
  • 1
    In psql do `\d your_table` to see the sequence associated with it (if any). It should be the default entry of your PK column. – clamp Jan 05 '20 at 21:28
  • 1
    Does this answer your question? [List all sequences in a Postgres db 8.1 with SQL](https://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql) – philipxy Jan 06 '20 at 02:30

1 Answers1

0

The following query gives names of all sequences.

SELECT c.relname 
FROM pg_class c 
WHERE c.relkind = 'S';

Typically a sequence is named as ${table}_id_seq.

I found the answer in this question: List all sequences in a Postgres db 8.1 with SQL

aroooo
  • 4,726
  • 8
  • 47
  • 81