PostgreSQL Cheatsheet — Complete SQL Commands, Joins, Functions & Administration Guide

PostgreSQL is a powerful open-source relational database management system (RDBMS) that emphasizes data integrity, extensibility, and standards compliance. It supports advanced SQL features, JSON data, and custom functions, making it a favorite among developers for scalable and secure applications.

This PostgreSQL Cheatsheet provides a concise reference of essential commands, SQL syntax, and administrative operations.

Basic PostgreSQL Commands

Command	Description
`psql -U username -d dbname`	Connect to a database
`\l`	List all databases
`\c database_name`	Connect to a specific database
`\dt`	List all tables
`\d table_name`	Describe a table
`\q`	Quit psql shell

Database and Table Management

Create and Drop Database

CREATE DATABASE mydb;
DROP DATABASE mydb;

Create and Drop Table

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(30)
);

DROP TABLE employees;

Alter Table

ALTER TABLE employees ADD COLUMN salary NUMERIC(10,2);
ALTER TABLE employees RENAME COLUMN name TO full_name;
ALTER TABLE employees DROP COLUMN department;

Insert, Update, Delete, and Select Data

Insert Data

INSERT INTO employees (full_name, age, salary)
VALUES ('Alice Brown', 28, 60000);

Select Data

SELECT * FROM employees;
SELECT full_name, salary FROM employees WHERE age > 25;
SELECT DISTINCT department FROM employees;

Update Data

UPDATE employees SET salary = salary + 5000 WHERE age > 30;

Delete Data

DELETE FROM employees WHERE salary < 40000;

Filtering and Sorting Data

SELECT * FROM employees WHERE department = 'HR' ORDER BY salary DESC;
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
SELECT * FROM employees WHERE full_name LIKE 'A%';

PostgreSQL Joins Explained

Join Type	Description	Example
INNER JOIN	Returns only matching rows	`SELECT * FROM a INNER JOIN b ON a.id = b.a_id;`
LEFT JOIN	All rows from left table + matching from right	`SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;`
RIGHT JOIN	All rows from right table + matching from left	`SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;`
FULL OUTER JOIN	All rows when there is a match in either table	`SELECT * FROM a FULL JOIN b ON a.id = b.a_id;`

Aggregate Functions

Function	Description	Example
`COUNT()`	Count number of rows	`SELECT COUNT(*) FROM employees;`
`SUM()`	Calculate total	`SELECT SUM(salary) FROM employees;`
`AVG()`	Average value	`SELECT AVG(salary) FROM employees;`
`MAX()`	Highest value	`SELECT MAX(age) FROM employees;`
`MIN()`	Lowest value	`SELECT MIN(age) FROM employees;`

GROUP BY and HAVING Clauses

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

Constraints and Keys

Constraint	Description
`PRIMARY KEY`	Uniquely identifies each row
`FOREIGN KEY`	References another table
`UNIQUE`	Ensures all values are different
`NOT NULL`	Field cannot be NULL
`CHECK`	Ensures valid data values
`DEFAULT`	Sets default value

postgresql cheatsheet, postgresql tutorial for beginners, postgresql commands list, postgresql sql examples, postgresql create database, postgresql joins, postgresql functions, postgresql vs mysql, postgresql select query, postgresql administration

PostgreSQL Functions and Expressions

SELECT NOW();               -- Current timestamp  
SELECT AGE('2025-10-28');   -- Age calculation  
SELECT UPPER('postgres');   -- Convert to uppercase  
SELECT ROUND(45.678, 1);    -- Round to 1 decimal

Transactions and Rollback

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Rollback in case of an error:

ROLLBACK;

User and Privilege Management

CREATE USER john WITH PASSWORD 'securePass';
GRANT ALL PRIVILEGES ON DATABASE mydb TO john;
REVOKE INSERT ON TABLE employees FROM john;
DROP USER john;

Indexing for Performance

CREATE INDEX idx_employee_name ON employees(full_name);
DROP INDEX idx_employee_name;

Backup and Restore PostgreSQL Database

Backup:

pg_dump mydb > mydb_backup.sql

Restore:

psql mydb < mydb_backup.sql

PostgreSQL JSON Functions

PostgreSQL fully supports JSON and JSONB.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  details JSONB
);

INSERT INTO products (details)
VALUES ('{"name": "Laptop", "brand": "Dell", "price": 800}');

Query JSON Data:

SELECT details->>'name' AS product_name FROM products;

PostgreSQL Performance Tips

Use EXPLAIN ANALYZE to inspect query performance.
Avoid unnecessary joins with CTEs (Common Table Expressions).
Always index columns used in WHERE or JOIN.
Use VACUUM ANALYZE to reclaim space and optimize statistics.

FAQ — PostgreSQL Cheatsheet

Q1: What makes PostgreSQL different from MySQL?
PostgreSQL supports complex queries, JSON data, and ACID compliance, making it ideal for enterprise applications.

Q2: Is PostgreSQL open source?
Yes, PostgreSQL is completely free and open source under the PostgreSQL License.

Q3: Can I use PostgreSQL for web development?
Yes, it’s widely used with frameworks like Django, Laravel, and Node.js.

Q4: What port does PostgreSQL use by default?
PostgreSQL uses port 5432 by default.

Q5: How can I connect PostgreSQL to PHP or Python?
Use pg_connect() in PHP or psycopg2 in Python to establish a database connection.