How to fix all sequences in postgres 9.x


-- select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') from information_schema.columns where column_default like 'nextval%';
-- Function: reset_sequence(text, text)

-- DROP FUNCTION reset_sequence(text, text);

CREATE OR REPLACE FUNCTION reset_sequence(tablename text, columnname text)
RETURNS void AS
$BODY$
DECLARE
BEGIN
EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
(SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

This entry was posted in Linux, postgres. Bookmark the permalink.

Comments are closed.