Mengatur ID Increment Selanjutnya pada PostgreSQL dan MySQL

[adminustrator]

Bismillahirrahmanirrahim

Bermula dari error yang saya temukan saat ingin menambahkan data lewat CMS dan keharusan saya untuk menuliskan nilai pada kolom "id" yang seharusnya terisi sendiri meski dikosongkan, maka saya mengambil keputusan bahwa ada yang keliru dengan nilai increment selanjutnya. Sebenarnya, kesulitan saya ini terjadi di PostgreSQL karena untuk menentukan nilai increment selanjutnya tidak semudah di MySQL dengan DBeaver sebagai Database GUI utama.

Pada MySQL, untuk menentukan nilai auto-increment selanjutnya bisa dilihat dan ditentukan tanpa menggunakan query. Cukup lihat pada properties tabel yang diinginkan, lalu ubah dan klik simpan.

[Image: https://cdn.hashnode.com/res/hashnode/im...ormat=webp]

Pada PostgreSQL, tampilan properties tabelnya tidak seperti di atas, melainkan seperti ini:

[Image: https://cdn.hashnode.com/res/hashnode/im...ormat=webp]

Tidak terlihat pengaturan auto-increment-nya, kan?

Yah begitu lah keterbatasan Database GUI Editor, tidak akan semudah dan sefleksibel command line pada terminal.

Setelah googling sana-sini, beginilah cara mengetahui nilai increment selanjutnya dan cara mengatur nilai increment selanjutnya pada PostgreSQL maupun MySQL.

---

PostgreSQL

Mengetahui nilai increment selanjutnya

Code:

SELECT nextval('nama_sequence');

Pertanyaannya, bagaimana mengetahui nama_sequence pada tabel tertentu itu?

Pada DBeaver, hal itu bisa didapatkan dengan melihat tab Columns dan nilai kolom Defaultnya.

[Image: https://cdn.hashnode.com/res/hashnode/im...ormat=webp]

Namun, untuk mendapatkan nama_sequence menggunakan query adalah sebagai berikut:

Code:

SELECT pg_get_serial_sequence('nama_tabel', 'nama_kolom');

-- contoh:
SELECT pg_get_serial_sequence('staff', 'id');

-- contoh response:
-- public.staff_id_seq

Maka, selanjutnya tinggal dibungkus menjadi string dan ditambahkan identifikasi kelas (class) dengan akhiran ::regclass dan tanpa awalan public.-nya.

Hasil akhirnya adalah berupa sequence_name : 'staff_id_seq'::regclass

Untuk mendapatkan nilai increment selanjutnya, bisa dengan query berikut:

Code:

SELECT nextval('staff_id_seq'::regclass);

-- output
-- kolom : nextval
-- nilai : 853

Mengatur nilai increment selanjutnya

Code:

SELECT setval('nama_sequence', nilai_baru);

Jika mengikuti contoh kolom di atas, maka query lengkapnya akan seperti ini:

Code:

SELECT setval('staff_id_seq'::regclass, 860);

---

MySQL

Mengetahui nilai increment selanjutnya

Ada dua cara atau query untuk mendapatkan hal tersebut, yakni query panjang dan pendek. Mari kita mulai dengan query panjang dahulu, yaitu seperti ini:

Code:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'nama_database'
AND TABLE_NAME = 'nama_tabel';

Contohnya seperti ini:

Code:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'daycare'
AND TABLE_NAME = 'staff';

-- output
-- kolom : AUTO_INCREMENT
-- nilai : 853

Adapun query pendeknya bisa seperti ini :

Code:

SHOW TABLE STATUS LIKE 'nama_tabel';

Contohnya seperti ini:

Code:

SHOW TABLE STATUS LIKE 'staff';

-- output
-- kolom : Auto_increment
-- nilai : 853

Kemudian lihat hasil pada kolom 'Auto_increment'. Angka yang tampil adalah yang menjadi nilai increment selanjutnya.

Mengatur nilai increment selanjutnya

Untuk mengatur atau override nilai increment selanjutnya, bisa dicapai dengan query berikut:

Code:

ALTER TABLE nama_tabel AUTO_INCREMENT = nilai_baru;

Contohnya seperti ini:

Code:

ALTER TABLE staff AUTO_INCREMENT = 860;

Ya, kira-kira demikian untuk mengetahui dan mengatur nilai increment selanjutnya pada PostgreSQL dan MySQL. Maafkan jika ada kekurangan dan mohon koreksi jika ada kekeliruan.

[adminustrator]

Tambahan

Jika males harus set satu per satu tabelnya, maka cukup jalankan query ini agar mereset seluruhnya.

Code:

DO $$
DECLARE
    rec RECORD;
BEGIN
    -- Loop untuk setiap sequence yang terhubung ke primary key
    FOR rec IN
        SELECT
            c.relname AS sequence_name,
            a.attname AS column_name,
            t.relname AS table_name
        FROM
            pg_class c
            JOIN pg_depend d ON d.objid = c.oid
            JOIN pg_attrdef ad ON ad.oid = d.refobjid
            JOIN pg_attribute a ON a.attnum = ad.adnum
            JOIN pg_class t ON t.oid = ad.adrelid
        WHERE
            c.relkind = 'S'
            AND d.classid = 'pg_class'::regclass
            AND a.attrelid = t.oid
    LOOP
        -- Menjalankan SETVAL untuk setiap sequence agar sesuai dengan nilai tertinggi dari primary key
        EXECUTE format(
            'SELECT SETVAL(''%s'', COALESCE((SELECT MAX(%I) FROM %I), 0) + 1, false);',
            rec.sequence_name,
            rec.column_name,
            rec.table_name
        );
    END LOOP;
END $$;