PostgreSQL

A comprehensive PostgreSQL cheatsheet

1. PostgreSQL Basics

What is PostgreSQL?

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

23. PostgreSQL Use Cases