One page
SQL and PostgreSQL cheat sheet
What to keep at hand. If a line is not clear, go back to the matching lesson in the course.
Open the course: SQL and PostgreSQLReading data
| What | How |
|---|---|
| Select | SELECT id, name FROM users |
| Filter | WHERE status = 'paid' AND total >= 100 |
| Page | ORDER BY created_at DESC LIMIT 20 OFFSET 40 |
| Join | JOIN users u ON u.id = o.user_id |
| Groups | GROUP BY status HAVING COUNT(*) > 10 |
Changing data
| What | How |
|---|---|
| Insert | INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id |
| Update | UPDATE users SET name = $1 WHERE id = $2 |
| Transaction | BEGIN; … COMMIT; |
| Index | CREATE INDEX ON orders (user_id) |
Schema and complex queries
| What | How |
|---|---|
| Constraints | email text NOT NULL UNIQUE |
| Foreign key | user_id bigint REFERENCES users (id) |
| Named step | WITH spent AS (SELECT …) SELECT … FROM spent |
| Query plan | EXPLAIN ANALYZE SELECT … |
| Create or update | ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name |
| Number within a group | ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) |