PostgreSQL - Cheatsheet

PostgreSQL Modern Cheatsheet

This PostgreSQL cheatsheet provides a quick reference to essential commands and modern best practices. PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. This guide will help you use PostgreSQL effectively in modern applications.

.toc_container { width: 100%; clear: both; display: table; } .toc_column { width: 50%; float: left; }
@media (max-width: 600px) { .toc_column { width: 100%; float: none; } .second_child { margin-top: -15px; } }

1. Connecting to PostgreSQL

Use the psql command-line tool. The easiest way to run PostgreSQL for local development is with Docker:

# Start PostgreSQL in Docker
docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

# Connect using psql
psql --host=localhost --username=postgres --dbname=postgres

Check the server version:

SELECT version();

2. Data Definition Language (DDL)

Create Table

Use GENERATED ALWAYS AS IDENTITY for auto-incrementing primary keys (SQL standard, available since Postgres 10). Use modern data types like UUID, TIMESTAMPTZ, and JSONB.

CREATE TABLE employees (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    employee_uuid UUID DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Alter Table

-- Add a column
ALTER TABLE employees ADD COLUMN start_date DATE;

-- Rename a column
ALTER TABLE employees RENAME COLUMN start_date TO hire_date;

-- Drop a column
ALTER TABLE employees DROP COLUMN hire_date;

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT salary_check CHECK (salary > 0);

Drop Table

DROP TABLE IF EXISTS employees;

3. Data Manipulation Language (DML)

Insert Data

-- Insert a single row
INSERT INTO employees(name, email, department, salary)
VALUES('Alice', 'alice@example.com', 'Engineering', 90000);

-- Insert multiple rows
INSERT INTO employees(name, email, department, salary) VALUES
('Bob', 'bob@example.com', 'Engineering', 95000),
('Charlie', 'charlie@example.com', 'Marketing', 85000);

Update Data

UPDATE employees
SET salary = salary * 1.05, department = 'Senior Engineering'
WHERE email = 'bob@example.com';

Delete Data

DELETE FROM employees WHERE email = 'charlie@example.com';

4. Data Query Language (DQL)

SELECT name, salary FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 10;

Aggregate Functions

-- Get statistics for the Engineering department
SELECT
    COUNT(*) AS num_employees,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_payroll
FROM employees
WHERE department = 'Engineering';

Grouping Data

SELECT department, AVG(salary) as avg_dept_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5; -- Filter groups after aggregation

Joins

SELECT e.name, p.project_name
FROM employees AS e
INNER JOIN projects AS p ON e.id = p.employee_id;

Pattern Matching

-- Case-sensitive search
SELECT name FROM employees WHERE name LIKE 'A%';

-- Case-insensitive search (Postgres-specific)
SELECT name FROM employees WHERE name ILIKE 'a%';

5. Advanced Querying

Common Table Expressions (CTEs)

Use CTEs (the WITH clause) to break down complex queries into logical, readable steps.

WITH high_earners AS (
    SELECT id, name, department
    FROM employees
    WHERE salary > 100000
)
SELECT * FROM high_earners WHERE department = 'Engineering';

Window Functions

Perform calculations across a set of table rows that are somehow related to the current row.

-- Rank employees by salary within each department
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

The CASE Statement

SELECT name, salary,
    CASE
        WHEN salary > 100000 THEN 'High'
        WHEN salary > 70000 THEN 'Medium'
        ELSE 'Standard'
    END AS salary_grade
FROM employees;

6. Transactions and Data Integrity

Transactions

Wrap multiple statements in a transaction to ensure they all succeed or fail together (atomicity).

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Or ROLLBACK to undo changes

Indexes

Create indexes to speed up query performance. Foreign keys are often good candidates for indexes.

-- Create an index on a frequently queried column
CREATE INDEX idx_employees_department ON employees(department);

-- Use EXPLAIN to analyze query performance
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';