Bab 1 — Fondasi
Mengenal PostgreSQL
RDBMS
Menyimpan data relasional dengan dukungan transaksi dan constraint yang kuat.
Open Source
Dapat digunakan dan dikembangkan secara terbuka.
Standar SQL
Mendukung banyak fitur SQL standar dan ekstensi modern.
Extensible
Mendukung tipe data, function, operator, dan extension tambahan.
Bab 2 — Alat Kerja
psql, pgAdmin, koneksi, dan database awal
psql
psql -U postgres -h localhost -d postgres
Perintah meta
\l
\c akademik
\dn
\dt
\d siswa
\q
\c akademik
\dn
\dt
\d siswa
\q
Database awal
CREATE DATABASE akademik;
COMMENT ON DATABASE akademik IS 'Database pembelajaran';
COMMENT ON DATABASE akademik IS 'Database pembelajaran';
Bab 3 — Schema
Database, schema, search_path, dan organisasi objek
Membuat schema
CREATE SCHEMA sekolah;
CREATE SCHEMA keuangan;
SET search_path TO sekolah, public;
CREATE SCHEMA keuangan;
SET search_path TO sekolah, public;
Nama lengkap objek
SELECT * FROM sekolah.siswa;
SELECT * FROM keuangan.pembayaran;
SELECT * FROM keuangan.pembayaran;
Schema membantu memisahkan modul dan menghindari benturan nama tabel.
Bab 4 — Tipe Data
Tipe data PostgreSQL yang kaya
| Tipe | Kegunaan |
|---|---|
| INTEGER / BIGINT | Bilangan bulat |
| NUMERIC(p,s) | Desimal presisi |
| VARCHAR / TEXT | Teks |
| BOOLEAN | TRUE/FALSE |
| DATE | Tanggal |
| TIMESTAMP / TIMESTAMPTZ | Tanggal dan waktu |
| UUID | Identitas universal |
| JSONB | Dokumen JSON terstruktur |
| TEXT[] / INTEGER[] | Array |
| INET | Alamat jaringan |
Bab 5 — Tabel dan Constraint
Membuat struktur yang aman
CREATE TABLE sekolah.siswa (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nis VARCHAR(20) NOT NULL UNIQUE,
nama VARCHAR(100) NOT NULL,
nilai NUMERIC(5,2) CHECK (nilai BETWEEN 0 AND 100),
aktif BOOLEAN DEFAULT TRUE,
profil JSONB DEFAULT '{}'::jsonb,
minat TEXT[] DEFAULT ARRAY[]::TEXT[],
created_at TIMESTAMPTZ DEFAULT now()
);
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nis VARCHAR(20) NOT NULL UNIQUE,
nama VARCHAR(100) NOT NULL,
nilai NUMERIC(5,2) CHECK (nilai BETWEEN 0 AND 100),
aktif BOOLEAN DEFAULT TRUE,
profil JSONB DEFAULT '{}'::jsonb,
minat TEXT[] DEFAULT ARRAY[]::TEXT[],
created_at TIMESTAMPTZ DEFAULT now()
);
Bab 6 — CRUD
INSERT, SELECT, UPDATE, DELETE, dan RETURNING
INSERT INTO sekolah.siswa(nis,nama,nilai)
VALUES ('2026001','Alya',88)
RETURNING id,nis,nama,nilai;
VALUES ('2026001','Alya',88)
RETURNING id,nis,nama,nilai;
SELECT id,nama,nilai
FROM sekolah.siswa
WHERE aktif IS TRUE
ORDER BY nilai DESC;
FROM sekolah.siswa
WHERE aktif IS TRUE
ORDER BY nilai DESC;
UPDATE sekolah.siswa
SET nilai=90, profil=profil || '{"status":"unggul"}'::jsonb
WHERE id=1
RETURNING *;
SET nilai=90, profil=profil || '{"status":"unggul"}'::jsonb
WHERE id=1
RETURNING *;
DELETE FROM sekolah.siswa
WHERE id=1
RETURNING id,nama;
WHERE id=1
RETURNING id,nama;
Bab 7 — Query Dasar
Filter, pencarian, sorting, dan pagination
ILIKE
SELECT * FROM siswa
WHERE nama ILIKE '%alya%';
WHERE nama ILIKE '%alya%';
BETWEEN dan IN
SELECT * FROM siswa
WHERE nilai BETWEEN 75 AND 90
AND kelas_id IN (1,2,3);
WHERE nilai BETWEEN 75 AND 90
AND kelas_id IN (1,2,3);
LIMIT OFFSET
SELECT * FROM siswa
ORDER BY id
LIMIT 20 OFFSET 40;
ORDER BY id
LIMIT 20 OFFSET 40;
Bab 8 — Agregasi
GROUP BY, HAVING, FILTER, dan statistik
Agregasi kelas
SELECT kelas_id,
COUNT(*) jumlah,
AVG(nilai) rata,
MAX(nilai) tertinggi
FROM siswa
GROUP BY kelas_id;
COUNT(*) jumlah,
AVG(nilai) rata,
MAX(nilai) tertinggi
FROM siswa
GROUP BY kelas_id;
FILTER
SELECT
COUNT(*) FILTER (WHERE nilai >= 75) AS lulus,
COUNT(*) FILTER (WHERE nilai < 75) AS remedial
FROM siswa;
COUNT(*) FILTER (WHERE nilai >= 75) AS lulus,
COUNT(*) FILTER (WHERE nilai < 75) AS remedial
FROM siswa;
Bab 9 — JOIN
Menggabungkan tabel
INNER JOIN
SELECT s.nama,k.nama_kelas
FROM siswa s
JOIN kelas k ON k.id=s.kelas_id;
FROM siswa s
JOIN kelas k ON k.id=s.kelas_id;
LEFT JOIN
SELECT k.nama_kelas,s.nama
FROM kelas k
LEFT JOIN siswa s ON s.kelas_id=k.id;
FROM kelas k
LEFT JOIN siswa s ON s.kelas_id=k.id;
LATERAL
SELECT s.nama,n.nilai
FROM siswa s
LEFT JOIN LATERAL (
SELECT nilai FROM nilai
WHERE siswa_id=s.id
ORDER BY created_at DESC LIMIT 1
) n ON TRUE;
FROM siswa s
LEFT JOIN LATERAL (
SELECT nilai FROM nilai
WHERE siswa_id=s.id
ORDER BY created_at DESC LIMIT 1
) n ON TRUE;
Bab 10 — JSONB
Menyimpan dan mengolah data fleksibel
Insert JSONB
UPDATE siswa
SET profil='{"kota":"Pontianak","hobi":["membaca","coding"]}'::jsonb
WHERE id=1;
SET profil='{"kota":"Pontianak","hobi":["membaca","coding"]}'::jsonb
WHERE id=1;
Mengambil nilai
SELECT profil->>'kota' AS kota
FROM siswa;
FROM siswa;
Filter JSONB
SELECT * FROM siswa
WHERE profil @> '{"kota":"Pontianak"}'::jsonb;
WHERE profil @> '{"kota":"Pontianak"}'::jsonb;
Bab 11 — Array
Menyimpan daftar nilai dalam satu kolom
Array
UPDATE siswa
SET minat=ARRAY['Sosiologi','Teknologi']
WHERE id=1;
SET minat=ARRAY['Sosiologi','Teknologi']
WHERE id=1;
ANY dan unnest
SELECT * FROM siswa
WHERE 'Teknologi'=ANY(minat);
SELECT nama,unnest(minat) AS minat
FROM siswa;
WHERE 'Teknologi'=ANY(minat);
SELECT nama,unnest(minat) AS minat
FROM siswa;
Bab 12 — CTE
WITH dan recursive query
CTE biasa
WITH lulus AS (
SELECT * FROM siswa WHERE nilai >= 75
)
SELECT * FROM lulus ORDER BY nilai DESC;
SELECT * FROM siswa WHERE nilai >= 75
)
SELECT * FROM lulus ORDER BY nilai DESC;
Recursive CTE
WITH RECURSIVE angka(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM angka WHERE n<10
)
SELECT * FROM angka;
SELECT 1
UNION ALL
SELECT n+1 FROM angka WHERE n<10
)
SELECT * FROM angka;
Bab 13 — Window Function
Ranking, running total, dan perbandingan baris
Ranking
SELECT nama,nilai,
RANK() OVER(ORDER BY nilai DESC) peringkat
FROM siswa;
RANK() OVER(ORDER BY nilai DESC) peringkat
FROM siswa;
Partition
SELECT nama,kelas_id,nilai,
AVG(nilai) OVER(PARTITION BY kelas_id) rata_kelas
FROM siswa;
AVG(nilai) OVER(PARTITION BY kelas_id) rata_kelas
FROM siswa;
LAG
SELECT tanggal,jumlah,
LAG(jumlah) OVER(ORDER BY tanggal) sebelumnya
FROM penjualan;
LAG(jumlah) OVER(ORDER BY tanggal) sebelumnya
FROM penjualan;
Bab 14 — UPSERT
INSERT ... ON CONFLICT
INSERT INTO siswa(nis,nama,nilai)
VALUES ('2026001','Alya',90)
ON CONFLICT (nis)
DO UPDATE SET
nama=EXCLUDED.nama,
nilai=EXCLUDED.nilai
RETURNING *;
VALUES ('2026001','Alya',90)
ON CONFLICT (nis)
DO UPDATE SET
nama=EXCLUDED.nama,
nilai=EXCLUDED.nilai
RETURNING *;
Bab 15 — View
View dan materialized view
View
CREATE VIEW v_siswa_lulus AS
SELECT id,nama,nilai
FROM siswa
WHERE nilai >= 75;
SELECT id,nama,nilai
FROM siswa
WHERE nilai >= 75;
Materialized View
CREATE MATERIALIZED VIEW mv_rata_kelas AS
SELECT kelas_id,AVG(nilai) rata
FROM siswa GROUP BY kelas_id;
REFRESH MATERIALIZED VIEW mv_rata_kelas;
SELECT kelas_id,AVG(nilai) rata
FROM siswa GROUP BY kelas_id;
REFRESH MATERIALIZED VIEW mv_rata_kelas;
Bab 16 — Function dan Trigger
Otomasi logika di database
Function
CREATE FUNCTION status_nilai(n NUMERIC)
RETURNS TEXT AS $$
BEGIN
RETURN CASE WHEN n>=75 THEN 'Lulus' ELSE 'Remedial' END;
END;
$$ LANGUAGE plpgsql;
RETURNS TEXT AS $$
BEGIN
RETURN CASE WHEN n>=75 THEN 'Lulus' ELSE 'Remedial' END;
END;
$$ LANGUAGE plpgsql;
Trigger
CREATE TRIGGER trg_update_time
BEFORE UPDATE ON siswa
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
BEFORE UPDATE ON siswa
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
Bab 17 — Index
B-tree, GIN, GiST, dan BRIN
B-tree
Cocok untuk pencarian, sorting, dan perbandingan umum.
GIN
Cocok untuk JSONB, array, dan full-text search.
GiST
Cocok untuk tipe data khusus seperti geometri dan range.
BRIN
Cocok untuk tabel sangat besar dengan data yang terurut secara alami.
CREATE INDEX idx_siswa_nama ON siswa(nama);
CREATE INDEX idx_siswa_profil ON siswa USING GIN(profil);
EXPLAIN ANALYZE SELECT * FROM siswa WHERE profil @> '{"kota":"Pontianak"}';
CREATE INDEX idx_siswa_profil ON siswa USING GIN(profil);
EXPLAIN ANALYZE SELECT * FROM siswa WHERE profil @> '{"kota":"Pontianak"}';
Bab 18 — Transaksi
BEGIN, COMMIT, ROLLBACK, dan SAVEPOINT
BEGIN;
UPDATE rekening SET saldo=saldo-100000 WHERE id=1;
SAVEPOINT setelah_debit;
UPDATE rekening SET saldo=saldo+100000 WHERE id=2;
COMMIT;
-- jika gagal:
ROLLBACK TO SAVEPOINT setelah_debit;
UPDATE rekening SET saldo=saldo-100000 WHERE id=1;
SAVEPOINT setelah_debit;
UPDATE rekening SET saldo=saldo+100000 WHERE id=2;
COMMIT;
-- jika gagal:
ROLLBACK TO SAVEPOINT setelah_debit;
Bab 19 — Role dan Privilege
Mengelola akun dan hak akses
Role
CREATE ROLE guru LOGIN PASSWORD 'PasswordKuat!';
CREATE ROLE pembaca;
CREATE ROLE pembaca;
Privilege
GRANT USAGE ON SCHEMA sekolah TO pembaca;
GRANT SELECT ON ALL TABLES IN SCHEMA sekolah TO pembaca;
GRANT pembaca TO guru;
GRANT SELECT ON ALL TABLES IN SCHEMA sekolah TO pembaca;
GRANT pembaca TO guru;
Bab 20 — Backup dan Restore
Melindungi database
Custom Format
pg_dump -Fc -d akademik -f akademik.backup
pg_restore -d akademik_baru akademik.backup
pg_restore -d akademik_baru akademik.backup
Plain SQL
pg_dump -Fp akademik > akademik.sql
psql -d akademik_baru -f akademik.sql
psql -d akademik_baru -f akademik.sql
Bab 21 — Maintenance
VACUUM, ANALYZE, REINDEX, dan monitoring
VACUUM
VACUUM (ANALYZE) sekolah.siswa;
ANALYZE
ANALYZE sekolah.siswa;
Aktivitas
SELECT pid,usename,state,query
FROM pg_stat_activity;
FROM pg_stat_activity;
Bab 22 — Proyek
Latihan dari dasar sampai HOTS
Dasar
Database Akademik
Buat schema sekolah, tabel siswa, kelas, mapel, dan nilai.
Menengah
JSONB Profil
Simpan profil tambahan siswa dengan JSONB dan buat query filternya.
Menengah
Peringkat
Gunakan window function untuk peringkat siswa per kelas.
Lanjutan
Audit Trigger
Buat trigger untuk mencatat perubahan data.
Lanjutan
Role Akses
Buat role admin, guru, dan pembaca dengan privilege berbeda.
HOTS
Optimasi Query
Bandingkan EXPLAIN ANALYZE sebelum dan sesudah index.
Bab 23 — Simulator
Generator query PostgreSQL
Bab 24 — Evaluasi
Kuis PostgreSQL 30 soal
Nilai minimal 75%.