PostgreSQL Queries Cheatsheet
Quick reference for PostgreSQL queries: SELECT, INSERT, UPDATE, DELETE, JOINs, aggregate functions, filtering, sorting, and subqueries.
| Query | Description | Example | Category |
|---|---|---|---|
| SELECT | Select columns from table | SELECT id, name FROM users; | Basic |
| SELECT * | Select all columns | SELECT * FROM users; | Basic |
| DISTINCT | Get unique values | SELECT DISTINCT country FROM users; | Basic |
| INSERT | Insert a row | INSERT INTO users(name, age) VALUES('Alice', 25); | Insert / Update / Delete |
| UPDATE | Update rows | UPDATE users SET age=26 WHERE name='Alice'; | Insert / Update / Delete |
| DELETE | Delete rows | DELETE FROM users WHERE age<18; | Insert / Update / Delete |
| WHERE | Filter rows | SELECT * FROM users WHERE age>18; | Filtering / Sorting |
| ORDER BY | Sort rows | SELECT * FROM users ORDER BY age DESC; | Filtering / Sorting |
| LIMIT / OFFSET | Limit rows returned | SELECT * FROM users LIMIT 10 OFFSET 20; | Filtering / Sorting |
| INNER JOIN | Join tables (common rows) | SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id=o.user_id; | Joins |
| LEFT JOIN | Left join | SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id=o.user_id; | Joins |
| RIGHT JOIN | Right join | SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id=o.user_id; | Joins |
| FULL JOIN | Full outer join | SELECT u.name, o.amount FROM users u FULL JOIN orders o ON u.id=o.user_id; | Joins |
| COUNT() | Count rows | SELECT COUNT(*) FROM users; | Aggregates |
| SUM() | Sum values | SELECT SUM(amount) FROM orders; | Aggregates |
| AVG() | Average value | SELECT AVG(age) FROM users; | Aggregates |
| MAX() / MIN() | Maximum or Minimum | SELECT MAX(age) FROM users; | Aggregates |
| GROUP BY | Group rows | SELECT country, COUNT(*) FROM users GROUP BY country; | Aggregates |
| HAVING | Filter groups | SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*)>5; | Aggregates |
| Subquery | Nested query | SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount>100); | Subqueries |
| EXISTS | Check existence | SELECT name FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id=users.id); | Subqueries |
| ALTER TABLE | Modify table | ALTER TABLE users ADD COLUMN email TEXT; | Others |
| DROP TABLE | Drop table | DROP TABLE users; | Others |
| CREATE INDEX | Create index | CREATE INDEX idx_name ON users(name); | Others |