PostgreSQL untuk Developer: Panduan Lengkap

# ====== Ubuntu/Debian ======
sudo apt update
sudo apt install postgresql postgresql-contrib

# Cek status service
sudo systemctl status postgresql

# Masuk ke PostgreSQL shell
sudo -u postgres psql

# ====== macOS (Homebrew) ======
brew install postgresql@16
brew services start postgresql@16
psql postgres

# ====== Windows ======
# Download installer dari https://www.postgresql.org/download/windows/
# Jalankan installer, set password untuk user postgres
# PgAdmin akan terinstal otomatis

# ====== Docker (Cara Tercepat) ======
docker run --name postgres-dev \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=belajar_db \
  -p 5432:5432 \
  -d postgres:16-alpine

# Masuk ke container
docker exec -it postgres-dev psql -U postgres -d belajar_db

-- Buat database baru
CREATE DATABASE belajar_db;

-- Buat user baru dengan password
CREATE USER developer WITH PASSWORD 'rahasia123';

-- Berikan hak akses
GRANT ALL PRIVILEGES ON DATABASE belajar_db TO developer;

-- Koneksi ke database
\c belajar_db

-- Berikan hak akses ke schema public
GRANT ALL ON SCHEMA public TO developer;

-- Cek daftar database
\l

-- Cek daftar tabel
\dt

-- Cek versi PostgreSQL
SELECT version();

-- Keluar dari psql
\q

-- Tabel mahasiswa dengan berbagai tipe data
CREATE TABLE mahasiswa (
    id          SERIAL PRIMARY KEY,
    nim         VARCHAR(20) UNIQUE NOT NULL,
    nama        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE NOT NULL,
    tanggal_lahir DATE,
    ipk         NUMERIC(3,2) CHECK (ipk >= 0 AND ipk <= 4),
    aktif       BOOLEAN DEFAULT TRUE,
    mata_kuliah TEXT[],
    alamat      JSONB,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Menambahkan komentar pada tabel dan kolom
COMMENT ON TABLE mahasiswa IS 'Data mahasiswa universitas';
COMMENT ON COLUMN mahasiswa.nim IS 'Nomor Induk Mahasiswa';
COMMENT ON COLUMN mahasiswa.ipk IS 'Indeks Prestasi Kumulatif (0.00 - 4.00)';

-- Contoh INSERT dengan berbagai tipe data
INSERT INTO mahasiswa (nim, nama, email, tanggal_lahir, ipk, mata_kuliah, alamat)
VALUES (
    '2024001',
    'Budi Santoso',
    'budi@email.com',
    '2002-05-15',
    3.75,
    ARRAY['Basis Data', 'Algoritma', 'Jaringan'],
    '{"jalan": "Jl. Merdeka No.10", "kota": "Jakarta", "kode_pos": "12345"}'::jsonb
);

-- Query data JSON
SELECT nama,
       alamat->>'kota' AS kota,
       alamat->>'kode_pos' AS kode_pos
FROM mahasiswa;

-- Query array
SELECT nama, unnest(mata_kuliah) AS mata_kuliah
FROM mahasiswa;

-- Insert satu baris
INSERT INTO mahasiswa (nim, nama, email, ipk)
VALUES ('2024002', 'Sari Dewi', 'sari@email.com', 3.85);

-- Insert multiple baris sekaligus
INSERT INTO mahasiswa (nim, nama, email, ipk) VALUES
    ('2024003', 'Andi Pratama', 'andi@email.com', 3.50),
    ('2024004', 'Maya Putri', 'maya@email.com', 3.92),
    ('2024005', 'Rizki Ahmad', 'rizki@email.com', 3.28);

-- Insert dari hasil SELECT
CREATE TABLE mahasiswa_berprestasi AS
SELECT nim, nama, ipk
FROM mahasiswa
WHERE ipk >= 3.50;

-- INSERT dengan RETURNING (mengembalikan data yang di-insert)
INSERT INTO mahasiswa (nim, nama, email, ipk)
VALUES ('2024006', 'Dina Rahma', 'dina@email.com', 3.67)
RETURNING id, nama;

-- Select semua kolom
SELECT * FROM mahasiswa;

-- Select kolom tertentu
SELECT nama, email, ipk FROM mahasiswa;

-- WHERE clause — filter data
SELECT nama, ipk FROM mahasiswa
WHERE ipk >= 3.5 AND aktif = TRUE;

-- LIKE — pencarian pattern
SELECT nama FROM mahasiswa
WHERE nama LIKE 'Sari%';        -- Dimulai dengan "Sari"
WHERE nama ILIKE '%putri%';     -- Case-insensitive mengandung "putri"

-- IN — cek multiple values
SELECT nama, ipk FROM mahasiswa
WHERE nama IN ('Budi Santoso', 'Sari Dewi', 'Andi Pratama');

-- BETWEEN — range nilai
SELECT nama, ipk FROM mahasiswa
WHERE ipk BETWEEN 3.50 AND 4.00;

-- ORDER BY — urutkan
SELECT nama, ipk FROM mahasiswa
ORDER BY ipk DESC, nama ASC;

-- LIMIT & OFFSET — paginasi
SELECT nama, ipk FROM mahasiswa
ORDER BY ipk DESC
LIMIT 10 OFFSET 0;  -- Halaman 1

-- GROUP BY & Aggregate Functions
SELECT
    COUNT(*) AS total,
    ROUND(AVG(ipk), 2) AS rata_rata_ipk,
    MAX(ipk) AS ipk_tertinggi,
    MIN(ipk) AS ipk_terendah
FROM mahasiswa
WHERE aktif = TRUE;

-- HAVING — filter setelah GROUP BY
SELECT
    CASE
        WHEN ipk >= 3.5 THEN 'Berprestasi'
        WHEN ipk >= 3.0 THEN 'Baik'
        ELSE 'Cukup'
    END AS kategori,
    COUNT(*) AS jumlah
FROM mahasiswa
GROUP BY kategori
HAVING COUNT(*) >= 2
ORDER BY kategori;

-- Subquery
SELECT nama, ipk FROM mahasiswa
WHERE ipk > (SELECT AVG(ipk) FROM mahasiswa);

-- CTE (Common Table Expression)
WITH ipk_per_kategori AS (
    SELECT
        CASE
            WHEN ipk >= 3.5 THEN 'Berprestasi'
            WHEN ipk >= 3.0 THEN 'Baik'
            ELSE 'Cukup'
        END AS kategori,
        nama,
        ipk
    FROM mahasiswa
)
SELECT kategori, COUNT(*) as jumlah
FROM ipk_per_kategori
GROUP BY kategori;

-- Update satu kolom
UPDATE mahasiswa
SET ipk = 3.95
WHERE nim = '2024001';

-- Update multiple kolom
UPDATE mahasiswa
SET ipk = 3.95, updated_at = CURRENT_TIMESTAMP
WHERE nim = '2024001'
RETURNING *;  -- Tampilkan data yang di-update

-- Update dengan kondisi kompleks
UPDATE mahasiswa
SET aktif = FALSE
WHERE ipk < 2.00 AND aktif = TRUE;

-- DELETE
DELETE FROM mahasiswa
WHERE nim = '2024006'
RETURNING *;  -- Tampilkan data yang dihapus

-- DELETE dengan kondisi
DELETE FROM mahasiswa
WHERE aktif = FALSE AND created_at < '2025-01-01';

-- ⚠️ HATI-HATI: DELETE tanpa WHERE = hapus semua data!
-- Selalu gunakan WHERE clause!

-- TRUNCATE — hapus semua data (lebih cepat dari DELETE)
TRUNCATE TABLE mahasiswa_berprestasi;

-- Tabel dosen
CREATE TABLE dosen (
    id     SERIAL PRIMARY KEY,
    nama   VARCHAR(100) NOT NULL,
    email  VARCHAR(150) UNIQUE
);

-- Tabel mata_kuliah
CREATE TABLE mata_kuliah (
    id         SERIAL PRIMARY KEY,
    kode       VARCHAR(10) UNIQUE NOT NULL,
    nama_mk    VARCHAR(100) NOT NULL,
    sks        INTEGER NOT NULL,
    dosen_id   INTEGER REFERENCES dosen(id)
);

-- Tabel nilai (junction table)
CREATE TABLE nilai (
    id           SERIAL PRIMARY KEY,
    mahasiswa_id INTEGER REFERENCES mahasiswa(id),
    mata_kuliah_id INTEGER REFERENCES mata_kuliah(id),
    nilai        NUMERIC(5,2),
    grade        VARCHAR(2)
);

-- Insert sample data
INSERT INTO dosen (nama, email) VALUES
    ('Dr. Ahmad Fauzi', 'ahmad@kampus.ac.id'),
    ('Dr. Siti Nurhaliza', 'siti@kampus.ac.id');

INSERT INTO mata_kuliah (kode, nama_mk, sks, dosen_id) VALUES
    ('IF101', 'Basis Data', 3, 1),
    ('IF102', 'Algoritma', 3, 1),
    ('IF103', 'Jaringan Komputer', 2, 2);

INSERT INTO nilai (mahasiswa_id, mata_kuliah_id, nilai, grade) VALUES
    (1, 1, 88.50, 'A'),
    (1, 2, 78.00, 'B+'),
    (2, 1, 92.00, 'A'),
    (2, 3, 85.00, 'A-'),
    (3, 1, 70.00, 'B'),
    (3, 2, 65.00, 'B-');

-- INNER JOIN — hanya baris yang cocok di kedua tabel
SELECT m.nama AS mahasiswa, mk.nama_mk, n.nilai, n.grade
FROM nilai n
INNER JOIN mahasiswa m ON n.mahasiswa_id = m.id
INNER JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
ORDER BY m.nama, mk.nama_mk;

-- LEFT JOIN — semua baris kiri + yang cocok dari kanan
SELECT m.nama AS mahasiswa, COUNT(n.id) AS jumlah_matkul
FROM mahasiswa m
LEFT JOIN nilai n ON m.id = n.mahasiswa_id
GROUP BY m.nama
ORDER BY jumlah_matkul DESC;

-- RIGHT JOIN — semua baris kanan + yang cocok dari kiri
SELECT mk.nama_mk, COUNT(n.id) AS jumlah_nilai
FROM nilai n
RIGHT JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
GROUP BY mk.nama_mk;

-- FULL OUTER JOIN — semua baris dari kedua tabel
SELECT m.nama AS mahasiswa, mk.nama_mk, n.nilai
FROM mahasiswa m
FULL OUTER JOIN nilai n ON m.id = n.mahasiswa_id
FULL OUTER JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id;

-- SELF JOIN — join tabel dengan dirinya sendiri
-- Contoh: cari mahasiswa dengan IPK sama
SELECT a.nama AS mahasiswa_1, b.nama AS mahasiswa_2, a.ipk
FROM mahasiswa a
INNER JOIN mahasiswa b ON a.ipk = b.ipk AND a.id < b.id;

-- JOIN dengan aggregasi
SELECT
    m.nama,
    ROUND(AVG(n.nilai), 2) AS rata_nilai,
    COUNT(n.id) AS jumlah_mk,
    SUM(mk.sks) AS total_sks
FROM mahasiswa m
JOIN nilai n ON m.id = n.mahasiswa_id
JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
GROUP BY m.nama
HAVING AVG(n.nilai) >= 80
ORDER BY rata_nilai DESC;

-- B-tree index (default) — untuk pencarian umum
CREATE INDEX idx_mahasiswa_nama ON mahasiswa(nama);

-- Composite index — index pada multiple kolom
CREATE INDEX idx_nilai_mhs_mk ON nilai(mahasiswa_id, mata_kuliah_id);

-- Unique index — memastikan nilai unik
CREATE UNIQUE INDEX idx_mahasiswa_nim ON mahasiswa(nim);

-- Partial index — hanya index baris yang memenuhi kondisi
CREATE INDEX idx_mahasiswa_aktif ON mahasiswa(nama)
WHERE aktif = TRUE;

-- GIN index untuk JSONB
CREATE INDEX idx_mahasiswa_alamat ON mahasiswa USING GIN(alamat);

-- GIN index untuk full-text search
CREATE INDEX idx_mahasiswa_nama_search ON mahasiswa
USING GIN(to_tsvector('indonesian', nama));

-- Cek index yang ada pada tabel
\d mahasiswa

-- Analisis query dengan EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM mahasiswa WHERE nama = 'Budi Santoso';
-- Seq Scan on mahasiswa (before index): 0.045 ms
-- Index Scan using idx_mahasiswa_nama (after index): 0.012 ms

-- Hapus index
DROP INDEX idx_mahasiswa_nama;

-- Rebuild index (setelah banyak INSERT/UPDATE/DELETE)
REINDEX INDEX idx_mahasiswa_nama;

-- View: Ringkasan nilai mahasiswa
CREATE VIEW v_ringkasan_nilai AS
SELECT
    m.nim,
    m.nama AS nama_mahasiswa,
    mk.nama_mk,
    mk.sks,
    n.nilai,
    n.grade,
    d.nama AS nama_dosen
FROM nilai n
JOIN mahasiswa m ON n.mahasiswa_id = m.id
JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
JOIN dosen d ON mk.dosen_id = d.id
ORDER BY m.nama, mk.nama_mk;

-- Menggunakan view seperti tabel biasa
SELECT * FROM v_ringkasan_nilai;
SELECT nama_mahasiswa, AVG(nilai) FROM v_ringkasan_nilai GROUP BY nama_mahasiswa;

-- View: IPK Mahasiswa
CREATE VIEW v_ipk_mahasiswa AS
SELECT
    m.nim,
    m.nama,
    ROUND(AVG(n.nilai), 2) AS rata_nilai,
    COUNT(n.id) AS jumlah_mk,
    SUM(mk.sks) AS total_sks,
    CASE
        WHEN AVG(n.nilai) >= 85 THEN 'Cum Laude'
        WHEN AVG(n.nilai) >= 75 THEN 'Baik'
        ELSE 'Cukup'
    END AS predikat
FROM mahasiswa m
JOIN nilai n ON m.id = n.mahasiswa_id
JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
GROUP BY m.nim, m.nama
ORDER BY rata_nilai DESC;

-- Materialized View — menyimpan hasil query (perlu refresh)
CREATE MATERIALIZED VIEW mv_statistik_nilai AS
SELECT
    mk.nama_mk,
    COUNT(n.id) AS jumlah_nilai,
    ROUND(AVG(n.nilai), 2) AS rata_nilai,
    MAX(n.nilai) AS nilai_max,
    MIN(n.nilai) AS nilai_min
FROM nilai n
JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
GROUP BY mk.nama_mk;

-- Refresh materialized view (harus dilakukan manual)
REFRESH MATERIALIZED VIEW mv_statistik_nilai;

-- Update view
CREATE OR REPLACE VIEW v_ringkasan_nilai AS
SELECT
    m.nim,
    m.nama AS nama_mahasiswa,
    m.ipk,
    mk.nama_mk,
    mk.sks,
    n.nilai,
    n.grade
FROM nilai n
JOIN mahasiswa m ON n.mahasiswa_id = m.id
JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id;

-- Hapus view
DROP VIEW IF EXISTS v_ringkasan_nilai;

-- Function sederhana: hitung IPK mahasiswa
CREATE OR REPLACE FUNCTION hitung_ipk(p_mahasiswa_id INTEGER)
RETURNS NUMERIC(3,2) AS $$
DECLARE
    v_ipk NUMERIC(3,2);
BEGIN
    SELECT ROUND(AVG(nilai) / 25.0, 2) INTO v_ipk
    FROM nilai
    WHERE mahasiswa_id = p_mahasiswa_id;

    RETURN COALESCE(v_ipk, 0.00);
END;
$$ LANGUAGE plpgsql;

-- Menggunakan function
SELECT nama, hitung_ipk(id) AS ipk FROM mahasiswa;

-- Function: konversi nilai ke grade
CREATE OR REPLACE FUNCTION konversi_grade(p_nilai NUMERIC)
RETURNS VARCHAR(2) AS $$
BEGIN
    RETURN CASE
        WHEN p_nilai >= 90 THEN 'A'
        WHEN p_nilai >= 85 THEN 'A-'
        WHEN p_nilai >= 80 THEN 'B+'
        WHEN p_nilai >= 75 THEN 'B'
        WHEN p_nilai >= 70 THEN 'B-'
        WHEN p_nilai >= 65 THEN 'C+'
        WHEN p_nilai >= 60 THEN 'C'
        WHEN p_nilai >= 50 THEN 'D'
        ELSE 'E'
    END;
END;
$$ LANGUAGE plpgsql;

-- Function: cek apakah mahasiswa bisa lulus
CREATE OR REPLACE FUNCTION cek_kelulusan(p_mahasiswa_id INTEGER)
RETURNS TABLE(nama TEXT, ipk NUMERIC, total_sks INTEGER, lulus BOOLEAN) AS $$
BEGIN
    RETURN QUERY
    SELECT
        m.nama::TEXT,
        hitung_ipk(m.id),
        COALESCE(SUM(mk.sks), 0)::INTEGER,
        (hitung_ipk(m.id) >= 2.00 AND COALESCE(SUM(mk.sks), 0) >= 144)
    FROM mahasiswa m
    LEFT JOIN nilai n ON m.id = n.mahasiswa_id
    LEFT JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
    WHERE m.id = p_mahasiswa_id
    GROUP BY m.id, m.nama;
END;
$$ LANGUAGE plpgsql;

-- Menggunakan function
SELECT * FROM cek_kelulusan(1);

-- Procedure: proses pemberian nilai
CREATE OR REPLACE PROCEDURE berikan_nilai(
    p_mahasiswa_id INTEGER,
    p_mata_kuliah_id INTEGER,
    p_nilai NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_grade VARCHAR(2);
BEGIN
    -- Konversi nilai ke grade
    v_grade := konversi_grade(p_nilai);

    -- Insert atau update nilai
    INSERT INTO nilai (mahasiswa_id, mata_kuliah_id, nilai, grade)
    VALUES (p_mahasiswa_id, p_mata_kuliah_id, p_nilai, v_grade)
    ON CONFLICT (mahasiswa_id, mata_kuliah_id)
    DO UPDATE SET
        nilai = EXCLUDED.nilai,
        grade = EXCLUDED.grade;

    -- Update IPK mahasiswa
    UPDATE mahasiswa
    SET ipk = hitung_ipk(p_mahasiswa_id),
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_mahasiswa_id;

    RAISE NOTICE 'Nilai % (grade: %) berhasil diberikan', p_nilai, v_grade;
END;
$$;

-- Menjalankan procedure
CALL berikan_nilai(1, 3, 92.50);

-- Trigger: otomatis update timestamp
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Pasang trigger pada tabel mahasiswa
CREATE TRIGGER trigger_update_timestamp
    BEFORE UPDATE ON mahasiswa
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();