PostgreSQL Cheatsheet
17 essential methods for coding interviews
Showing 17 of 17 methods
| Method | Syntax | Description | Time | Priority |
|---|---|---|---|---|
SELECT | SELECT column1, column2 FROM table WHERE condition | Retrieve data from table with optional filtering | O(n) | essential |
DISTINCT | SELECT DISTINCT column FROM table | Return unique values, removing duplicates | O(n) | essential |
ORDER BY | SELECT * FROM table ORDER BY column1 ASC, column2 DESC | Sort results by one or more columns | O(n log n) | essential |
LIMIT / OFFSET | SELECT * FROM table LIMIT n OFFSET m | Limit number of rows returned, skip first m rows | O(n) | essential |
GROUP BY | SELECT column, aggregate(column) FROM table GROUP BY column | Group rows by column values and apply aggregate functions | O(n) | essential |
HAVING | SELECT ... GROUP BY ... HAVING condition | Filter groups after aggregation (WHERE filters before) | O(n) | essential |
COUNT, SUM, AVG, MIN, MAX | SELECT COUNT(*), SUM(column), AVG(column), MIN(column), MAX(column) FROM table | Aggregate functions for counting, summing, averaging, finding min/max | O(n) | essential |
INNER JOIN | SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id | Return rows where join condition matches in both tables | O(n * m) | essential |
LEFT JOIN | SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id | Return all rows from left table, NULLs for non-matching right table rows | O(n * m) | essential |
INSERT | INSERT INTO table (col1, col2) VALUES (val1, val2) | Insert new row(s) into table | O(1) per row | essential |
UPDATE | UPDATE table SET column = value WHERE condition | Update existing rows matching condition | O(n) | essential |
DELETE | DELETE FROM table WHERE condition | Delete rows matching condition | O(n) | essential |
RIGHT JOIN | SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id | Return all rows from right table, NULLs for non-matching left table rows | O(n * m) | common |
Window Functions | SELECT column, aggregate() OVER (PARTITION BY col ORDER BY col) FROM table | Perform calculations across set of rows related to current row | O(n log n) | common |
CTE (WITH) | WITH cte AS (SELECT ...) SELECT * FROM cte | Common Table Expression - temporary named result set | O(n) | common |
EXISTS | SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE condition) | Check if subquery returns any rows | O(n * m) | common |
UNION | SELECT ... UNION SELECT ... | Combine results of multiple SELECT statements, removing duplicates | O(n log n) | useful |