Setting the Postgres 9.6 Current Sequence Values of All Tables to Max ID values of all Parent Tables

Question

Postgres allows for auto-increment of the Primary Key value SERIAL is used at DDL. With this, a sequence property for the table is also created along with the table. However, when tuples are inserted manually, the sequence current value does not get updated.

For One table at a time we can use SQL like this SELECT setval('<table>_id_seq', COALESCE((SELECT MAX(id) FROM your_table), 1), false); wherein we need to manually set the <table>_id_seq' and id' attribute name.

The problem becomes labourious if there are multiple tables. So is there any generalised method to set current value for all sequences to the MAX(<whatever_sequence_attribute_is>)?

Answer 1

Yup! Got it

First run the following SQL

SELECT 'SELECT setval(' || quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) || ', COALESCE((SELECT MAX(' ||quote_ident(pg_attribute.attname)|| ') FROM ' || quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| '), 1), false)' || ';' FROM pg_depend INNER JOIN pg_class AS class_sequence ON class_sequence.oid = pg_depend.objid AND class_sequence.relkind = 'S' INNER JOIN pg_class AS class_table ON class_table.oid = pg_depend.refobjid INNER JOIN pg_attribute ON pg_attribute.attrelid = class_table.oid AND pg_depend.refobjsubid = pg_attribute.attnum INNER JOIN pg_namespace as table_namespace ON table_namespace.oid = class_table.relnamespace INNER JOIN pg_namespace AS sequence_namespace ON sequence_namespace.oid = class_sequence.relnamespace ORDER BY sequence_namespace.nspname, class_sequence.relname;

The result window will show as many SQL statements as sequences are there in the DB. Copy all the SQL statements and Execute all. The current value of all sequences will get updated.

Note: Please Note that the major SQL will only generate SQLs and WILL NOT UPDATE the sequences. One has to copy all the SQL Statements from the Result and execute.