KAL
Menu

ALTER TABLE

Mengubah struktur tabel yang sudah berjalan secara aman, dari menambah kolom hingga mengubah tipe.

Daftar isi

Topik ini membahas ALTER TABLE, pernyataan yang mengubah struktur tabel yang sudah berjalan, mulai dari menambah kolom, mengganti nama, mengubah tipe, sampai pola perubahan yang aman untuk tabel besar. Semua contoh berjalan pada tabel latihan yang dibangun dan dihapus di dalam topik, sehingga dataset utama tidak tersentuh.

Menambah dan mengganti nama kolom#

ADD COLUMN menambah kolom baru, dan PostgreSQL modern mengeksekusinya seketika tanpa menulis ulang baris lama, termasuk ketika kolomnya diberi DEFAULT. Kolom baru yang wajib terisi membutuhkan DEFAULT, karena baris lama harus memperoleh nilai tanpa dibaca satu per satu.

bash 
# tambah kolom dengan default, lalu ganti nama kolom
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE staf (
    id   SERIAL PRIMARY KEY,
    nama TEXT NOT NULL
);
ALTER TABLE staf ADD COLUMN cabang TEXT NOT NULL DEFAULT 'Pusat';
INSERT INTO staf (nama, cabang) VALUES ('Hana Zahira', 'Cabang Timur');
ALTER TABLE staf RENAME COLUMN cabang TO unit;
SELECT * FROM staf;
DROP TABLE staf;
SQL
text Keluaran 
CREATE TABLE
ALTER TABLE
INSERT 0 1
ALTER TABLE
 id |    nama     |     unit
----+-------------+--------------
  1 | Hana Zahira | Cabang Timur
(1 row)

DROP TABLE

RENAME COLUMN mengubah nama tanpa menyentuh data, dan bentuk serupa tersedia untuk tabel serta constraint melalui RENAME TO dan RENAME CONSTRAINT. Perubahan nama adalah operasi murah, tetapi efeknya langsung terasa pada query lama yang masih menyebut nama sebelumnya, sehingga perubahannya dikomunikasikan terlebih dahulu.

Mengubah tipe dan keterangan kolom#

ALTER COLUMN TYPE mengubah tipe kolom, dan operasi ini menulis ulang seluruh tabel karena representasi nilainya berubah. Klausa USING memandu konversi ketika jalur konversinya perlu dinyatakan, misalnya mengubah teks menjadi bilangan.

bash 
# ubah tipe kolom dengan USING
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE staf (
    id        SERIAL PRIMARY KEY,
    nama      TEXT NOT NULL,
    nomor_id  TEXT
);
INSERT INTO staf (nama, nomor_id) VALUES ('Hana Zahira', '1987'), ('Ivan Gunawan', '1992');
ALTER TABLE staf ALTER COLUMN nomor_id TYPE INTEGER USING nomor_id::integer;
SELECT nama, nomor_id FROM staf ORDER BY nomor_id;
DROP TABLE staf;
SQL
text Keluaran 
CREATE TABLE
INSERT 0 2
ALTER TABLE
     nama     | nomor_id
--------------+----------
 Hana Zahira  |     1987
 Ivan Gunawan |     1992
(2 rows)

DROP TABLE

Keterangan kolom diubah dengan pernyataan yang lebih ringan, yaitu SET NOT NULL dan DROP NOT NULL untuk keharusan terisi, serta SET DEFAULT dan DROP DEFAULT untuk nilai bawaan. SET NOT NULL memeriksa seluruh baris terlebih dahulu, sehingga baris lama yang masih kosong harus diisi sebelum aturannya ditegakkan.

Pola perubahan yang aman pada tabel besar#

Perubahan struktur pada tabel besar menuntut disiplin karena sebagian bentuk ALTER TABLE memegang kunci yang menahan seluruh lalu lintas. Tiga pola yang aman menjadi kebiasaan baku, dan ketiganya memecah perubahan besar menjadi langkah kecil yang cepat.

  1. Tambahkan kolom baru tanpa DEFAULT dan tanpa NOT NULL, isi nilainya bertahap melalui UPDATE berkelompok, lalu tegakkan SET NOT NULL setelah seluruh baris terisi.
  2. Untuk CHECK yang berat, tambahkan dengan NOT VALID agar berlaku pada penulisan baru, jalankan VALIDATE CONSTRAINT di luar jam sibuk, dan validasinya berjalan dengan kunci yang lebih lunak.
  3. Untuk perubahan tipe pada tabel sangat besar, buat kolom baru bertipe tujuan, salin nilainya bertahap, arahkan penulisan ke kolom baru, lalu jatuhkan kolom lama setelah aplikasi sepenuhnya berpindah.

Urutan langkah ketiga terlihat panjang, tetapi setiap langkahnya singkat dan dapat dibatalkan, sedangkan satu ALTER COLUMN TYPE langsung menahan tabel sepanjang penulisan ulangnya. Aturan umumnya sederhana, yaitu perubahan yang murah untuk tabel kecil belum tentu murah untuk tabel besar, dan keputusan diperiksa dengan membaca bentuk operasinya di dokumentasi resmi.

Kesalahan umum#

Kesalahan pertama adalah menjalankan ALTER COLUMN TYPE pada tabel jutaan baris di jam sibuk, yang menahan kunci sepanjang penulisan ulang dan melumpuhkan aplikasi. Kesalahan kedua adalah DROP COLUMN langsung pada kolom yang masih dipakai query lama, padahal pola yang aman berhenti menulis ke kolom itu lebih dahulu, memantau tidak ada pembaca tersisa, dan menghapusnya belakangan.

Kesalahan ketiga adalah mengira SET NOT NULL murah seperti ADD COLUMN. Pemeriksaan seluruh baris tetap berjalan, sehingga pada tabel besar ia ditempuh melalui pola CHECK NOT VALID yang serupa atau di luar jam sibuk. Kesalahan keempat adalah mengganti nama kolom tanpa memeriksa view dan fungsi yang merujuknya, karena nama baru membuat objek tersebut gagal pada pembacaan berikutnya.

Ringkasan#

  • ADD COLUMN pada PostgreSQL modern cepat tanpa penulisan ulang, dan kolom NOT NULL membutuhkan DEFAULT untuk baris lama.
  • ALTER COLUMN TYPE menulis ulang tabel, dengan USING sebagai panduan konversi, sehingga hanya untuk tabel yang cukup kecil atau di luar jam sibuk.
  • SET NOT NULL, DROP NOT NULL, SET DEFAULT, dan DROP DEFAULT mengatur keterangan kolom secara ringan.
  • Perubahan aman pada tabel besar memecah pekerjaan menjadi langkah pendek, dengan NOT VALID dan kolom pendamping sebagai alat utamanya.

Referensi#