PostgreSQL Cheat Sheet

# Login
psql -U username -d database
 
# List databases
\l
 
# Connect to database
\c database_name
 
# List tables
\dt
 
# Describe table
\d

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

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

-- Select all
SELECT * FROM users;
 
-- Select specific columns
SELECT name, email FROM users;
 
-- WHERE clause

-- Update single row
UPDATE users SET name = 'Galih Pratama' WHERE id = 1;
 
-- Update multiple columns
UPDATE users
SET name = 'Budi', age = 31, updated_at =

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

-- INNER JOIN (matching rows only)
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;
 

-- PRIMARY KEY
CREATE TABLE users (
 id SERIAL PRIMARY KEY
);
 
-- FOREIGN KEY
CREATE TABLE posts (
 id SERIAL PRIMARY KEY,
 user_id 

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

-- Start transaction
BEGIN;
 
-- Run queries
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance +

-- Create table with JSONB
CREATE TABLE products (
 id SERIAL PRIMARY KEY,
 data JSONB
);
 
-- Insert JSON
INSERT INTO products (data) 

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

-- Simple function
CREATE FUNCTION get_user_count() RETURNS INTEGER AS $$
BEGIN
 RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE

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

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

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

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

const { Pool } = require('pg');
 
const pool = new Pool({
 user: 'postgres',
 host: 'localhost',
 database: 'mydb',
 password: 'password'

datasource 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 } });

-- String functions
LENGTH('hello')        -- 5
UPPER('hello')         -- 'HELLO'
LOWER('HELLO')         -- 'hello'
CONCAT('Hello', 

# 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