Last active
June 3, 2025 16:29
-
-
Save aliemre/a689ced32e8a9b26f1ee422f232b2ffc to your computer and use it in GitHub Desktop.
PostgreSQL Sync Sequence for Duplicate Key Error
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
| # For Single Table | |
| SELECT setval('public."upload_file_morph_id_seq"', | |
| (SELECT MAX(id) FROM public.upload_file_morph) | |
| ); | |
| # For All Tables | |
| 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%'; |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
New Command
DO $$ DECLARE rec RECORD; BEGIN FOR rec IN SELECT n.nspname AS schema_name, s.relname AS sequence_name, t.relname AS table_name, a.attname AS column_name FROM pg_class s JOIN pg_depend d ON d.objid = s.oid JOIN pg_class t ON d.refobjid = t.oid JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid JOIN pg_namespace n ON n.oid = s.relnamespace WHERE s.relkind = 'S' AND d.deptype = 'a' LOOP RAISE NOTICE 'Updating sequence: %.%', rec.schema_name, rec.sequence_name; EXECUTE format( 'SELECT setval(''%I.%I'', COALESCE((SELECT MAX(%I) FROM %I.%I), 1))', rec.schema_name, rec.sequence_name, rec.column_name, rec.schema_name, rec.table_name ); END LOOP; END $$;