All commands in PostgreSQL syntax. ```sql 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 ```sql CASE WHEN condition THEN result [WHEN ...] [ELSE result] END ``` ## Casting ```sql CAST(expression AS type) expression::type ``` ## Combining Queries ```sql query1 UNION [ALL] query2 query1 INTERSECT [ALL] query2 query1 EXCEPT [ALL] query2 ``` Duplicate rows are eliminated unless `ALL` is used. ## CTE ```sql WITH cte_name AS ( -- SELECT statement ), another_cte AS ( -- SELECT statement ) -- SELECT statement ``` ## Datetime ```sql EXTRACT(field FROM source) date_trunc(field, source) ``` ## Misc ```sql GREATEST(value [, ...]) LEAST(value [, ...]) SELECT (SELECT ...) / (SELECT ...) AS fraction ``` ## NULL Handling * `COALESCE(value [, ...])` returns the first of its arguments that is not null. Null is returned only if all arguments are null. * `NULLIF(value1, value2)` returns a null value if `value1` equals `value2`; otherwise it returns `value1`. ## Strings * `text || text` concatenates the two strings * `char_length (text)` returns number of chars in the string * `concat(val1, val2, ...)` concatenates strings, ignoring NULLs * `substr(string, start, length)` (first char is `start` = 1) * `replace(string, from, to)` * `repeat(string, number)` repeats `string` the specified number of times ## Window Function Used like a column expression: ```sql 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. * `first_value(value)` * `last_value(value)`