Panduan Praktis Mengelola Struktur dan Data dengan Sintaks SQL PostgreSQL

SQL Adalah Bahasa Basis Data

Setelah berhasil menginstal dan menyiapkan lingkungan PostgreSQL di Seri 1, kini saatnya Anda mempelajari bahasanya: Structured Query Language (SQL). SQL adalah bahasa standar untuk berkomunikasi dengan hampir semua sistem basis data relasional.

Dalam konteks PostgreSQL, SQL dibagi menjadi beberapa sub-bahasa utama yang sangat krusial:

1. Data Definition Language (DDL): Digunakan untuk mendefinisikan dan memodifikasi struktur objek database (misalnya, membuat tabel, mengubah skema, membuat indeks).

2. Data Manipulation Language (DML): Digunakan untuk mengambil dan memanipulasi data di dalam objek database (misalnya, memasukkan, mengambil, memperbarui, dan menghapus data).

PostgreSQL dikenal karena kepatuhannya yang ketat terhadap standar SQL, namun ia juga memperkaya bahasa tersebut dengan banyak fitur dan tipe data yang unik dan kuat. Artikel ini akan memandu Anda secara mendalam melalui DDL dan DML, serta mengeksplorasi tipe data canggih yang membedakan PostgreSQL dari RDBMS lainnya. Anda akan belajar bagaimana merancang tabel yang kokoh dan menjalankan query yang efisien.

Mengapa Desain Skema Itu Penting?

Sebelum memasukkan data (DML), kita harus mendefinisikan strukturnya (DDL). Desain skema yang baik, yang mencakup tabel, kolom, dan batasan (Constraints), adalah penentu utama kinerja dan integritas data di masa depan. Sebuah skema yang buruk akan mengakibatkan duplikasi data, anomali saat pembaruan, dan query yang lambat. Oleh karena itu, langkah DDL harus dilakukan dengan pertimbangan yang matang, biasanya mengikuti prinsip Normalisasi Basis Data.

🧱 DDL: Membangun Struktur Tabel yang Kokoh

DDL adalah fondasi dari setiap proyek database. Perintah utamanya adalah CREATE, ALTER, dan DROP.

1. Perintah CREATE TABLE dan Tipe Data Dasar

Perintah CREATE TABLE adalah pintu masuk utama DDL. Saat mendefinisikan tabel, Anda harus memilih tipe data yang paling sesuai untuk setiap kolom, yang akan menentukan bagaimana data disimpan dan diolah.

-- Membuat Tabel Karyawan
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary NUMERIC(10, 2) CHECK (salary > 0),
    is_active BOOLEAN DEFAULT TRUE
);

Tipe Data Dasar PostgreSQL

2. Memastikan Integritas Data dengan Constraints

Constraints (Batasan) adalah aturan yang diterapkan pada kolom atau tabel untuk membatasi jenis data yang dapat masuk ke dalamnya, sehingga menjamin integritas data.

• PRIMARY KEY: Mengidentifikasi setiap baris secara unik; otomatis menyiratkan NOT NULL dan UNIQUE.

• FOREIGN KEY: Memastikan hubungan antar tabel terjaga; mengacu pada PRIMARY KEY di tabel lain.

• NOT NULL: Memastikan kolom tidak boleh kosong.

• UNIQUE: Memastikan semua nilai dalam kolom berbeda (kecuali NULL).

• CHECK: Memastikan nilai kolom memenuhi kondisi tertentu (seperti salary > 0 pada contoh di atas).

Implementasi FOREIGN KEY untuk Relasi

Relasi adalah jantung dari RDBMS. Di PostgreSQL, kita menggunakan FOREIGN KEY untuk membangunnya.

-- Membuat Tabel Departemen
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) UNIQUE NOT NULL
);

-- Mengubah Tabel Karyawan untuk Menambahkan Foreign Key
ALTER TABLE employees
ADD COLUMN dept_id INTEGER,
ADD CONSTRAINT fk_dept
    FOREIGN KEY (dept_id)
    REFERENCES departments (dept_id)
    ON DELETE RESTRICT;

ON DELETE RESTRICT adalah praktik yang baik; mencegah baris di tabel departments dihapus jika masih ada karyawan yang terkait dengannya. Opsi lain termasuk CASCADE (hapus baris anak) atau SET NULL.

3. Memodifikasi dan Menghapus Struktur (ALTER dan DROP)

• ALTER TABLE: Digunakan untuk memodifikasi struktur tabel yang sudah ada.

  SQL

   

  -- Menambahkan kolom baru
  ALTER TABLE employees
  ADD COLUMN phone_number VARCHAR(15);
  
  -- Mengubah tipe data kolom
  ALTER TABLE employees
  ALTER COLUMN phone_number TYPE VARCHAR(20);
  
  -- Menghapus kolom
  ALTER TABLE employees
  DROP COLUMN is_active;
  

• DROP TABLE: Menghapus tabel secara permanen.

  SQL

   

  -- Hati-hati, data akan hilang!
  DROP TABLE employees;
  
  -- Opsi CASCADE: Hapus tabel dan semua objek yang bergantung padanya (misalnya, Foreign Key yang mengacu padanya)
  -- DROP TABLE departments CASCADE;
  

💾 DML: Memanipulasi dan Mengambil Data

DML adalah bagian paling interaktif dari SQL, yang digunakan setiap hari untuk mengelola data operasional. Perintah utamanya adalah INSERT, SELECT, UPDATE, dan DELETE.

1. Memasukkan Data (INSERT)

Data dimasukkan ke dalam tabel yang telah Anda definisikan.

-- Memasukkan data ke tabel departemen
INSERT INTO departments (dept_name) VALUES
('Pemasaran'),
('Teknologi Informasi'),
('Keuangan');

-- Memasukkan data ke tabel karyawan
INSERT INTO employees (first_name, last_name, email, salary, dept_id) VALUES
('Budi', 'Setiawan', 'budi.s@mail.com', 6500000.00, 2), -- dept_id 2 = TI
('Ani', 'Wijaya', 'ani.w@mail.com', 7200000.00, 3),    -- dept_id 3 = Keuangan
('Citra', 'Dewi', 'citra.d@mail.com', 5800000.00, 1);  -- dept_id 1 = Pemasaran

SERIAL / Primary Key: Kita tidak perlu menentukan nilai untuk kolom employee_id karena tipe datanya SERIAL (PostgreSQL otomatis mengelolanya).

2. Mengambil Data (SELECT)

Perintah SELECT adalah yang paling sering digunakan, memungkinkan pengambilan data dari satu atau lebih tabel.

-- Mengambil semua kolom dari tabel karyawan
SELECT * FROM employees;

-- Mengambil kolom tertentu dan memberi alias
SELECT
    e.first_name || ' ' || e.last_name AS full_name,
    d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 6000000.00
ORDER BY e.salary DESC;

Penting: Perhatikan penggunaan klausa WHERE untuk memfilter baris, ORDER BY untuk mengurutkan, dan JOIN untuk menggabungkan data dari tabel berbeda. Ini adalah inti dari query relasional.

3. Memperbarui Data (UPDATE)

Digunakan untuk mengubah data yang sudah ada di satu atau lebih baris. Selalu gunakan klausa WHERE untuk menghindari pembaruan semua baris!

-- Menaikkan gaji Budi sebesar 10%
UPDATE employees
SET salary = salary * 1.10
WHERE first_name = 'Budi' AND last_name = 'Setiawan';

4. Menghapus Data (DELETE)

Digunakan untuk menghapus baris dari tabel. Selalu gunakan klausa WHERE untuk menghindari penghapusan semua data!

-- Menghapus karyawan yang memiliki gaji di bawah 5000000.00
DELETE FROM employees
WHERE salary < 5000000.00;

-- Perintah TRUNCATE: Menghapus SEMUA baris dengan sangat cepat (tetapi DML ini dianggap DDL dalam beberapa konteks)
-- TRUNCATE TABLE employees RESTART IDENTITY;

TRUNCATE jauh lebih cepat daripada DELETE tanpa WHERE karena ia tidak mencatat penghapusan baris satu per satu (tidak bisa di-rollback).

✨ Tipe Data Lanjutan Khas PostgreSQL

Salah satu keunggulan kompetitif terbesar PostgreSQL adalah dukungannya yang luar biasa terhadap tipe data non-tradisional yang terstruktur dan semi-terstruktur.

1. JSONB: Penyimpanan Data Semi-Terstruktur

PostgreSQL menawarkan dua tipe data JSON: JSON (menyimpan string JSON mentah) dan JSONB (menyimpan JSON dalam format biner yang sudah didekonstruksi dan diindeks). JSONB adalah pilihan yang jauh lebih baik untuk query dan manipulasi, karena jauh lebih cepat.

Contoh Penggunaan JSONB

Bayangkan Anda ingin menyimpan riwayat pendidikan setiap karyawan tanpa perlu membuat tabel terpisah untuk setiap detail.

ALTER TABLE employees
ADD COLUMN details JSONB;

-- Memasukkan data JSONB
UPDATE employees
SET details = '{
    "education": [
        {"level": "S1", "major": "Informatika"},
        {"level": "S2", "major": "Manajemen"}
    ],
    "skills": ["Python", "SQL", "Cloud"]
}'
WHERE first_name = 'Budi';

Operator Query JSONB

PostgreSQL menyediakan operator khusus untuk query pada data JSONB:

2. ARRAY: Menyimpan Daftar Nilai Sederhana

PostgreSQL memungkinkan Anda menyimpan daftar nilai (array) dalam satu kolom. Ini berguna untuk menyimpan tag, daftar ID, atau data lain yang merupakan kumpulan dari tipe data yang sama.

-- Membuat Tabel Produk dengan Kolom Array
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255),
    tags TEXT[] -- Kolom array teks
);

-- Memasukkan data array
INSERT INTO products (product_name, tags) VALUES
('Laptop Gaming', ARRAY['elektronik', 'premium', 'diskrit']),
('Monitor Ultra-wide', ARRAY['elektronik', 'monitor']);

Operator Query ARRAY

• ANY / ALL: Untuk perbandingan dengan nilai array.

• @> (Contains): Memeriksa apakah array mengandung elemen lain.

• && (Overlap): Memeriksa apakah dua array memiliki elemen yang tumpang tindih.

-- Temukan produk yang memiliki tag 'elektronik'
SELECT product_name FROM products
WHERE 'elektronik' = ANY (tags);

🏆 Fungsi Agregat dan Pengelompokan Data

Fungsi Agregat (Aggregate Functions) adalah salah satu fitur DML yang paling kuat, digunakan untuk menghitung nilai tunggal dari sekumpulan baris.

Untuk menerapkan fungsi agregat pada kelompok-kelompok baris yang berbeda, kita menggunakan klausa GROUP BY.

-- Menghitung rata-rata gaji dan jumlah karyawan per departemen
SELECT
    d.dept_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.employee_id) > 1 -- Hanya tampilkan departemen dengan > 1 karyawan
ORDER BY average_salary DESC;

HAVING vs WHERE: WHERE memfilter baris sebelum pengelompokan (GROUP BY); HAVING memfilter kelompok setelah pengelompokan.

💻 Kesimpulan: Kunci Penguasaan SQL DDL dan DML

Seri 2 telah membekali Anda dengan pengetahuan penting tentang cara merancang skema (DDL) dan cara memanipulasi data di dalamnya (DML). Penguasaan constraints menjamin integritas data, sementara pemahaman tentang tipe data lanjutan seperti JSONB dan ARRAY membuka pintu untuk menyimpan dan mengolah data yang lebih kompleks dan beragam.

Kunci penguasaan SQL di PostgreSQL terletak pada:

1. Presisi DDL: Selalu definisikan constraints (Primary Key, Foreign Key, NOT NULL) secara ketat.

2. Efisiensi DML: Selalu gunakan WHERE untuk memfilter, dan pahami perbedaan antara GROUP BY dan HAVING.

3. Memanfaatkan Tipe Data Khusus: Jangan takut menggunakan JSONB atau Arrays untuk data yang bersifat semi-terstruktur atau berulang.

Untuk melanjutkan penguasaan Anda, praktikkan perintah-perintah ini dan bersiaplah untuk Seri 3, di mana kita akan menggali query yang lebih kompleks, teknik indexing yang akan mempercepat query Anda, dan fitur optimasi kinerja lainnya.

Berikut adalah command ringkasan DDL dan DML yang harus Anda kuasai:

-- DDL ESSENTIALS
CREATE TABLE [nama_tabel] ( [kolom] [tipe_data] [constraint], ... );
ALTER TABLE [nama_tabel] ADD COLUMN [nama_kolom] [tipe_data];
ALTER TABLE [nama_tabel] ADD CONSTRAINT [fk_nama] FOREIGN KEY (kolom_lokal) REFERENCES [tabel_lain];
DROP TABLE [nama_tabel] CASCADE;

-- DML ESSENTIALS
INSERT INTO [nama_tabel] (kolom1, kolom2) VALUES (nilai1, nilai2);
SELECT kolom1, fungsi_agregat(kolom_data) FROM [tabel] WHERE kondisi GROUP BY kolom1 HAVING kondisi_agregat ORDER BY kolom1;
UPDATE [nama_tabel] SET [kolom] = [nilai_baru] WHERE [kondisi_pemfilteran];
DELETE FROM [nama_tabel] WHERE [kondisi_pemfilteran];

-- DML POSTGRESQL ADVANCED (JSONB/ARRAY)
SELECT data_jsonb ->> 'key' FROM [tabel] WHERE data_array @> ARRAY['nilai'];

Anda telah menguasai dasar-dasar bahasa basis data. Langkah berikutnya adalah bagaimana Anda membuat data ini bergerak dengan cepat.