Cara Set Ulang semua tabel Squence pada postgres

26/01/2024 239

Tutorial
Cara Set Ulang semua tabel Squence pada postgres

berikut contoh script untuk menyetel ulang semua tabel auto increment pada postgres mengingat di postgres memakai metode squence :

DO $$
DECLARE
    r record;
BEGIN
    FOR r IN
        SELECT c.relname AS sequence_name, 
               t.relname AS table_name, 
               a.attname AS column_name
        FROM pg_class c
        JOIN pg_depend d ON d.objid = c.oid
        JOIN pg_class t ON d.refobjid = t.oid
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
        WHERE c.relkind = 'S'
    LOOP
        EXECUTE 'SELECT setval(''' || r.sequence_name || ''', COALESCE((SELECT MAX(' || r.column_name || ')+1 FROM ' || r.table_name || '), 1), false)';
    END LOOP;
END $$;

Posted By
Avatar
Super Admin
Web Developer, Designer, and Teacher
Top Reads