PostgreSQL
A comprehensive PostgreSQL cheatsheet
1. PostgreSQL Basics
What is PostgreSQL?
- Open‑source object‑relational database (ORDBMS)
- ACID compliant
- Supports JSON, arrays, full‑text search
- Used in production at scale (Instagram, Reddit, Uber)
2. Installation & Service Control
Linux (Ubuntu/Debian)
sudo apt update sudo apt install postgresql postgresql-contrib
Start / Stop / Status
sudo systemctl start postgresql # Start service sudo systemctl stop postgresql # Stop service sudo systemctl restart postgresql # Restart service sudo systemctl status postgresql # Check status
Check Version
psql --version
3. PostgreSQL Login & Shell
Switch to postgres user
sudo -i -u postgres
Open PostgreSQL shell
psql
Login with user & database
psql -U username -d dbname
Exit psql
\\q
4. Database Management
List Databases
\\l
Create Database
CREATE DATABASE mydb;
Delete Database
DROP DATABASE mydb;
Connect to Database
\\c mydb
5. User & Role Management
Create User
CREATE USER bablu WITH PASSWORD 'password123';
Create Role
CREATE ROLE dev_role;
Grant Privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO bablu;
Make Superuser
ALTER USER bablu WITH SUPERUSER;
List Users
\\du
6. Tables & Schema
Create Table
CREATE TABLE students ( id SERIAL PRIMARY KEY, name VARCHAR(100), age INT, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
View Tables
\\dt
Table Structure
\\d students
Drop Table
DROP TABLE students;
7. CRUD Operations (DML)
Insert Data
INSERT INTO students (name, age, email) VALUES ('Bablu', 22, 'bablu@mail.com');
Select Data
SELECT * FROM students;
Select with Condition
SELECT name, age FROM students WHERE age > 20;
Update Data
UPDATE students SET age = 23 WHERE name = 'Bablu';
Delete Data
DELETE FROM students WHERE id = 1;
8. Constraints
PRIMARY KEY FOREIGN KEY UNIQUE NOT NULL CHECK DEFAULT
Example
CREATE TABLE orders ( id SERIAL PRIMARY KEY, price INT CHECK (price > 0), user_id INT REFERENCES users(id) );
9. Joins
INNER JOIN
SELECT a.name, b.course FROM students a INNER JOIN courses b ON a.id = b.student_id;
LEFT JOIN
SELECT * FROM students s LEFT JOIN courses c ON s.id = c.student_id;
RIGHT JOIN
SELECT * FROM students s RIGHT JOIN courses c ON s.id = c.student_id;
10. Indexes (Performance)
Create Index
CREATE INDEX idx_students_email ON students(email);
List Indexes
\\di
Drop Index
DROP INDEX idx_students_email;
11. Aggregate Functions
COUNT() SUM() AVG() MIN() MAX()
Example
SELECT COUNT(*) FROM students;
12. Group By & Having
SELECT age, COUNT(*) FROM students GROUP BY age;
SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 1;
13. Views
Create View
CREATE VIEW adult_students AS SELECT name, age FROM students WHERE age >= 18;
Use View
SELECT * FROM adult_students;
14. Transactions
BEGIN; INSERT INTO students VALUES (...); COMMIT;
Rollback
ROLLBACK;
15. Functions & Procedures
Function Example
CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;
Call Function
SELECT add_numbers(10, 20);
16. Triggers
CREATE TRIGGER log_update AFTER UPDATE ON students FOR EACH ROW EXECUTE FUNCTION log_changes();
17. JSON & JSONB
Insert JSON
INSERT INTO users (data) VALUES ('{"name": "Bablu", "skill": "Cyber"}');
Query JSON
SELECT data->>'name' FROM users;
18. Backup & Restore
Backup
pg_dump mydb > backup.sql
Restore
psql mydb < backup.sql
19. Permissions
GRANT SELECT ON students TO bablu; REVOKE INSERT ON students FROM bablu;
20. Performance & Debugging
Explain Query
EXPLAIN ANALYZE SELECT * FROM students WHERE email='a@mail.com';
21. Useful Meta Commands
\\l -- list databases \\dt -- list tables \\du -- list users \\d -- describe table \\q -- quit
22. PostgreSQL Interview One‑Liners
- PostgreSQL is ACID compliant
- JSONB is faster than JSON
- Index improves SELECT, slows INSERT
- WAL ensures durability
23. PostgreSQL Use Cases
- Web apps (Django, Node, Spring)
- Data analytics
- Cyber security logs
- Fintech systems