PostgreSQL 15 · 13 categories · 77 entries

SQL Reference Sheet

A complete PostgreSQL syntax reference covering all essential functions, clauses, and operators. Bookmark for quick lookups while writing queries, or print the one-pager for your desk.

Print cheatsheetPractice SQL →
BasicsFilteringAggregationJoinsSubqueriesCTEsWindow FunctionsString FunctionsDate & TimeType CastingConditionalsMathSet Operations
Basics

SELECT, FROM, WHERE, ORDER BY, LIMIT, DISTINCT, aliases

7
SELECT col1, col2
FROM table_name
WHERE condition
ORDER BY col ASC|DESC
LIMIT n / OFFSET n
DISTINCT
col AS alias
Filtering

AND, OR, IN, BETWEEN, LIKE, IS NULL, EXISTS

6
AND / OR / NOT
IN (val1, val2, …)
BETWEEN a AND b
LIKE 'J%' / ILIKE
IS NULL / IS NOT NULL
EXISTS (subquery)
Aggregation

COUNT, SUM, AVG, GROUP BY, HAVING, FILTER

7
COUNT(*) / COUNT(col)
COUNT(DISTINCT col)
SUM(col) / AVG(col)
MIN(col) / MAX(col)
GROUP BY col
HAVING condition
FILTER (WHERE cond)
Joins

INNER, LEFT, RIGHT, FULL OUTER, CROSS, USING

6
INNER JOIN t ON a.id = b.id
LEFT JOIN t ON …
RIGHT JOIN t ON …
FULL OUTER JOIN t ON …
CROSS JOIN t
USING (col)
Subqueries

Scalar, correlated, IN, EXISTS, derived tables

5
WHERE col IN (SELECT …)
WHERE col = (SELECT …)
WHERE EXISTS (SELECT 1 …)
FROM (SELECT …) AS t
Correlated subquery
CTEs

WITH, chained CTEs, recursive CTE

3
WITH cte AS (SELECT …)
WITH a AS (…), b AS (…)
WITH RECURSIVE cte AS (…)
Window Functions

OVER, PARTITION BY, ROW_NUMBER, RANK, LAG, LEAD, NTILE

9
func() OVER ()
OVER (PARTITION BY col)
OVER (ORDER BY col)
ROW_NUMBER()
RANK() / DENSE_RANK()
NTILE(n)
LAG(col,n) / LEAD(col,n)
SUM(col) OVER (…)
ROWS BETWEEN … AND …
String Functions

UPPER, LENGTH, TRIM, SUBSTRING, CONCAT, REPLACE

9
UPPER(col) / LOWER(col)
LENGTH(col)
TRIM(col)
SUBSTRING(col, start, len)
LEFT(col,n) / RIGHT(col,n)
CONCAT(a,b) or a || b
REPLACE(col, old, new)
SPLIT_PART(col, delim, n)
INITCAP(col)
Date & Time

NOW, DATE_TRUNC, EXTRACT, AGE, intervals, TO_CHAR

8
NOW()
CURRENT_DATE
DATE_TRUNC('month', col)
EXTRACT(part FROM col)
AGE(end, start)
col + INTERVAL '3 days'
TO_CHAR(col, 'YYYY-MM')
col::date
Type Casting

CAST, :: shorthand, numeric precision

5
CAST(col AS integer)
col::integer / ::text
col::numeric(10,2)
col::boolean
col::date / ::timestamp
Conditionals

CASE WHEN, COALESCE, NULLIF

3
CASE WHEN … THEN … END
COALESCE(a, b, c)
NULLIF(a, b)
Math

Arithmetic, ROUND, FLOOR, ABS, POWER

5
+ - * / %
ROUND(col, n)
FLOOR(col) / CEIL(col)
ABS(col)
POWER(base, exp) / SQRT(col)
Set Operations

UNION, UNION ALL, INTERSECT, EXCEPT

4
UNION
UNION ALL
INTERSECT
EXCEPT

Ready to practice?

Apply these functions in the live playground against real PostgreSQL sandboxes. The schema explorer keeps this reference right beside your editor.

Open playground →Printable version