Tutorial PostgreSQL dengan Contoh yang Bisa Dijalankan
Tutorial PostgreSQL gratis dengan query yang berjalan di halaman: tipe data, CREATE TABLE, upsert, JSONB, tanggal, CTE, window function, indeks, dan VACUUM. Edit query-nya dan jalankan lagi.
Mulai kursusMemulai
- PostgreSQL vs MySQLPostgreSQL dan MySQL sama-sama database relasional gratis. PostgreSQL punya SQL yang lebih kaya, JSONB, dan DDL dalam transaksi; MySQL lebih mudah dijalankan di tempat yang sudah memakainya sebagai default.
- PostgreSQL vs SQL ServerPostgreSQL gratis dan open source; SQL Server adalah database komersial Microsoft dengan T-SQL dan tool yang berpusat pada Windows. Begini perbedaan SQL, JSON, locking, dan lisensinya.
- PostgreSQL vs MongoDBPostgreSQL menyimpan baris di tabel dengan schema tetap dan bisa menampung dokumen JSON di kolom jsonb; MongoDB menyimpan dokumen dengan schema fleksibel di collection dan membaginya (sharding) ke banyak server.
- Daftar database dan tabelDi psql, \l menampilkan daftar database, \c berpindah ke salah satunya, dan \dt menampilkan tabelnya. Di SQL, query pg_database, information_schema.tables, dan information_schema.columns.
Tabel & Tipe
- Tipe dataTipe data PostgreSQL yang akan kamu pakai di hampir setiap kolom: integer, numeric, text, boolean, timestamptz, uuid, jsonb, array, dan enum, beserta kapan memilih masing-masing.
- CREATE TABLECREATE TABLE mendefinisikan kolom, tipe, dan constraint sebuah tabel. Membahas identity key, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS, dan LIKE.
- ALTER TABLEALTER TABLE mengubah tabel yang sudah ada: menambah, mengganti nama, dan menghapus kolom, mengubah tipe kolom dengan USING, mengatur default dan NOT NULL, serta menambah constraint.
- Primary keyCara mendefinisikan primary key di PostgreSQL dan cara membuat nilainya: kolom identity, SERIAL yang lebih lama, dan UUID dengan gen_random_uuid(). Juga composite key dan reset sequence.
- Foreign keyForeign key membuat sebuah kolom hanya menerima nilai yang ada di tabel lain. Membahas REFERENCES, ON DELETE CASCADE, SET NULL dan RESTRICT, menambah key ke tabel yang sudah ada, dan meng-index-nya.
- Tipe enumTipe enum adalah daftar nilai yang diizinkan, tetap dan berurutan, dibuat dengan CREATE TYPE ... AS ENUM. Membahas menambah dan mengganti nama nilai, melihat daftarnya, pengurutan, dan kapan constraint CHECK lebih tepat.
- Timestamptimestamptz menyimpan satu momen waktu dan menampilkannya di zona waktu sesi; timestamp menyimpan waktu jam dinding tanpa zona. Keduanya tidak menyimpan zona yang kamu tulis. Pakai timestamptz untuk hampir semuanya.
Kueri
- JoinJoin menggabungkan baris dari dua tabel berdasarkan sebuah kondisi. INNER JOIN menyimpan yang cocok, LEFT JOIN menyimpan setiap baris tabel pertama, FULL JOIN menyimpan keduanya, CROSS JOIN memasangkan semuanya, dan LATERAL menjalankan subquery per baris.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort menyimpan baris pertama dari setiap kelompok, misalnya pesanan terbaru setiap pelanggan. Fitur ini hanya ada di PostgreSQL.
- COALESCE, NULLIF, dan CASECOALESCE(a, b, ...) mengembalikan argumen pertama yang bukan NULL, NULLIF(a, b) mengubah sebuah nilai menjadi NULL, dan CASE WHEN memilih nilai berdasarkan kondisi.
- LIKE, ILIKE, dan regexLIKE mencocokkan teks dengan pola memakai % dan _, ILIKE melakukan hal yang sama tanpa memedulikan huruf besar kecil, dan operator ~ mencocokkan regular expression POSIX.
- CAST dan ::CAST(value AS type) dan value::type mengonversi nilai ke tipe lain di PostgreSQL. Keduanya konversi yang sama; :: adalah sintaks PostgreSQL yang lebih pendek.
Mengubah Data
- Upsert (ON CONFLICT)Upsert di PostgreSQL dengan INSERT ... ON CONFLICT: DO UPDATE dengan EXCLUDED, DO NOTHING, update bersyarat, counter, dan membedakan baris yang dimasukkan dari yang diperbarui.
- RETURNINGRETURNING membuat INSERT, UPDATE, DELETE, dan MERGE mengembalikan baris yang diubahnya: id yang dibuat, nilai baru, baris yang dihapus, dan aksi MERGE mana yang dijalankan.
- TransaksiCara kerja transaksi PostgreSQL: BEGIN, COMMIT, dan ROLLBACK, savepoint, apa akibat error pada transaksi yang terbuka, dan level isolasi.
JSON & Array
- JSONBSimpan dan query JSON di PostgreSQL dengan jsonb: operator -> dan ->>, containment @>, jsonb_set dan update lainnya, mengubah array menjadi baris, index GIN, serta json vs jsonb.
- ArrayKolom array di PostgreSQL: cara mendeklarasikannya, indeks yang dimulai dari 1, mengecek apakah array berisi sebuah nilai dengan ANY, @>, dan &&, unnest, serta fungsi array yang kamu butuhkan.
- string_agg dan array_aggstring_agg menggabungkan nilai dalam satu kelompok menjadi satu string dan array_agg mengumpulkannya menjadi array. Pengurutan, DISTINCT, FILTER, penanganan NULL, dan error yang sering muncul.
String & Tanggal
- Fungsi stringFungsi string PostgreSQL yang dipakai sehari-hari: substring, left dan right, || dan concat, split_part, position, replace, regexp_replace, trim, lpad, dan format.
- Fungsi tanggalBekerja dengan tanggal di PostgreSQL: now() dan current_date, menambah dan mengurangi interval, date_trunc untuk pengelompokan, extract dan age, serta memformat dengan to_char.
- generate_seriesgenerate_series mengembalikan sekumpulan baris dari nilai awal sampai nilai akhir: angka, tanggal, dan timestamp. Pakai untuk mengisi celah di laporan, membuat data uji, dan menyusun kalender.
Kueri Lanjutan
- CTE (WITH)Common table expression memberi nama pada subquery di klausa WITH supaya query utama bisa membacanya seperti tabel. WITH RECURSIVE memungkinkannya merujuk dirinya sendiri untuk menelusuri pohon dan graf.
- Window functionWindow function menghitung nilai untuk setiap baris dari sekelompok baris yang berhubungan tanpa meringkasnya: peringkat, total berjalan, rata-rata bergerak, dan perbandingan dengan baris sebelumnya.
Performa & Pemeliharaan
- IndexCREATE INDEX secara default membuat index B-tree supaya PostgreSQL bisa menemukan baris tanpa membaca seluruh tabel. Jenis lain (GIN, GiST, BRIN, hash) mencakup JSONB, array, pencarian full-text, rentang, dan tabel yang sangat besar.
- EXPLAIN ANALYZEEXPLAIN menampilkan rencana yang dipilih PostgreSQL untuk sebuah query, dan EXPLAIN ANALYZE menjalankan query itu lalu menambahkan jumlah baris dan waktu sebenarnya di setiap langkah. Pelajari cara membaca pohon rencana dan nama node yang umum.
- View dan materialized viewView adalah query tersimpan yang dijalankan setiap kali kamu membacanya. Materialized view menyimpan hasil query dan hanya berubah saat kamu menjalankan REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM menghapus versi baris mati yang ditinggalkan UPDATE dan DELETE supaya ruangnya bisa dipakai ulang. Autovacuum menjalankannya untukmu; halaman ini menunjukkan kapan, cara mengaturnya, dan kapan VACUUM FULL sepadan dengan lock-nya.
Fungsi & Trigger
- Fungsi dan procedureCREATE FUNCTION mendefinisikan fungsi yang kamu panggil di dalam query, ditulis dalam SQL atau PL/pgSQL. CREATE PROCEDURE mendefinisikan rutin yang kamu jalankan dengan CALL, yang bisa melakukan commit transaksi di tengah jalan.
- TriggerTrigger menjalankan fungsi secara otomatis saat baris dimasukkan, diperbarui, atau dihapus. Di PostgreSQL, kamu menulis trigger function yang mengembalikan trigger, lalu memasangnya ke tabel dengan CREATE TRIGGER.