SQL Cheat Sheet

PostgreSQL syntax

SELECT [DISTINCT] column_name
  FROM table_name
  WHERE condition
  GROUP BY grouping_element
  HAVING condition
  ORDER BY expression [ ASC | DESC ]
  LIMIT count
  OFFSET start;

INSERT INTO table_name
    VALUES (value [, ...]);
INSERT INTO table_name (column_name [, ...])
  VALUES (value [, ...]), (value [, ...]);

UPDATE table_name SET column_name = value [, ...] WHERE condition;

DELETE FROM table_name WHERE condition [ RETURNING * ];

Aggregate Functions

array_agg(... [ORDER BY input_sort_columns])
avg(...)
count(...)
json_agg(... [ORDER BY input_sort_columns])
max(...)
min(...)
string_agg(..., delimiter [ORDER BY input_sort_columns])
sum(...)

Case

CASE WHEN condition THEN result
  [WHEN ...]
  [ELSE result]
END

Combining Queries

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

Duplicate rows are eliminated unless ALL is used.

CTE

WITH
  cte_name AS (
    -- SELECT statement
  ),
  another_cte AS (
    -- SELECT statement
  )
-- SELECT statement

NULL Handling

COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null.

NULLIF(value1, value2)

The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1.

GREATEST(value [, ...])
LEAST(value [, ...])

Strings

text || text concatenates the two strings
char_length (text) returns number of chars in the string
concat(val1, val2, ...) concatenates strings, ignoring NULLs

Window Function

Used like a column expression:

function_name() OVER (
  [PARTITION BY column1, column2, ...]
  ORDER BY columnA, columnB, ...
)

function_name():

row_number(): Returns the number of the current row within its partition, counting from 1.
rank(): Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.
dense_rank(): Returns the rank of the current row, without gaps; this function effectively counts peer groups.

stefanthoss/sql-cheat-sheet.md