Cheatsheet PostgreSQL yang Wajib Kamu Ketahui

PostgreSQL adalah salah satu sistem manajemen basis data relasional (RDBMS) open-source yang paling populer dan powerful di dunia. Dikenal dengan kehandalannya, fitur-fitur canggih, dan dukungan untuk berbagai tipe data, PostgreSQL telah menjadi pilihan utama bagi banyak developer dan perusahaan besar. Dalam artikel ini, kami akan membagikan cheatsheet PostgreSQL yang komprehensif untuk membantu Anda dalam mengelola database dengan lebih efisien.

Mengapa Cheatsheet PostgreSQL Penting?

Cheatsheet PostgreSQL sangat berguna bagi developer, database administrator, dan DevOps engineer yang bekerja dengan database setiap hari. Dengan memiliki referensi cepat tentang perintah-perintah penting, Anda dapat menghemat waktu dan meningkatkan produktivitas. Baik Anda seorang pemula yang baru belajar PostgreSQL atau seorang profesional berpengalaman, cheatsheet ini akan menjadi panduan praktis yang sangat membantu.

Koneksi ke PostgreSQL

Sebelum mulai bekerja dengan PostgreSQL, Anda perlu tahu cara terhubung ke database server:

psql -U username -d database_name -h hostname -p port

Koneksi menggunakan psql:

Keluar dari psql:

\q
\quit

Koneksi dengan password:

psql -U username -W

Melihat daftar database:

\l
\list

Beralih ke database lain:

\c database_name

Operasi Database Dasar

Membuat Database Baru

Untuk membuat database baru di PostgreSQL:

CREATE DATABASE nama_database;

Membuat database dengan encoding tertentu:

CREATE DATABASE nama_database ENCODING 'UTF8';

Menghapus Database

DROP DATABASE nama_database;

DROP DATABASE IF EXISTS nama_database;

Melihat Informasi Database

Melihat database yang sedang digunakan:

SELECT current_database();

Melihat ukuran database:

SELECT pg_size_pretty(pg_database_size('nama_database'));

Operasi Tabel

Membuat Tabel

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Melihat Struktur Tabel

Menampilkan deskripsi tabel:

\d nama_tabel

Melihat semua tabel:

\dt

Melihat detail lengkap tabel:

\d+ nama_tabel

Mengubah Tabel

Menambah kolom baru:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Menghapus kolom:

ALTER TABLE users DROP COLUMN phone;

Mengubah tipe data kolom:

ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

Menambah constraint:

ALTER TABLE users ADD CONSTRAINT check_email CHECK (email LIKE '%@%');

Menghapus Tabel

DROP TABLE nama_tabel;

DROP TABLE IF EXISTS nama_tabel CASCADE;

Manipulasi Data (CRUD Operations)

INSERT – Menambah Data

Menambah satu baris:

INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

Menambah multiple baris:

INSERT INTO users (username, email) VALUES
('user1', '[email protected]'),
('user2', '[email protected]'),
('user3', '[email protected]');

Insert dengan return:

INSERT INTO users (username, email) VALUES ('jane', '[email protected]') RETURNING id;

SELECT – Membaca Data

Select semua data:

SELECT * FROM users;

Select kolom tertentu:

SELECT username, email FROM users;

Select dengan kondisi:

SELECT * FROM users WHERE id = 1;

Select dengan multiple kondisi:

SELECT * FROM users WHERE username = 'john_doe' AND email LIKE '%@example.com';

Select dengan sorting:

SELECT * FROM users ORDER BY created_at DESC;

Select dengan limit:

SELECT * FROM users LIMIT 10 OFFSET 0;

UPDATE – Mengubah Data

Update satu kolom:

UPDATE users SET email = '[email protected]' WHERE id = 1;

Update multiple kolom:

UPDATE users SET username = 'new_username', email = '[email protected]' WHERE id = 1;

Update dengan return:

UPDATE users SET email = '[email protected]' WHERE id = 1 RETURNING *;

DELETE – Menghapus Data

Hapus dengan kondisi:

DELETE FROM users WHERE id = 1;

Hapus semua data:

DELETE FROM users;

Hapus dengan return:

DELETE FROM users WHERE id = 1 RETURNING *;

Query Lanjutan

JOIN Operations

INNER JOIN:

SELECT users.username, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN:

SELECT users.username, orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

RIGHT JOIN:

SELECT users.username, orders.order_date
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

FULL OUTER JOIN:

SELECT users.username, orders.order_date
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Subqueries

Subquery di WHERE:

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Subquery di SELECT:

SELECT username, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count FROM users;

Aggregate Functions

COUNT:

SELECT COUNT(*) FROM users;

SUM:

SELECT SUM(total) FROM orders;

AVG:

SELECT AVG(total) FROM orders;

MAX dan MIN:

SELECT MAX(total), MIN(total) FROM orders;

GROUP BY:

SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id;

HAVING:

SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 5;

Indexes

Membuat index:

CREATE INDEX idx_username ON users(username);

Membuat unique index:

CREATE UNIQUE INDEX idx_email ON users(email);

Membuat index untuk multiple kolom:

CREATE INDEX idx_name_email ON users(username, email);

Melihat index:

\di

Menghapus index:

DROP INDEX idx_username;

User Management dan Permissions

Membuat User

CREATE USER username WITH PASSWORD 'password';

CREATE USER username WITH ENCRYPTED PASSWORD 'password';

Mengubah Password User

ALTER USER username WITH PASSWORD 'new_password';

Memberikan Privileges

Grant all privileges pada database:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Grant privileges pada tabel:

GRANT SELECT, INSERT, UPDATE ON TABLE table_name TO username;

Grant privileges pada schema:

GRANT ALL ON SCHEMA public TO username;

Mencabut Privileges

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

Melihat User dan Role

Melihat semua user:

\du

Menghapus User

DROP USER username;