PostgreSQL Sync Sequence for Duplicate Key Error

postgresql-sync-sequence.sql

# 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%';

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 $$;