Skip to content

Instantly share code, notes, and snippets.

Last active September 9, 2021 07:50
What would you like to do?
Fix Postgres 9.x Sequences

PostgreSQL 9.x Sequence Fixing Script

This script is intended to automatically fix the sequence numbers for all tables in the current database.

This is accomplished through the use of the setval() command, which we provide with the next ID value we wish to make use of. We use the setval(sequence, number, is_called) overload and set is_called = false in conjunction with COALESCE(MAX + 1, 1) to ensure that, with an empty table, the next sequence value is 1 as expected.

-- This function is responsible for fixing the id sequences for all tables in
-- a database. It is useful when you have used pg_dump to restore data from another
-- database.
-- This function has been written such that it is easy to see what it does (i.e.
-- lots of variables etc.) however it can be rewritten to execute in a single line
-- should you prefer a bit of extra performance and don't mind not knowing which
-- sequences were updated.
CREATE OR REPLACE FUNCTION fix_sequence(tableName text, columnName text)
nextValue int;
expectedNextValue int;
sequenceName text;
EXECUTE 'SELECT pg_get_serial_sequence(''' || tableName || ''', ''' || columnName || ''')' INTO sequenceName;
SELECT nextval(sequenceName) INTO nextValue;
EXECUTE 'SELECT COALESCE(MAX(' || columnName || ') + 1, 1) FROM ' || tableName INTO expectedNextValue;
IF nextValue < expectedNextValue THEN
EXECUTE 'SELECT setval(''' || sequenceName || ''', ' || expectedNextValue || ', false)';
RETURN true;
RETURN false;
-- Here's a faster (but harder to understand) single line version which you can use instead, if you wish.
-- CREATE OR REPLACE FUNCTION fix_sequence(tableName text, columnName text)
-- RETURNS void AS $$
-- EXECUTE 'SELECT setval(pg_get_serial_sequence(''' || tableName || ''', ''' || columnName || '''), (SELECT COALESCE (MAX(' || columnName || ') + 1, 1) FROM ' || tableName || '), false)';
-- END;
-- $$ LANGUAGE plpgsql VOLATILE;
-- Execute the fix
table_name || '_' || column_name || '_seq' AS Sequence,
fix_sequence(table_name, column_name) AS ResetResult
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';
-- Cleanup the function
DROP FUNCTION fix_sequence(text, text);
Copy link

Thanks for sharing, you saved the day!

Copy link

Thanks! This is what I was looking for :)

Copy link

OOOh. It looks like this should help with AWS Postgres to Aurora migration bug.

Copy link

nrowland commented Oct 9, 2018

For multiple schemas, add schema name as a param and set the search path in the function.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment