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.

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

posix regular expressions

Provide powerful means for pattern matching compared to LIKE or SIMILAR TO.

Postgresl supports 4 operators for posix regular expressions (also know as tilde operator ~).

~ returns true or false depending on whether or not a regular expression can match a string or a part thereof.

• ~: matches regular expression (case sensitive)

• ~*: matches regular expression (case insensitive)

• !~: does not match regular expression (case sensitive)

• !~*: does not match regular expression (case insensitive)

SELECT
    'same' ~ 'same' -- true
    'same' ~ 'Same' -- false
    'same' ~* 'Same' -- true
    'same' !~ 'Same' -- true

regexp_matches

REGEXP_MATCHES function matches a regular expression against a string and returns matched substrings.

-- REGEXP_MATCHES(source_string, pattern, [, flags])

/*
    flags
    ---------
    g    global search
    i    match case-insensitive
*/

SELECT
    REGEXP_MATCHES('Amazing #Postgres', '#([A-Za-z]+)') -- Postgres
    
    -- With global flag it will match multiple times in the string
    REGEXP_MATCHES('Amazing #Postgres #Sql', '#([A-Za-z]+)', 'g') -- Postgres & Sql

regexp_replace

REGEXP_REPLACE function replaces strings that match a regular expression.

-- REGEXP_REPLACES(source_string, pattern, replacement_string, [, flags])

/*
    flags
    ---------
    g    global search
    i    match case-insensitive
*/

SELECT
    -- Reversing the groups
    REGEXP_REPLACES('Amazing Postgres', '(.*) (.*)', '\2 \1') -- Postgres Amazing
    
    -- Removing digits
    REGEXP_REPLACES('ABCD1234xyz', '[[:digit:]]', '', 'g') -- ABCDxyz

regexp_split_to_table

REGEXP_SPLIT_TO_TABLE will split delimited text into rows.

-- REGEXP_SPLIT_TO_TABLE(source_string, pattern)

regexp_split_to_array

REGEXP_SPLIT_TO_ARRAY will split delimited text into arrays.

-- REGEXP_SPLIT_TO_ARRAY(source_string, pattern)

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)

Text Search Engines

Using data types like tsvector and tsquery, Postgres gives us powerful ways for full text searches on large amounts of text.

• tsvector: Text to be searched and stored in optimized format.

• tsquery: Represent the search query terms and operations.

tsvector

It reduces text to a sorted list of "lexemes" (words without the variations created by suffixes), which are units of meaning in language.

By default, the words are normalized as a lexeme in English.

Use GIN index for tsvector is needed.

Create tsvector columns

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY
    ts_text TSVECTOR
);

to_tsvector

TO_TSVECTOR creates a list of tokens for a given string, returning:

• Every token as a lexeme, with

• pointers (the position in the document), and

• where words that carry little meaning, such as articles (the) and conjunctions (and, or) are conveniently omitted

• orders the words alphabetically, and number following each colon indicates its position in original text.

SELECT
    to_tsvector('this is a string')
    -- 'string':4

to_tsquery

TO_TSQUERY accepts a list of words that will be checked against the normalizaed vector we created with to_tsvector.

-- ts_query(text)

/*
    Operators
    -------
    @@             Match operators
    &              AND
    |              OR
    !              Not
    <-> or <1>     Search for adjacent words (Side by side)
    <n>            Search for words that are 'n' distance apart (Not up until 'n')
                   (n = abs(tsvector_position_word_1 - tsvector_position_word_2))
                   (e.g. <2> will get words that have 1 word between them)
*/

SELECT
    to_tsvector('this is a lamp') @@ to_tsquery('lamp') -- true
    
    to_tsvector('the quick brown fox jumped over the lazy dog')
    @@ to_tsquery('fox & dog') -- true
    
    to_tsvector('the quick brown fox jumped over the lazy dog')
    @@ to_tsquery('fox & !tiger') -- true
    
    to_tsvector('the quick brown fox jumped over the lazy dog')
    @@ to_tsquery('fox <-> jumped') -- true
    
    -- The order matters
    to_tsvector('the quick brown fox jumped over the lazy dog')
    @@ to_tsquery('jumped <-> fox') -- false
    
    to_tsvector('the quick brown fox jumped over the lazy dog')
    @@ to_tsquery('fox <-> dog') -- false
    
    -- 4 words between them (considering 'the' which is ommited in tsvector)
    to_tsvector('the quick brown fox jumped over the lazy dog')
    @@ to_tsquery('fox <5> dog') -- true
    
    -- It only searches for words in the '5' distance
    to_tsvector('the quick brown fox jumped over the lazy dog')
    @@ to_tsquery('fox <5> over') -- false

ts_rank

TS_RANK to rank query results.

SELECT
    ts_rank(
        to_tsvector('the quick brown fox jumped over the lazy dog'),
        to_tsquery('fox <-> jumped')
    ) -- 0.099