Perintah psql
Meta-command dijalankan di dalam psql (client terminal PostgreSQL), bukan di server. Perintah ini diawali backslash dan tidak perlu titik koma.
| Kegunaan | Perintah |
|---|
| Tampilkan daftar database | \l |
| Terhubung ke database lain | \c shop |
| Tampilkan daftar tabel | \dt |
| Tampilkan tabel di semua schema | \dt *.* |
| Jelaskan struktur tabel (kolom, indeks, key) | \d users |
| Jelaskan beserta info penyimpanan dan komentar | \d+ users |
| Tampilkan daftar schema | \dn |
| Tampilkan daftar view | \dv |
| Tampilkan daftar indeks | \di |
| Tampilkan daftar fungsi | \df |
| Tampilkan daftar role (user) | \du |
| Tampilkan koneksi saat ini | \conninfo |
| Jalankan SQL dari file | \i setup.sql |
| Nyalakan atau matikan pengukuran waktu query | \timing |
| Output diperluas (satu kolom per baris) | \x |
| Edit query terakhir di editormu | \e |
| Bantuan sintaks SQL | \h CREATE TABLE |
| Tampilkan semua meta-command | \? |
| Keluar dari psql | \q |
Terhubung dari terminal
| Kegunaan | Perintah |
|---|
| Terhubung ke database sebagai user tertentu | psql -U postgres -d shop |
| Terhubung ke host dan port remote | psql -h db.example.com -p 5432 -U app -d shop |
| Terhubung dengan URL koneksi | psql postgresql://app:secret@localhost:5432/shop |
| Jalankan satu query lalu keluar | psql -d shop -c "SELECT count(*) FROM users;" |
| Jalankan file SQL | psql -d shop -f setup.sql |
| Buat database dari shell | createdb shop |
| Hapus database dari shell | dropdb shop |
Database dan schema
| Kegunaan | SQL |
|---|
| Buat database | CREATE DATABASE shop; |
| Hapus database | DROP DATABASE shop; |
| Tampilkan database saat ini | SELECT current_database(); |
| Buat schema | CREATE SCHEMA sales; |
| Tampilkan urutan pencarian schema | SHOW search_path; |
| Cari di schema tertentu lebih dulu | SET search_path TO sales, public; |
| Tampilkan versi server | SELECT version(); |
Tabel dan tipe data
| Kegunaan | SQL |
|---|
| Buat tabel dengan key identity | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| Tambah kolom | ALTER TABLE users ADD COLUMN phone text; |
| Ganti nama kolom | ALTER TABLE users RENAME COLUMN country TO country_code; |
| Ubah tipe kolom | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| Atur nilai default | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| Hapus kolom | ALTER TABLE users DROP COLUMN tags; |
| Kosongkan tabel dan reset identity-nya | TRUNCATE orders, users RESTART IDENTITY; |
| Hapus tabel jika ada | DROP TABLE IF EXISTS products; |
| Desimal pasti untuk nilai uang | numeric(10,2) |
| Tanggal dan waktu dengan zona waktu | timestamptz |
| UUID acak | SELECT gen_random_uuid(); |
| Tipe enum | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
Constraint dan key
| Kegunaan | SQL |
|---|
| Tambah primary key | ALTER TABLE logs ADD PRIMARY KEY (id); |
| Foreign key yang ikut menghapus baris anak | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| Tambah check constraint | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| Unique yang menganggap NULL sama (15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| Jadikan kolom wajib diisi | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| Hapus constraint | ALTER TABLE users DROP CONSTRAINT users_email_key; |
Query (tambahan PostgreSQL)
SELECT, WHERE, dan JOIN standar ada di cheat sheet SQL; berikut bentuk khas PostgreSQL.
| Kegunaan | SQL |
|---|
| Pencocokan tanpa membedakan huruf besar dan kecil | SELECT * FROM users WHERE name ILIKE 'a%'; |
| Pencocokan regular expression | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| Baris pertama per grup | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| Limit dan offset | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| Batas baris standar | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| Cast dengan :: | SELECT '42'::integer + 1; |
| Nilai pengganti NULL | SELECT name, COALESCE(email, 'none') FROM users; |
| Bandingkan dengan menganggap NULL sama | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| Buat deret angka | SELECT * FROM generate_series(1, 5); |
| Gabungkan menjadi string | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| Agregasi bersyarat | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
Insert, update, delete, dan upsert
| Kegunaan | SQL |
|---|
| Insert dan dapatkan id baru | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| Insert beberapa baris | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert (insert atau update) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| Insert, abaikan duplikat | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| Update dan lihat baris yang berubah | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| Update dari tabel lain | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| Delete dan lihat apa yang dihapus | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| Delete memakai tabel lain | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| Salin baris ke tabel baru | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| Kegunaan | SQL |
|---|
| Ambil field sebagai JSON | SELECT profile -> 'plan' FROM users; |
| Ambil field sebagai teks | SELECT profile ->> 'plan' FROM users; |
| Ambil field bersarang sebagai teks | SELECT profile #>> '{address,city}' FROM users; |
| Baris yang JSON-nya berisi suatu nilai | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| Baris yang punya key tertentu | SELECT * FROM users WHERE profile ? 'city'; |
| Atur nilai key | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| Gabungkan object | UPDATE users SET profile = profile || '{"beta": true}'; |
| Hapus key | UPDATE users SET profile = profile - 'city'; |
| Bangun JSON dari kolom | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| Baris sebagai array JSON | SELECT jsonb_agg(users) FROM users; |
| Indeks untuk pencarian @> dan ? | CREATE INDEX users_profile_idx ON users USING gin (profile); |
Array
| Kegunaan | SQL |
|---|
| Literal array | SELECT ARRAY[1, 2, 3]; |
| Baris yang array-nya berisi suatu nilai | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| Baris yang array-nya berisi semua nilai | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| Satu baris per elemen | SELECT name, unnest(tags) FROM users; |
| Tambahkan elemen di akhir | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| Panjang array | SELECT name, cardinality(tags) FROM users; |
Tanggal dan waktu
| Kegunaan | SQL |
|---|
| Sekarang, dengan zona waktu | SELECT now(); |
| Hari ini | SELECT current_date; |
| Tambah interval | SELECT current_date + INTERVAL '7 days'; |
| Baris dari 30 hari terakhir | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| Potong ke bulan | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| Ambil bagian tertentu | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| Selisih tanggal (hari) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| Usia di antara dua tanggal | SELECT name, age(current_date, hired_on) FROM employees; |
| Format tanggal sebagai teks | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| Ubah teks menjadi tanggal | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| Konversi ke zona waktu | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
Fungsi teks
| Kegunaan | SQL |
|---|
| Gabungkan (NULL menghasilkan NULL) | SELECT name || ' <' || email || '>' FROM users; |
| Gabungkan, lewati NULL | SELECT concat_ws(' ', name, email) FROM users; |
| Huruf besar dan kecil | SELECT upper(name), lower(name) FROM users; |
| Panjang | SELECT name, length(name) FROM users; |
| Bagian dari string | SELECT substring(name FROM 1 FOR 2) FROM users; |
| Ganti teks | SELECT replace(email, '@example.com', '') FROM users; |
| Ambil satu bagian hasil pemisahan | SELECT split_part(email, '@', 2) FROM users; |
| Ganti dengan regular expression | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| Hapus spasi di tepi | SELECT trim(' hi '); |
CTE dan window function
| Kegunaan | SQL |
|---|
| Beri nama subquery dengan WITH | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| CTE rekursif (hitung sampai 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| Beri nomor baris per grup | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| Peringkat dengan nilai seri | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| Total berjalan | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| Nilai baris sebelumnya | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| Join ke subquery milik tiap baris | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
Indeks
| Kegunaan | SQL |
|---|
| Indeks B-tree (default) | CREATE INDEX orders_user_idx ON orders (user_id); |
| Indeks unik | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| Indeks multikolom | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| Indeks parsial | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| Indeks ekspresi | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| Covering index (index-only scan) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| Bangun tanpa memblokir penulisan | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| Hapus indeks | DROP INDEX IF EXISTS orders_user_idx; |
| Lihat rencana eksekusi | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| Lihat rencana eksekusi dengan waktu nyata | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
Transaksi dan lock
| Kegunaan | SQL |
|---|
| Mulai, lalu simpan atau batalkan | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| Batalkan sebagian transaksi | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| Isolasi lebih ketat | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| Kunci baris yang kamu baca | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| Antrean job: lewati baris yang terkunci | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
User, role, dan hak akses
| Kegunaan | SQL |
|---|
| Buat role yang bisa login | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| Akses baca saja ke tabel | GRANT SELECT ON users TO app; |
| Akses baca ke semua tabel dalam schema | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| Cabut hak akses | REVOKE SELECT ON users FROM app; |
| Ganti password | ALTER ROLE app PASSWORD 'new-secret'; |
| Tampilkan user saat ini | SELECT current_user; |
Maintenance dan pemantauan
| Kegunaan | SQL |
|---|
| Query yang sedang berjalan | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| Batalkan query | SELECT pg_cancel_backend(12345); |
| Akhiri koneksi | SELECT pg_terminate_backend(12345); |
| Ukuran tabel beserta indeksnya | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| Ukuran database | SELECT pg_size_pretty(pg_database_size(current_database())); |
| Perbarui statistik planner | ANALYZE orders; |
| Bersihkan baris mati | VACUUM orders; |
| Tulis ulang tabel ke ukuran minimum (menguncinya) | VACUUM FULL orders; |
| Tampilkan pengaturan | SHOW work_mem; |
| Ubah pengaturan untuk sesi ini | SET work_mem = '64MB'; |
Backup dan restore
Perintah shell dari tool client PostgreSQL.
| Kegunaan | Perintah |
|---|
| Dump database ke file SQL | pg_dump -U postgres shop > shop.sql |
| Dump dalam format custom | pg_dump -Fc -U postgres shop > shop.dump |
| Dump satu tabel | pg_dump -t orders shop > orders.sql |
| Restore dump SQL | psql -d shop -f shop.sql |
| Restore dump format custom | pg_restore -d shop shop.dump |
| Dump semua database dan role | pg_dumpall > cluster.sql |
| Ekspor query ke CSV (dari psql) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| Impor CSV (dari psql) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
Semua perintah psql dan pernyataan PostgreSQL yang sering kamu pakai, dalam tabel siap salin: koneksi dan perintah \d lebih dulu, lalu tabel, upsert, JSONB, tanggal, window function, indeks, role, backup, dan maintenance.
SQL di sini memakai sintaks PostgreSQL 17, dan sebagian besar juga berjalan di versi sebelumnya. Pernyataan SQL-nya sudah diperiksa di PostgreSQL 17; untuk mencobanya, tempel ke playground PostgreSQL, yang sudah dilengkapi tabel contoh.