Skip to content

Instantly share code, notes, and snippets.

@hasanisaeed
Last active March 20, 2024 08:55
Show Gist options
  • Save hasanisaeed/0cb6e9a2b30702c0517d1e11e743d842 to your computer and use it in GitHub Desktop.
Save hasanisaeed/0cb6e9a2b30702c0517d1e11e743d842 to your computer and use it in GitHub Desktop.
Postgres: reset sequence from max(id) in all table.
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( ''' || sequence_name || ''', ' || '(SELECT MAX(' || columnname ||
') FROM ' || tablename || ')' || '+1)';
END;
$body$ LANGUAGE 'plpgsql';
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%';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment