belajarkoding © 2025 BelajarKoding. All rights reserved.
PostgreSQL Cheat Sheet - BelajarKoding | BelajarKoding
PostgreSQL Cheat Sheet Quick reference PostgreSQL. SQL commands, data types, joins, indexes, dan common queries untuk relational database development.
Lanjutkan Membaca
Daftar gratis untuk akses penuh ke semua artikel dan cheat sheet. Cepat, mudah, dan tanpa biaya!
Akses Tanpa Batas
Baca semua artikel dan cheat sheet kapan pun kamu mau
Bookmark Konten
Simpan artikel dan roadmap favoritmu untuk dibaca nanti
Gratis Selamanya
Tidak ada biaya tersembunyi, 100% gratis
Dengan mendaftar, kamu setuju dengan syarat dan ketentuan kami
# psql CommandsPerintah-perintah command line untuk berinteraksi dengan PostgreSQL database menggunakan psql client.
# Login psql -U username -d database
# List databases \l
# Connect to database \c database_name
# List tables \dt
# Describe table \d table_name
# List indexes \di
# List users/roles \du
# Execute SQL file \i file.sql
# Export query to file \o output.txt
# Toggle timing \timing
# Exit \q # Database OperationsOperasi-operasi dasar untuk membuat, menghapus, dan mengelola database.
-- Create database CREATE DATABASE mydb ;
-- Drop database DROP DATABASE mydb; DROP DATABASE IF EXISTS mydb;
-- Rename database ALTER DATABASE mydb RENAME TO newdb; # Table OperationsOperasi untuk membuat, memodifikasi, dan menghapus tabel dalam database.
-- Create table CREATE TABLE users ( id SERIAL PRIMARY KEY , name VARCHAR ( 100 ) NOT NULL , email VARCHAR ( 255 ) # Data TypesBerbagai tipe data yang tersedia di PostgreSQL untuk kolom tabel.
Type Description Example SERIAL Auto-incrementing integer id SERIAL PRIMARY KEY INTEGER Whole numbers age INTEGER BIGINT Large integers views BIGINT DECIMAL(10,2) Exact decimal price DECIMAL(10,2) REAL Float (6 digits) rating REAL VARCHAR(n) Variable string (max n) name VARCHAR(100) TEXT Unlimited string content TEXT
# CRUD OperationsOperasi dasar Create, Read, Update, Delete pada data tabel.
# INSERTMenambahkan data baru ke dalam tabel.
-- Single row INSERT INTO users ( name , email) VALUES ( 'Galih' , 'galih@example.com' );
-- Multiple rows INSERT INTO users ( name , email) VALUES ( 'Budi' , 'budi@example.com' ), ( 'Ani' # SELECTMengambil data dari tabel dengan berbagai kondisi dan filter.
-- Select all SELECT * FROM users;
-- Select specific columns SELECT name , email FROM users;
-- WHERE clause # UPDATEMengubah data yang sudah ada dalam tabel.
-- Update single row UPDATE users SET name = 'Galih Pratama' WHERE id = 1 ;
-- Update multiple columns UPDATE users SET name = 'Budi' , age = 31 , updated_at = # DELETEMenghapus data dari tabel.
-- Delete specific rows DELETE FROM users WHERE id = 1 ;
-- Delete with condition DELETE FROM users WHERE age < 18 ;
-- Delete all rows (careful!) DELETE FROM users;
-- Delete with RETURNING # JoinsMenggabungkan data dari multiple tabel berdasarkan relasi.
-- INNER JOIN (matching rows only) SELECT u . name , p . title FROM users u INNER JOIN posts p ON u . id = p . user_id ;
# ConstraintsAturan-aturan yang membatasi tipe data dan relasi dalam tabel.
-- PRIMARY KEY CREATE TABLE users ( id SERIAL PRIMARY KEY );
-- FOREIGN KEY CREATE TABLE posts ( id SERIAL PRIMARY KEY , user_id # Indexes-- Create index CREATE INDEX idx_users_email ON users(email);
-- Unique index CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Multiple columns CREATE INDEX idx_users_name_age ON users( name , age);
-- Partial index (conditional) # Transactions-- Start transaction BEGIN ;
-- Run queries UPDATE accounts SET balance = balance - 100 WHERE id = 1 ; UPDATE accounts SET balance = balance + # JSON Operations-- Create table with JSONB CREATE TABLE products ( id SERIAL PRIMARY KEY , data JSONB );
-- Insert JSON INSERT INTO products ( data ) # Views-- Create view CREATE VIEW active_users AS SELECT id, name , email FROM users WHERE active = TRUE;
-- Use view SELECT * FROM active_users;
-- Materialized view (cached) CREATE # Functions-- Simple function CREATE FUNCTION get_user_count () RETURNS INTEGER AS $$ BEGIN RETURN ( SELECT COUNT ( * ) FROM users); END ; $$ LANGUAGE # Triggers-- Function for trigger CREATE OR REPLACE FUNCTION update_updated_at () RETURNS TRIGGER AS $$ BEGIN NEW . updated_at = CURRENT_TIMESTAMP; RETURN NEW; END ; $$ LANGUAGE plpgsql;
-- Create trigger # Pattern Umum# UUID Primary KeyCREATE EXTENSION IF NOT EXISTS "uuid-ossp" ;
CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name VARCHAR ( 100 ) ); # Soft DeleteCREATE TABLE users ( id SERIAL PRIMARY KEY , name VARCHAR ( 100 ), deleted_at TIMESTAMP NULL );
-- Soft delete UPDATE users SET deleted_at = NOW () WHERE # Auto-Update TimestampCREATE TABLE posts ( id SERIAL PRIMARY KEY , title VARCHAR ( 200 ), updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
CREATE OR REPLACE FUNCTION update_updated_at_column () RETURNS -- Offset-based (simple) SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20 ;
-- Cursor-based (better performance) SELECT * FROM users WHERE id > 20 ORDER BY id LIMIT 10 ; # Performance# EXPLAIN-- Show query plan EXPLAIN SELECT * FROM users WHERE email = 'test@example.com' ;
-- Show actual execution EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25 ; # Vacuum-- Clean up dead rows VACUUM users;
-- Full vacuum (more thorough) VACUUM FULL users;
-- Analyze (update statistics) ANALYZE users;
-- Combined VACUUM ANALYZE users; # Backup & Restore# Backup database pg_dump -U postgres mydb > backup.sql
# Backup with compression pg_dump -U postgres mydb | gzip > backup.sql.gz
# Restore database psql -U postgres mydb < backup.sql
# User Management-- Create user CREATE USER myuser WITH PASSWORD 'mypassword' ;
-- Grant privileges GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser; GRANT SELECT , INSERT , UPDATE ON users TO # Connection from Code# Node.js (pg)const { Pool } = require ( 'pg' );
const pool = new Pool ({ user: 'postgres' , host: 'localhost' , database: 'mydb' , password: 'password' # Prismadatasource db { provider = "postgresql" url = env ( "DATABASE_URL" ) }
model User { id Int @id @default ( autoincrement ()) email String const { PrismaClient } = require ( '@prisma/client' ); const prisma = new PrismaClient ();
const users = await prisma.user. findMany ({ include: { posts: true } }); # Common Functions-- String functions LENGTH ( 'hello' ) -- 5 UPPER ( 'hello' ) -- 'HELLO' LOWER ( 'HELLO' ) -- 'hello' CONCAT ( 'Hello' , # Docker Setup# docker-compose.yml version : '3.8' services : postgres : image : postgres:16 environment : POSTGRES_USER : postgres POSTGRES_PASSWORD : mypassword POSTGRES_DB : mydb ports : docker-compose up -d docker exec -it postgres_container psql -U postgres # Best Practices# ResourcesHappy querying! 🐘
UNIQUE
,
age INTEGER CHECK (age >= 18 ),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Drop table
DROP TABLE users;
DROP TABLE IF EXISTS users CASCADE;
-- Rename table
ALTER TABLE users RENAME TO customers;
-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR ( 20 );
-- Drop column
ALTER TABLE users DROP COLUMN phone;
-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Change column type
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT ;
-- Add constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
CHAR(n)
BOOLEAN TRUE/FALSE active BOOLEAN
DATE Date only birthdate DATE
TIME Time only start_time TIME
TIMESTAMP Date + time created_at TIMESTAMP
TIMESTAMPTZ Timestamp with timezone logged_at TIMESTAMPTZ
UUID Unique identifier id UUID
JSON JSON data (text) data JSON
JSONB Binary JSON (faster) settings JSONB
INTEGER[] Array of integers tags INTEGER[]
VARCHAR(100)[] Array of strings emails VARCHAR(100)[]
,
'ani@example.com'
);
-- Return inserted row
INSERT INTO users ( name , email)
VALUES ( 'Rina' , 'rina@example.com' )
RETURNING * ;
-- Insert from SELECT
INSERT INTO users_backup SELECT * FROM users;
SELECT
*
FROM
users
WHERE
age
>=
25
;
-- Multiple conditions
SELECT * FROM users WHERE age >= 25 AND active = TRUE;
-- OR condition
SELECT * FROM users WHERE age < 20 OR age > 60 ;
-- IN operator
SELECT * FROM users WHERE id IN ( 1 , 2 , 3 );
-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 25 AND 35 ;
-- LIKE (pattern matching)
SELECT * FROM users WHERE email LIKE '%@gmail.com' ;
-- ILIKE (case-insensitive)
SELECT * FROM users WHERE name ILIKE '%john%' ;
-- IS NULL
SELECT * FROM users WHERE phone IS NULL ;
-- ORDER BY
SELECT * FROM users ORDER BY created_at DESC ;
-- LIMIT & OFFSET
SELECT * FROM users LIMIT 10 OFFSET 20 ;
-- DISTINCT
SELECT DISTINCT age FROM users;
-- Aggregate functions
SELECT COUNT ( * ) FROM users;
SELECT AVG (age) FROM users;
SELECT SUM (price) FROM products;
SELECT MAX (age), MIN (age) FROM users;
-- GROUP BY
SELECT age, COUNT ( * ) as count
FROM users
GROUP BY age;
-- HAVING (filter after GROUP BY)
SELECT age, COUNT ( * ) as count
FROM users
GROUP BY age
HAVING COUNT ( * ) > 5 ;
NOW
()
WHERE id = 2 ;
-- Update with calculation
UPDATE users SET age = age + 1 WHERE id = 3 ;
-- Update all rows (careful!)
UPDATE users SET updated_at = NOW ();
-- Update with RETURNING
UPDATE users SET age = 26 WHERE id = 1 RETURNING * ;
DELETE
FROM
users
WHERE
id
=
1
RETURNING
*
;
-- Truncate (faster, resets sequences)
TRUNCATE TABLE users;
-- LEFT JOIN (all from left table)
SELECT u . name , p . title
FROM users u
LEFT JOIN posts p ON u . id = p . user_id ;
-- RIGHT JOIN (all from right table)
SELECT u . name , p . title
FROM users u
RIGHT JOIN posts p ON u . id = p . user_id ;
-- FULL OUTER JOIN (all from both)
SELECT u . name , p . title
FROM users u
FULL OUTER JOIN posts p ON u . id = p . user_id ;
-- Multiple joins
SELECT u . name , p . title , c . content
FROM users u
JOIN posts p ON u . id = p . user_id
JOIN comments c ON p . id = c . post_id ;
INTEGER
REFERENCES
users(id)
ON DELETE CASCADE
);
-- UNIQUE
CREATE TABLE users (
email VARCHAR ( 255 ) UNIQUE
);
-- CHECK
CREATE TABLE users (
age INTEGER CHECK (age >= 18 )
);
-- NOT NULL
CREATE TABLE users (
email VARCHAR ( 255 ) NOT NULL
);
-- DEFAULT
CREATE TABLE users (
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
active BOOLEAN DEFAULT TRUE
);
-- Add constraint to existing table
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT age_check CHECK (age >= 18 );
CREATE INDEX idx_active_users ON users(email) WHERE active = TRUE;
-- Drop index
DROP INDEX idx_users_email;
-- List indexes
\di
100
WHERE
id
=
2
;
-- Save changes
COMMIT ;
-- Or undo changes
ROLLBACK ;
-- Savepoint (partial rollback)
BEGIN ;
UPDATE users SET name = 'Test' WHERE id = 1 ;
SAVEPOINT my_savepoint;
UPDATE users SET age = 99 WHERE id = 1 ;
ROLLBACK TO my_savepoint; -- Undo age update only
COMMIT ;
VALUES
( '{"name": "Laptop", "price": 1000, "brand": "Dell"}' );
-- Query JSON field
SELECT data->> 'name' as name FROM products;
-- Nested JSON
SELECT data-> 'specs' ->> 'cpu' as cpu FROM products;
-- Where clause
SELECT * FROM products WHERE data->> 'brand' = 'Dell' ;
-- Check if key exists
SELECT * FROM products WHERE data ? 'warranty' ;
-- Contains (@>)
SELECT * FROM products WHERE data @ > '{"brand": "Dell"}' ;
-- Update JSON
UPDATE products
SET data = data || '{"warranty": "2 years"}'
WHERE id = 1 ;
-- Remove key
UPDATE products
SET data = data - 'old_field'
WHERE id = 1 ;
-- Create index on JSON field
CREATE INDEX idx_products_brand ON products(( data->> 'brand' ));
MATERIALIZED VIEW user_stats
AS
SELECT age, COUNT ( * ) as count
FROM users
GROUP BY age;
-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_stats;
-- Drop view
DROP VIEW active_users;
plpgsql;
-- Use function
SELECT get_user_count();
-- Function with parameters
CREATE FUNCTION get_users_by_age (min_age INTEGER )
RETURNS TABLE (id INTEGER , name VARCHAR ) AS $$
BEGIN
RETURN QUERY
SELECT users . id , users . name FROM users WHERE age >= min_age;
END ;
$$ LANGUAGE plpgsql;
-- Use function
SELECT * FROM get_users_by_age( 25 );
-- Drop function
DROP FUNCTION get_user_count();
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- Drop trigger
DROP TRIGGER update_users_updated_at ON users;
id
=
1
;
-- Query active only
SELECT * FROM users WHERE deleted_at IS NULL ;
TRIGGER
AS
$$
BEGIN
NEW . updated_at = NOW ();
RETURN NEW;
END ;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
# Restore from compressed
gunzip < backup.sql.gz | psql -U postgres mydb
# Backup all databases
pg_dumpall -U postgres > all_dbs.sql
myuser;
-- Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;
-- Change password
ALTER USER myuser WITH PASSWORD 'newpassword' ;
-- Drop user
DROP USER myuser;
,
port: 5432 ,
});
// Query
const result = await pool. query ( 'SELECT * FROM users WHERE id = $1' , [ 1 ]);
console. log (result.rows);
@unique
name String ?
posts Post []
}
model Post {
id Int @id @default ( autoincrement ())
title String
author User @relation ( fields : [ authorId ], references : [ id ])
authorId Int
}
' '
,
'World'
)
-- 'Hello World'
SUBSTRING ( 'hello' FROM 1 FOR 2 ) -- 'he'
TRIM ( ' hello ' ) -- 'hello'
-- Date functions
NOW () -- Current timestamp
CURRENT_DATE -- Current date
CURRENT_TIME -- Current time
AGE( timestamp '2000-01-01' ) -- Age from date
EXTRACT( YEAR FROM NOW ()) -- Extract year
-- Math functions
ABS ( - 5 ) -- 5
ROUND ( 3 . 14159 , 2 ) -- 3.14
CEIL( 3 . 1 ) -- 4
FLOOR ( 3 . 9 ) -- 3
RANDOM() -- Random 0-1
-- Aggregate functions
COUNT ( * )
SUM (column)
AVG (column)
MAX (column)
MIN (column)
STRING_AGG (column, ',' ) -- Concatenate rows
- "5432:5432"
volumes :
- postgres_data:/var/lib/postgresql/data
volumes :
postgres_data :
