Postgres: Understanding Primary Key Sequences

Question

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;

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
– clamp, Commented Jan 5, 2020 at 21:28
Does this answer your question? List all sequences in a Postgres db 8.1 with SQL — philipxy
– philipxy, Commented Jan 6, 2020 at 2:30

aroooo · Accepted Answer · 2020-01-05 21:39:46Z

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

edited Jan 5, 2020 at 21:39

user330315

answered Jan 5, 2020 at 21:35

aroooo

5,1268 gold badges54 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

philipxy Over a year ago

The question is a duplicate, flag/vote it as such, don't answer it.

Collectives™ on Stack Overflow

Postgres: Understanding Primary Key Sequences

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related