Last active
March 20, 2024 08:55
-
-
Save hasanisaeed/0cb6e9a2b30702c0517d1e11e743d842 to your computer and use it in GitHub Desktop.
Postgres: reset sequence from max(id) in all table.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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