Last active
September 17, 2023 16:30
-
-
Save sagar290/a70f34b599be8b2b257ad8d928fe5a27 to your computer and use it in GitHub Desktop.
Resetting Auto-Incrementing IDs in PostgreSQL
This file contains hidden or 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
DO | |
$$ | |
DECLARE | |
rec RECORD; | |
lastId INT; | |
BEGIN | |
FOR rec IN (SELECT table_name | |
FROM information_schema.tables | |
WHERE table_schema = (SELECT current_schema()) | |
AND table_type = 'BASE TABLE') | |
LOOP | |
RAISE NOTICE 'Processing table: %', rec.table_name; | |
BEGIN | |
EXECUTE 'SELECT COALESCE(MAX(id), 0) FROM ' || rec.table_name INTO lastId; | |
EXECUTE 'ALTER SEQUENCE ' || rec.table_name || '_id_seq RESTART WITH ' || (lastId + 1); | |
EXCEPTION | |
WHEN others THEN | |
RAISE NOTICE 'Error occurred for table %: %', rec.table_name, SQLERRM; | |
END; | |
END LOOP; | |
END; | |
$$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment