Syntax & Queries

Update

Returning data after updating

Return the updated data with:

UPDATE table_name
SET ...
WHERE ...
RETURNING *
;

Insert

on conflict

Specify conflict keys on insert, and when data already exists, choose what to do.

INSERT INTO table_name (col1)
VALUES (...)
ON CONFLICT (col1)
DO NOTHING
;

INSERT INTO table_name (col1)
VALUES (...)
ON CONFLICT (col1)
DO
    UPDATE SET
        col2 = ...
        col3 = ...
;

Joins

using

Use USING instead of ON when the joining column name is the same in them.

SELECT
    table1.id,
    table2.id
FROM table1
JOIN table2 USING (id)
;

cross

In a CROSS JOIN query, the result (also known as Cartesian product) lines up each row in the left table with each row in the right table to present all possible combinations of rows.

If table1 has n rows and table2 has m rows, the result will have n * m rows.

SELECT
    *
FROM table1
CROSS JOIN table2
;

natural

A NATURAL JOIN is a join that creates an implicit join based on the same column names in the joined tables.

If not specified it will use INNER.

-- NATURAL [INNER, LEFT, RIGHT] JOIN table_name

SELECT
    *
FROM table1
NATURAL JOIN table2
;

Sorting

order by

Order null values along with regular data. NULLS FIRST will place NULL values before other values.

SELECT * FROM table_name
ORDER BY
    -- col [ASC | DESC] [NULLS FIRST | NULLS LAST]
    col ASC NULLS FIRST
;

Aggregates

group by

Groups rows by columns.

Can only directly access the columns used to group the rows.

Any other column can only be accessed with an aggregate functions.

rollup

ROLLUP will give summarize rows for the given aggregate function, (e.g. AVG). So it will return the averages aggregated by col_name in each row, but it will also give a new row with the average of all the groups.

Providing more than one column in ROLLUP will also produce this new total averages by the aggregation of the two columns and for aggregating for one column.

SELECT AVG(col2) FROM table_name GROUP BY ROLLUP(col_name);

cube

CUBE allows you to generate combinations of multiple grouping sets.

SELECT AVG(col) FROM table_name GROUP BY CUBE (col1, col2, col3);

/*
    (col1, col2, col3)
    (col1, col2)
    (col1, col3)
    (col2, col3)
    (col1)
    (col2)
    (col3)
    ()
*/

grouping sets

GROUPING SETS is similar to CUBE but you specify the groups yourself, instead of letting postgres make a combination of them.

SELECT AVG(col) FROM table_name GROUP BY
    GROUPING SETS (
        (), -- The aggregation of all
        col1,
        col2
    );

filter

You can use FILTER (WHERE ...) to filter data specifically to each aggregate function.

SELECT
    AVG(col1) FILTER (WHERE col3 < 5),
    AVG(col1) FILTER (WHERE col5 = 'value')
FROM table_name GROUP BY ROLLUP(col_name);

count

When using Count to count a particular column, have in mind that null values of this column are not counted.

Use Count(*) then, to count the rows.

having

Filter a set of groups.

Calculates on aggregate function or grouped columns only, other columns.

Windowing functions

Window functions allow you to run aggregate functions without grouping the data.

Specifying PARTITION BY works like creating aggregations like GROUP BY.

Window functions are powerful as it lets you aggregate data in different ways in the same query.

If no PARTITION is specified, it will consider the hole group.

over

-- Will do average over everyone since not partition was given
SELECT
    col1,
    AVG(col2) OVER()
FROM table_name;

-- Will partition data differently for each result column
SELECT
    SUM(col1) OVER(PARTITION BY col1),
    AVG(col1) OVER(PARTITION BY col2)
FROM table_name;

-- Will partition only filtered data
SELECT
    SUM(col1) OVER(PARTITION BY col1 = 'value')
FROM table_name;

-- Will partition with order
SELECT
    SUM(col1) OVER(PARTITION BY col1 ORDER BY col3)
FROM table_name;

You can create sliding dynamic windows for the data by specifying window frames.

Window frames are used to indicate how many rows around the current view, the window function should include.

-- Dynamic sliding windows of 3 rows (previous row, current, next row)
SELECT
    SUM(col1) OVER(
        PARTITION BY col1
        ORDER BY col3
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM table_name;

-- Dynamic sliding windows of 2 rows (previous row, next row) (Current row excluded)
SELECT
    SUM(col1) OVER(
        PARTITION BY col1
        ORDER BY col3
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
        EXCLUDE CURRENT ROW
    )
FROM table_name;

Utility functions

greatest

Pick the highest out of a list of values.

SELECT GREATEST(col1, col2, x, ...)

least

Pick the lowest out of a list of values.

SELECT LEAST(col1, col2, x, ...)

case

To use ifs in a select statement.

If ELSE is not used, a null value will be returned if none of the conditions were met.

SELECT
  col1,
  CASE
    WHEN col2 > x THEN 'value1'
    WHEN col2 < x THEN 'value2'
    ELSE 'values3'
  END
  ...

split_part()

Splits the string value by a delimiter and returns the desired position.

SPLIT_PART(string, delimiter, position);

SELECT SPLIT_PART('2025-01-01', '-', 1); -- 2025

trim()

If not specified, the default character is space.

TRIM removes the longest string that contains a specific character from a string.

LTRIM removes all characters from the beginning of a string.

RTRIM removes all characters from the end of a string.

BRIM is the combination of LTRIM and RTRIM.

TRIM([LEADING | TRAILING | BOTH] [characters] FROM string);
LTRIM(string, [characters])
RTRIM(string, [characters])
BRIM(string, [characters])

SELECT TRIM(LEADING '0' FROM '000123') -- '123'
SELECT LTRIM('___text', '-') -- 'text'
SELECT RTRIM('___text___', '-') -- '___text'
SELECT BRIM('___text___', '-') -- 'text'

pad()

If not specified, the default character is space.

LPAD pads a string on the left to a specified length with a sequence of characters.

RPAD pads a string on the right to a specified length with a sequence of characters.

LPAD(string, length[, fill]);
RPAD(string, length[, fill]);

SELECT LPAD('value', 15, '*') -- '**********value'
SELECT RPAD('123', 5, '0') -- '12300'

repeat()

Repeats a string a spefied number of times.

REPEAT(string, number);

SELECT REPEAT('a', 5) -- 'aaaaa'

Operators

concatenation

Use ||, CONCAT or CONCAT_WS to concatenate strings like:

SELECT col1 || ' ' || col2

SELECT CONCAT(col1, ' ', col2)

CONCAT_WS will handle not putting the "separator" if there is only one value or none.

And it also ignores NULL values to be concatenated.

SELECT CONCAT_WS(' ', col1, col2)

inclusion

@>, <@ and && are some inclusion operators, that usually check if something is in the other thing, or if something overlaps with another thing, etc.

SELECT
    ARRAY[1,2,3,4] @> ARRAY[2,3,4] -- true (Contains)
    ARRAY['a','b'] <@ ARRAY['a', 'b'] -- true (is contained by)
    ARRAY[1,2,3,4] && ARRAY[2,3,4] -- true (they overlaps)
    
    -- Does the date range contains the following date?
    DATERANGE(CURRENT_DATE, CURRENT_DATE + 30) @> CURRENT_DATE + 15 -- true (Contains)
    
    -- Does range 1..4 overlaps with 0..3?
    NUMRANGE(1,5) && NUMRANGE(0,4) -- true (they overlap)

PreviousData Types NextSequences

Last updated 3 days ago