PostgreSQL Cheat Sheet

# 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

-- Create database
CREATE DATABASE mydb;
 
-- Drop database
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;
 
-- Rename database
ALTER DATABASE mydb RENAME TO newdb;

-- Create table
CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(255) 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);

-- 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', '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 all
SELECT * FROM users;
 
-- Select specific columns
SELECT name, email FROM users;
 
-- WHERE clause
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;

-- Update single row
UPDATE users SET name = 'Galih Pratama' WHERE id = 1;
 
-- Update multiple columns
UPDATE users
SET name = 'Budi', age = 31, updated_at = 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 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
DELETE FROM users WHERE id = 1 RETURNING *;
 
-- Truncate (faster, resets sequences)
TRUNCATE TABLE users;

-- INNER JOIN (matching rows only)
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;
 
-- 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;

-- PRIMARY KEY
CREATE TABLE users (
 id SERIAL PRIMARY KEY
);
 
-- FOREIGN KEY
CREATE TABLE posts (
 id SERIAL PRIMARY KEY,
 user_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
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)
CREATE INDEX idx_active_users ON users(email) WHERE active = TRUE;
 
-- Drop index
DROP INDEX idx_users_email;
 
-- List indexes
\di

-- Start transaction
BEGIN;
 
-- Run queries
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 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;

-- Create table with JSONB
CREATE TABLE products (
 id SERIAL PRIMARY KEY,
 data JSONB
);
 
-- Insert JSON
INSERT INTO products (data) 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'));

-- 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 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;

-- Simple function
CREATE FUNCTION get_user_count() RETURNS INTEGER AS $$
BEGIN
 RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE 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();

-- 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
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;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
CREATE TABLE users (
 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
 name VARCHAR(100)
);

CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 name VARCHAR(100),
 deleted_at TIMESTAMP NULL
);
 
-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 1;
 
-- Query active only
SELECT * FROM users WHERE deleted_at IS NULL;

CREATE TABLE posts (
 id SERIAL PRIMARY KEY,
 title VARCHAR(200),
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS 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();

-- 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;

-- Show query plan
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
 
-- Show actual execution
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- Clean up dead rows
VACUUM users;
 
-- Full vacuum (more thorough)
VACUUM FULL users;
 
-- Analyze (update statistics)
ANALYZE users;
 
-- Combined
VACUUM ANALYZE users;

# 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
 
# Restore from compressed
gunzip < backup.sql.gz | psql -U postgres mydb
 
# Backup all databases
pg_dumpall -U postgres > all_dbs.sql

-- 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 myuser;
 
-- Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;
 
-- Change password
ALTER USER myuser WITH PASSWORD 'newpassword';
 
-- Drop user
DROP USER myuser;

const { Pool } = require('pg');
 
const pool = new Pool({
 user: 'postgres',
 host: 'localhost',
 database: 'mydb',
 password: 'password',
 port: 5432,
});
 
// Query
const result = await pool.query('SELECT * FROM users WHERE id = $1', [1]);
console.log(result.rows);

datasource db {
 provider = "postgresql"
 url   = env("DATABASE_URL")
}
 
model User {
 id  Int   @id @default(autoincrement())
 email String @unique
 name String?
 posts Post[]
}
 
model Post {
 id    Int  @id @default(autoincrement())
 title   String
 author  User  @relation(fields: [authorId], references: [id])
 authorId Int
}

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
 
const users = await prisma.user.findMany({ include: { posts: true } });

-- String functions
LENGTH('hello')        -- 5
UPPER('hello')         -- 'HELLO'
LOWER('HELLO')         -- 'hello'
CONCAT('Hello', ' ', '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

# docker-compose.yml
version: '3.8'
services:
 postgres:
  image: postgres:16
  environment:
   POSTGRES_USER: postgres
   POSTGRES_PASSWORD: mypassword
   POSTGRES_DB: mydb
  ports:
   - "5432:5432"
  volumes:
   - postgres_data:/var/lib/postgresql/data
 
volumes:
 postgres_data:

docker-compose up -d
docker exec -it postgres_container psql -U postgres