Pengujian memberi tahu bahwa sesuatu salah; debugging mencari tahu di mana dan mengapa. Debugging SQL punya dua musuh besar: pesan error yang dibaca asal jeda (padahal PostgreSQL sudah menyebut baris dan penyelesaiannya), dan query yang salah diam-diam — berjalan mulus, hasilnya meyakinkan, angkanya salah. Chapter ini menyusun proses debugging yang sistematis: baca anatomi pesan error dengan benar, bagi query besar menjadi tahap yang bisa diperiksa satu per satu, gunakan RAISE NOTICE sebagai alat cetak di tengah proses, lalu tutup dengan satu sesi debugging nyata dari gejala sampai perbaikan terverifikasi.

Membaca Pesan Error dengan Benar #

Pesan error PostgreSQL bukan satu baris acak — pesan itu berstruktur, dan setiap bagian punya peran. Bagian-bagiannya:

BagianIsinyaContoh
ERROR:ringkasan kegagalancolumn "hrga" does not exist
LINE:potongan kueri plus tanda sisir ^ menunjuk posisi salahSELECT produk, hrga FROM ...
DETAIL:data pendukung (baris yang ditolak, kunci yang bentrok)Failing row contains (...)
HINT:saran perbaikan yang sering langsung bisa dipakaiPerhaps you meant ... "harga"
CONTEXT:asal kegagalan (fungsi, trigger, baris COPY)COPY barang, line 2: ...

File berikut sengaja berisi satu salah ketik kolom. Perhatikan betapa banyak informasi yang diberikan server — posisi salah, dan saran kolom yang paling mirip:

sql anatomi-error.sql 
-- Anatomi pesan error PostgreSQL.
-- File ini sengaja memuat satu salah ketik kolom dan berakhir dengan error.
CREATE TABLE penjualan (
    id     integer PRIMARY KEY,
    produk text NOT NULL,
    harga  integer NOT NULL
);

INSERT INTO penjualan VALUES
    (1, 'Kopi Bubuk', 25000),
    (2, 'Teh Melati', 15000);

-- "harga" salah ketik menjadi "hrga"
SELECT produk, hrga FROM penjualan ORDER BY produk;

Output yang Dihasilkan:

text 
psql:anatomi-error.sql:14: ERROR:  column "hrga" does not exist
LINE 1: SELECT produk, hrga FROM penjualan ORDER BY produk;
                       ^
HINT:  Perhaps you meant to reference the column "penjualan.harga".

Tiga bagian bekerja bersama: ERROR menyebut nama kolom yang tidak ditemukan, LINE menunjuk tepat ke hrga di tengah kueri, dan HINT menebak maksud Anda — penjualan.harga — dengan editor levenshtein sederhana. Kebiasaan yang perlu dibangun: baca dari atas ke bawah, selengkapnya. Dalam eksekusi batch, CONTEXT juga menunjukkan file dan nomor baris skrip tempat kegagalan terjadi, sehingga Anda langsung tahu perintah mana yang gagal tanpa menebak-nebak.

Membagi Query Menjadi Tahap yang Bisa Diperiksa #

Query panjang gagal di suatu titik yang tak terlihat. Penyembuhnya adalah memecahnya: jalankan tiap CTE secara terpisah dan periksa hasil antara sebelum menggabungkan kembali. Bagian “Sesi Debugging Nyata” di bawah memperagakan teknik ini dari awal sampai akhir — jadi di sini cukup prinsipnya:

  1. Jalankan CTE paling dalam sendiri — bila bahan mentahnya sudah salah, tidak ada gunanya memeriksa lapisan atasnya.
  2. Naik satu lapisan per langkah — bandingkan hasil tiap tahap dengan perkiraan manual Anda.
  3. Komentari bagian yang dicurigai — SELECT tanpa JOIN yang dicurigai, atau kondisi WHERE yang dilepas sementara, mempersempit wilayah salah dengan cepat.
  4. Periksa tipe datanya, bukan hanya nilainya — fungsi pg_typeof() mengungkap jebakan konversi yang tak terlihat di hasil.

Untuk nilai di tengah prosedur PL/pgSQL, RAISE NOTICE berperan sebagai printf-nya SQL:

bash debug-notice.sh 
#!/usr/bin/env bash
# RAISE NOTICE adalah "printf debugging" di dalam database: pesannya keluar
# lewat stderr, bukan hasil query. Jalankan psql dengan 2>&1 agar keduanya
# tampil berurutan di satu aliran.
psql -X 2>&1 <<'SQL'
DO $$
DECLARE
    r        record;
    v_total  integer := 0;
BEGIN
    FOR r IN SELECT id, kategori, harga FROM transaksi ORDER BY id LOOP
        v_total := v_total + r.harga;
        RAISE NOTICE 'transaksi % (kategori %): harga % -> total sementara %',
                     r.id, r.kategori, r.harga, v_total;
    END LOOP;
    RAISE NOTICE 'total semua transaksi: %', v_total;
END $$;

-- Silang-cek: hasil loop harus sama dengan agregat SQL.
SELECT SUM(harga) AS total_silang_cek FROM transaksi;
SQL

Output yang Dihasilkan:

text 
ERROR:  relation "transaksi" does not exist
LINE 1: SELECT id, kategori, harga FROM transaksi ORDER BY id
                                        ^
QUERY:  SELECT id, kategori, harga FROM transaksi ORDER BY id
CONTEXT:  PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows
ERROR:  relation "transaksi" does not exist
LINE 1: SELECT SUM(harga) AS total_silang_cek FROM transaksi;
                                                   ^

Perhatikan dua hal dari output tersebut. Pertama, semua baris NOTICE: muncul sebelum tag DO — NOTICE dikirim ke stderr klien sementara hasil query mengalir lewat stdout; menggabungkannya dengan 2>&1 (seperti skrip di atas) adalah cara melihat keduanya dalam satu aliran. Kedua, total yang dihitung loop (69000) cocok dengan silang-cek SUM(harga) — teknik “hitung dua cara berbeda, bandingkan” yang murah dan sering menangkap bug logika loop.

Sesi Debugging Nyata: Rata-rata yang Terpotong #

Laporan bug dari tim keuangan Toko Maju Jaya: “rata-rata harga kategori makanan seharusnya 18333.33, sistem menampilkan 18333”. Query-nya berjalan tanpa error. Beginilah cara menggarapnya langkah demi langkah — semuanya bisa dijalankan ulang dari file yang disertakan.

Langkah 1: Reproduksi dan Ekspektasi #

Reproduksi adalah syarat debugging — bug yang tidak bisa direproduksi tidak bisa diperbaiki. File berikut membuat fixture lima transaksi lalu menjalankan query yang dilaporkan bermasalah:

sql query-rusak.sql 
-- Kasus debugging: laporan rata-rata harga per kategori Toko Maju Jaya
-- "kehilangan" desimalnya. Query berjalan mulus, tidak ada error --
-- hasilnya saja yang salah.
CREATE TABLE transaksi (
    id       integer PRIMARY KEY,
    kategori text NOT NULL,
    harga    integer NOT NULL
);

INSERT INTO transaksi VALUES
    (1, 'makanan', 15000),
    (2, 'makanan', 20000),
    (3, 'makanan', 20000),
    (4, 'minuman',  6000),
    (5, 'minuman',  8000);

-- Query yang dilaporkan bermasalah oleh tim keuangan.
SELECT kategori,
       SUM(harga) / COUNT(*) AS rata_rata_harga
FROM transaksi
GROUP BY kategori
ORDER BY kategori;

-- Ekspektasi manual untuk pembanding:
--   makanan: (15000 + 20000 + 20000) / 3 = 18333.33...
--   minuman: (6000 + 8000) / 2          = 7000

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 5
 kategori | rata_rata_harga 
----------+-----------------
 makanan  |           18333
 minuman  |            7000
(2 rows)

Perhatikan pola gejalanya: makanan menampilkan 18333 (terpotong), tetapi minuman menampilkan 7000 — benar. Pembagian 14000/2 memang menghasilkan bilangan bulat, jadi separuh laporan tampak normal. Bug semacam ini lolos dari pemeriksaan cepat karena hanya kasus yang “tidak habis dibagi” yang tampak salah.

Langkah 2: Hipotesis dan Pembagian Tahap #

Selisihnya di bawah 1 dan selalu ke arah bawah — hipotesis paling kuat: pembagian integer memotong desimal. Uji hipotesis itu dengan membagi query menjadi tahap:

sql debug-tahap.sql 
-- Debugging dengan membagi query: periksa setiap tahap perantara.
-- Tahap 1: agregat mentah per kategori -- apakah total dan jumlahnya benar?
WITH tahap AS (
    SELECT kategori,
           SUM(harga)  AS total_harga,
           COUNT(*)    AS jumlah_transaksi
    FROM transaksi
    GROUP BY kategori
)
SELECT kategori, total_harga, jumlah_transaksi
FROM tahap
ORDER BY kategori;

-- Tahap 2: tipe data hasil pembagian -- integer atau numeric?
WITH tahap AS (
    SELECT kategori,
           SUM(harga)  AS total_harga,
           COUNT(*)    AS jumlah_transaksi
    FROM transaksi
    GROUP BY kategori
)
SELECT kategori,
       pg_typeof(total_harga)                 AS tipe_total,
       pg_typeof(total_harga / jumlah_transaksi) AS tipe_pembagian
FROM tahap
ORDER BY kategori;

-- Tahap 3: eksperimen kecil membandingkan kedua pembagian.
SELECT 55000 / 3           AS pembagian_integer,
       55000::numeric / 3  AS pembagian_numeric;

Output yang Dihasilkan:

text 
 kategori | total_harga | jumlah_transaksi 
----------+-------------+------------------
 makanan  |       55000 |                3
 minuman  |       14000 |                2
(2 rows)

 kategori | tipe_total | tipe_pembagian 
----------+------------+----------------
 makanan  | bigint     | bigint
 minuman  | bigint     | bigint
(2 rows)

 pembagian_integer | pembagian_numeric  
-------------------+--------------------
             18333 | 18333.333333333333
(1 row)

Tahap 1 membuktikan agregat mentahnya benar: total 55000 dengan 3 transaksi (makanan) dan 14000 dengan 2 transaksi (minuman). Artinya SUM dan COUNT tidak bersalah. Tahap 2 menjatuhkan vonisnya: pg_typeof melaporkan total_harga bertipe bigint, dan total_harga / jumlah_transaksi juga bigint — pembagian dua integer di PostgreSQL menghasilkan integer, dengan desimal dipotong, bukan dibulatkan. Tahap 3 mengunci bukti dengan eksperimen mini: 55000 / 3 menghasilkan 18333, sedangkan 55000::numeric / 3 menghasilkan 18333.333333333333.

Langkah 3: Perbaikan dan Verifikasi #

Perbaikannya satu kata: cast salah satu operand ke numeric sebelum pembagian. Lalu — sama pentingnya — perbaikan diverifikasi dengan pola EXCEPT dua arah dari chapter pertama:

sql query-perbaikan.sql 
-- Perbaikan: cast satu operand ke numeric sebelum membagi,
-- lalu verifikasi hasil terhadap ekspektasi manual dengan EXCEPT dua arah.
WITH hasil AS (
    SELECT kategori,
           SUM(harga)::numeric / COUNT(*) AS rata_rata_harga
    FROM transaksi
    GROUP BY kategori
),
ekspektasi AS (
    VALUES ('makanan', 55000::numeric / 3),
           ('minuman', 14000::numeric / 2)
)
SELECT kategori, rata_rata_harga FROM hasil ORDER BY kategori;

-- Verifikasi: selisih dua arah harus kosong.
WITH hasil AS (
    SELECT kategori,
           SUM(harga)::numeric / COUNT(*) AS rata_rata_harga
    FROM transaksi
    GROUP BY kategori
),
ekspektasi AS (
    VALUES ('makanan', 55000::numeric / 3),
           ('minuman', 14000::numeric / 2)
),
selisih AS (
    SELECT 1 FROM hasil     EXCEPT SELECT 1 FROM ekspektasi
    UNION ALL
    SELECT 1 FROM ekspektasi EXCEPT SELECT 1 FROM hasil
)
SELECT CASE WHEN count(*) = 0
            THEN 'LULUS: hasil perbaikan identik dengan ekspektasi'
            ELSE 'GAGAL: ' || count(*) || ' baris selisih'
       END AS status_uji
FROM selisih;

Output yang Dihasilkan:

text 
 kategori |    rata_rata_harga    
----------+-----------------------
 makanan  |    18333.333333333333
 minuman  | 7000.0000000000000000
(2 rows)

                    status_uji                    
--------------------------------------------------
 LULUS: hasil perbaikan identik dengan ekspektasi
(1 row)

Ekspektasi ditulis dari aritmetika manual pada fixture: (15000 + 20000 + 20000) / 3 dan (6000 + 8000) / 2. Verifikasi LULUS menutup sesi debugging. Dua catatan penutup: hasil kategori minuman kini tampil sebagai 7000.0000000000000000 — tipe numeric mempertahankan tempat desimalnya, itulah perilaku tipe ini; dan untuk kasus spesifik “rata-rata”, AVG(harga) sebenarnya menghasilkan numeric secara otomatis tanpa pembagian manual — sebagian bug SQL lahir karena kita membuat ulang alat yang sudah tersedia.

Galeri Bug SQL Umum #

Enam jebakan yang menyumbang sebagian besar sesi debugging SQL, lengkap dengan gejala khasnya:

BugGejala khasPerbaikan
Kolom ambigu di joinERROR: column reference "nama" is ambiguouskualifikasi a.nama, b.nama dengan alias tabel
NOT IN dengan subquery yang mengandung NULLhasil kosong atau baris hilang, tanpa errorganti ke NOT EXISTS, atau saring NULL di subquery
Pembagian integerangka “terpotong” ke bawah (kasus sesi di atas)cast ::numeric satu operand sebelum membagi
Kebingungan timezonedata jam “geser” beberapa jam antar serverpakai timestamptz, set timezone sesi secara eksplisit
Cast implisit yang gagalERROR: operator does not exist: text = integersamakan tipe dengan cast eksplisit saat membandingkan
Kolom tanpa agregat di GROUP BYERROR: column ... must appear in the GROUP BY clausemasukkan kolom ke GROUP BY atau bungkus dengan agregat

EXPLAIN sebagai Alat Debug #

Pembahasan di atas menangani query yang salah hasil. Keluhan lain yang sama seringnya: query yang benar tetapi tiba-tiba lambat. Untuk itu alat debuggernya adalah EXPLAIN — rencana eksekusi menunjukkan jalur yang dipilih planner: sequential scan yang tidak diharapkan, join yang salah strategi, atau estimasi baris yang meleset jauh dari kenyataan (tanda statistik usang yang perlu ANALYZE). Cara membaca rencana eksekusi sudah dibahas tuntas di chapter EXPLAIN dan Membaca Query Execution Plan .

Query dan data yang sudah benar pun belum lengkap sampai perilakunya terkunci dengan pengujian yang bisa dijalankan ulang. Objek database berikutnya yang menunggu giliran itu adalah function dan trigger — kode yang berjalan tersembunyi di dalam server, dengan pola pengujian tersendiri untuk membedahnya.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

anatomi-error.sql

Unduh berkas
sql 
-- Anatomi pesan error PostgreSQL.
-- File ini sengaja memuat satu salah ketik kolom dan berakhir dengan error.
CREATE TABLE penjualan (
    id     integer PRIMARY KEY,
    produk text NOT NULL,
    harga  integer NOT NULL
);

INSERT INTO penjualan VALUES
    (1, 'Kopi Bubuk', 25000),
    (2, 'Teh Melati', 15000);

-- "harga" salah ketik menjadi "hrga"
SELECT produk, hrga FROM penjualan ORDER BY produk;

debug-notice.sh

Unduh berkas
bash 
#!/usr/bin/env bash
# RAISE NOTICE adalah "printf debugging" di dalam database: pesannya keluar
# lewat stderr, bukan hasil query. Jalankan psql dengan 2>&1 agar keduanya
# tampil berurutan di satu aliran.
psql -X 2>&1 <<'SQL'
DO $$
DECLARE
    r        record;
    v_total  integer := 0;
BEGIN
    FOR r IN SELECT id, kategori, harga FROM transaksi ORDER BY id LOOP
        v_total := v_total + r.harga;
        RAISE NOTICE 'transaksi % (kategori %): harga % -> total sementara %',
                     r.id, r.kategori, r.harga, v_total;
    END LOOP;
    RAISE NOTICE 'total semua transaksi: %', v_total;
END $$;

-- Silang-cek: hasil loop harus sama dengan agregat SQL.
SELECT SUM(harga) AS total_silang_cek FROM transaksi;
SQL

debug-tahap.sql

Unduh berkas
sql 
-- Debugging dengan membagi query: periksa setiap tahap perantara.
-- Tahap 1: agregat mentah per kategori -- apakah total dan jumlahnya benar?
WITH tahap AS (
    SELECT kategori,
           SUM(harga)  AS total_harga,
           COUNT(*)    AS jumlah_transaksi
    FROM transaksi
    GROUP BY kategori
)
SELECT kategori, total_harga, jumlah_transaksi
FROM tahap
ORDER BY kategori;

-- Tahap 2: tipe data hasil pembagian -- integer atau numeric?
WITH tahap AS (
    SELECT kategori,
           SUM(harga)  AS total_harga,
           COUNT(*)    AS jumlah_transaksi
    FROM transaksi
    GROUP BY kategori
)
SELECT kategori,
       pg_typeof(total_harga)                 AS tipe_total,
       pg_typeof(total_harga / jumlah_transaksi) AS tipe_pembagian
FROM tahap
ORDER BY kategori;

-- Tahap 3: eksperimen kecil membandingkan kedua pembagian.
SELECT 55000 / 3           AS pembagian_integer,
       55000::numeric / 3  AS pembagian_numeric;

query-perbaikan.sql

Unduh berkas
sql 
-- Perbaikan: cast satu operand ke numeric sebelum membagi,
-- lalu verifikasi hasil terhadap ekspektasi manual dengan EXCEPT dua arah.
WITH hasil AS (
    SELECT kategori,
           SUM(harga)::numeric / COUNT(*) AS rata_rata_harga
    FROM transaksi
    GROUP BY kategori
),
ekspektasi AS (
    VALUES ('makanan', 55000::numeric / 3),
           ('minuman', 14000::numeric / 2)
)
SELECT kategori, rata_rata_harga FROM hasil ORDER BY kategori;

-- Verifikasi: selisih dua arah harus kosong.
WITH hasil AS (
    SELECT kategori,
           SUM(harga)::numeric / COUNT(*) AS rata_rata_harga
    FROM transaksi
    GROUP BY kategori
),
ekspektasi AS (
    VALUES ('makanan', 55000::numeric / 3),
           ('minuman', 14000::numeric / 2)
),
selisih AS (
    SELECT 1 FROM hasil     EXCEPT SELECT 1 FROM ekspektasi
    UNION ALL
    SELECT 1 FROM ekspektasi EXCEPT SELECT 1 FROM hasil
)
SELECT CASE WHEN count(*) = 0
            THEN 'LULUS: hasil perbaikan identik dengan ekspektasi'
            ELSE 'GAGAL: ' || count(*) || ' baris selisih'
       END AS status_uji
FROM selisih;

query-rusak.sql

Unduh berkas
sql 
-- Kasus debugging: laporan rata-rata harga per kategori Toko Maju Jaya
-- "kehilangan" desimalnya. Query berjalan mulus, tidak ada error --
-- hasilnya saja yang salah.
CREATE TABLE transaksi (
    id       integer PRIMARY KEY,
    kategori text NOT NULL,
    harga    integer NOT NULL
);

INSERT INTO transaksi VALUES
    (1, 'makanan', 15000),
    (2, 'makanan', 20000),
    (3, 'makanan', 20000),
    (4, 'minuman',  6000),
    (5, 'minuman',  8000);

-- Query yang dilaporkan bermasalah oleh tim keuangan.
SELECT kategori,
       SUM(harga) / COUNT(*) AS rata_rata_harga
FROM transaksi
GROUP BY kategori
ORDER BY kategori;

-- Ekspektasi manual untuk pembanding:
--   makanan: (15000 + 20000 + 20000) / 3 = 18333.33...
--   minuman: (6000 + 8000) / 2          = 7000