PostgreSQL/Cheat sheet

Contents

Check out the other cheat sheets!

Do you prefer building with Prisma ORM? Check out Prisma ORM cheat sheet for PostgreSQL!

Your one-stop quick reference for mastering PostgreSQL — from setup and SQL essentials to performance tuning and admin magic! 🐘

---

⚙️ Setup & Connection

Install (Linux)

sudo apt install postgresql postgresql-contrib

Start & Enable Service

sudo systemctl start postgresql
sudo systemctl enable postgresql

Switch to Postgres User

sudo -i -u postgres

Enter the psql Shell

psql

Connect to a Database

psql -U username -d dbname -h localhost -p 5432

Exit psql

\q

---

🗃️ Database Management

---

👤 Users & Roles

Role-Based Access

GRANT SELECT, INSERT ON table TO role;
REVOKE UPDATE ON table FROM role;

---

🧱 Tables

Example

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

---

💾 Data Manipulation (CRUD)

Insert

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

Select

SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;

Update

UPDATE users SET email = 'new@email.com' WHERE id = 1;

Delete

DELETE FROM users WHERE id = 1;

Returning Clause

INSERT INTO users (name) VALUES ('Bob') RETURNING id;

---

🔍 Filtering & Sorting

SELECT * FROM products WHERE price > 50;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM orders ORDER BY created_at DESC;
SELECT * FROM employees WHERE department IN ('HR', 'IT');
SELECT * FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2025-01-01';

---

🔗 Joins

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

---

🧮 Aggregations

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

HAVING Clause

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

---

🪄 Constraints

Example:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  total NUMERIC CHECK (total >= 0)
);

---

🧩 Indexes

CREATE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email;

---

🧠 JSON & Arrays

JSON

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO profiles (data) VALUES ('{"name":"Alice","age":25}');
SELECT data->>'name' FROM profiles;

Arrays

CREATE TABLE tags (
  id SERIAL,
  keywords TEXT[]
);

INSERT INTO tags (keywords) VALUES (ARRAY['tech','postgres']);
SELECT * FROM tags WHERE 'tech' = ANY(keywords);

---

🧰 Views

CREATE VIEW active_users AS
SELECT name, email FROM users WHERE active = true;

SELECT * FROM active_users;
DROP VIEW active_users;

---

🔄 Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or
ROLLBACK;

Atomic, safe, and drama-free.

---

🧩 Extensions

Enable:

CREATE EXTENSION IF NOT EXISTS postgis;

---

📈 Window Functions

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC)
FROM employees;

---

🧮 Common Table Expressions (CTEs)

WITH sales AS (
  SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT COUNT(*) FROM sales;

Recursive example:

WITH RECURSIVE countdown(n) AS (
  SELECT 5
  UNION ALL
  SELECT n-1 FROM countdown WHERE n > 1
)
SELECT * FROM countdown;

---

🧾 Backup & Restore

Dump a Database

pg_dump dbname > backup.sql

Restore

psql dbname < backup.sql

Compressed Dump

pg_dump -Fc dbname > backup.dump
pg_restore -d dbname backup.dump

---

🧠 Performance Tuning

Analyze Query Performance

EXPLAIN ANALYZE SELECT * FROM big_table WHERE id=42;

Vacuum & Analyze

VACUUM;
ANALYZE;

---

🔒 Security

• Role-based access control
• SSL/TLS connections
• Row-Level Security (RLS)

Example

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_policy
ON users FOR SELECT
USING (id = current_user_id());

---

🧰 psql Shortcuts

---

🧭 Troubleshooting

---

🧩 TL;DR Mind Map