Built-in Functions

Built-in Functions

Comprehensive reference of all built-in functions, organized by category.

---

String Functions

UPPER / LOWER

UPPER(string TEXT) → TEXT — Converts all characters to upper case.

LOWER(string TEXT) → TEXT — Converts all characters to lower case.

SQL

SELECT UPPER('hello');   -- 'HELLO'
SELECT LOWER('WORLD');   -- 'world'

▶ Run

LENGTH / CHAR_LENGTH

LENGTH(string TEXT) → INT — Returns the number of characters in the string.

SQL

SELECT LENGTH('hello');   -- 5
SELECT LENGTH('');        -- 0

▶ Run

LEFT / RIGHT

LEFT(string TEXT, n INT) → TEXT — Returns the first n characters. If n is negative, returns all but the last |n| characters.

RIGHT(string TEXT, n INT) → TEXT — Returns the last n characters.

SQL

SELECT LEFT('PostgreSQL', 4);    -- 'Post'
SELECT RIGHT('PostgreSQL', 3);   -- 'SQL'
SELECT LEFT('hello', -2);        -- 'hel'

▶ Run

TRIM / BTRIM / LTRIM / RTRIM

TRIM([LEADING|TRAILING|BOTH] [chars TEXT] FROM string TEXT) → TEXT — Removes characters (default: spaces) from the start, end, or both.

BTRIM(string TEXT [, chars TEXT]) → TEXT — Removes chars from both ends.

LTRIM(string TEXT [, chars TEXT]) → TEXT — Removes chars from the left.

RTRIM(string TEXT [, chars TEXT]) → TEXT — Removes chars from the right.

SQL

SELECT TRIM('  hello  ');            -- 'hello'
SELECT BTRIM('xxhelloxx', 'x');      -- 'hello'
SELECT LTRIM('000123', '0');         -- '123'

▶ Run

LPAD / RPAD

LPAD(string TEXT, length INT [, fill TEXT]) → TEXT — Left-pads string to length using fill (default: space).

RPAD(string TEXT, length INT [, fill TEXT]) → TEXT — Right-pads string to length using fill.

SQL

SELECT LPAD('42', 5, '0');    -- '00042'
SELECT RPAD('hi', 5, '.');    -- 'hi...'

▶ Run

REPEAT

REPEAT(string TEXT, number INT) → TEXT — Repeats string the given number of times.

SQL

SELECT REPEAT('ab', 3);    -- 'ababab'

▶ Run

REVERSE

REVERSE(string TEXT) → TEXT — Reverses the characters of a string.

SQL

SELECT REVERSE('hello');    -- 'olleh'

▶ Run

INITCAP

INITCAP(string TEXT) → TEXT — Converts the first letter of each word to upper case and the rest to lower case.

SQL

SELECT INITCAP('hello world');    -- 'Hello World'

▶ Run

ASCII / CHR

ASCII(string TEXT) → INT — Returns the ASCII code of the first character.

CHR(code INT) → TEXT — Returns the character with the given ASCII code.

SQL

SELECT ASCII('A');     -- 65
SELECT CHR(65);        -- 'A'

▶ Run

STRPOS / POSITION

STRPOS(string TEXT, substring TEXT) → INT — Returns the position of the first occurrence of substring (1-based; 0 if not found).

POSITION(substring TEXT IN string TEXT) → INT — SQL standard form of STRPOS.

SQL

SELECT STRPOS('hello world', 'world');    -- 7
SELECT POSITION('lo' IN 'hello');         -- 4

▶ Run

SPLIT_PART

SPLIT_PART(string TEXT, delimiter TEXT, field INT) → TEXT — Splits string on delimiter and returns the nth field (1-based).

SQL

SELECT SPLIT_PART('a,b,c', ',', 2);         -- 'b'
SELECT SPLIT_PART('2024-03-15', '-', 1);    -- '2024'

▶ Run

TRANSLATE

TRANSLATE(string TEXT, from TEXT, to TEXT) → TEXT — Replaces characters in from with corresponding characters in to. If to is shorter, extra from characters are deleted.

SQL

SELECT TRANSLATE('hello', 'el', 'ip');    -- 'hippo'
SELECT TRANSLATE('123-456', '-', '');     -- '123456'

▶ Run

QUOTE_IDENT / QUOTE_LITERAL / QUOTE_NULLABLE

QUOTE_IDENT(string TEXT) → TEXT — Returns string as a safely quoted SQL identifier (double-quotes where needed).

QUOTE_LITERAL(string TEXT) → TEXT — Returns string as a safely quoted SQL literal.

QUOTE_NULLABLE(value TEXT) → TEXT — Like QUOTE_LITERAL but returns NULL for NULL input.

SQL

SELECT QUOTE_IDENT('my table');      -- '"my table"'
SELECT QUOTE_LITERAL('it''s');       -- '''it''''s'''
SELECT QUOTE_NULLABLE(NULL);         -- 'NULL'

▶ Run

OVERLAY

OVERLAY(string TEXT PLACING replacement TEXT FROM start INT [FOR length INT]) → TEXT — Replaces a substring within string.

SQL

SELECT OVERLAY('hello world' PLACING 'DB9' FROM 7);         -- 'hello DB9ld'
SELECT OVERLAY('hello world' PLACING 'DB9' FROM 7 FOR 5);   -- 'hello DB9'

▶ Run

CONCAT

CONCAT

CONCAT(val1 TEXT, val2 TEXT, ...)→ TEXT

Concatenates all arguments. NULL arguments are silently ignored (unlike the || operator, which returns NULL if any operand is NULL).

SQL

SELECT CONCAT('DB', '9');                    -- 'DB9'
SELECT CONCAT('Hello', NULL, ' World');      -- 'Hello World'

▶ Run

CONCAT_WS

CONCAT_WS

CONCAT_WS(separator TEXT, val1 TEXT, val2 TEXT, ...)→ TEXT

Concatenates arguments with a separator between each. The first argument is the separator. NULL values in the list are skipped.

SQL

SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Carol');   -- 'Alice, Bob, Carol'
SELECT CONCAT_WS('-', '2024', '01', '15');          -- '2024-01-15'

▶ Run

SUBSTRING

SUBSTRING

SUBSTRING(string TEXT, start INT [, length INT])→ TEXT

Extracts a substring starting at position start (1-based). Also supports SQL standard form: SUBSTRING(string FROM start FOR length). Supports regex extraction: SUBSTRING(string FROM pattern).

SQL

SELECT SUBSTRING('PostgreSQL', 1, 4);          -- 'Post'
SELECT SUBSTRING('PostgreSQL' FROM 5);          -- 'greSQL'
SELECT SUBSTRING('foo@bar.com' FROM '@(.*)');  -- 'bar.com'

▶ Run

REPLACE

REPLACE

REPLACE(string TEXT, from TEXT, to TEXT)→ TEXT

Replaces all occurrences of substring from in string with to.

SQL

SELECT REPLACE('Hello World', 'World', 'DB9');  -- 'Hello DB9'

▶ Run

FORMAT

FORMAT

FORMAT(formatstr TEXT [, args ANY, ...])→ TEXT

Formats a string using printf-style specifiers: %s (string), %I (quoted identifier), %L (quoted literal), %% (literal %). Useful for safely building dynamic SQL.

SQL

SELECT FORMAT('Hello, %s!', 'World');                         -- 'Hello, World!'
SELECT FORMAT('SELECT * FROM %I WHERE id = %L', 'users', 42); -- 'SELECT * FROM users WHERE id = ''42'''

▶ Run

---

CONCAT

CONCAT

CONCAT(val1 TEXT, val2 TEXT, ...)→ TEXT

Concatenates all arguments. NULL arguments are silently ignored (unlike the || operator, which returns NULL if any operand is NULL).

SQL

SELECT CONCAT('DB', '9');                    -- 'DB9'
SELECT CONCAT('Hello', NULL, ' World');      -- 'Hello World'
SELECT CONCAT(42, ' items');                 -- '42 items'

▶ Run

CONCAT_WS

CONCAT_WS

CONCAT_WS(separator TEXT, val1 TEXT, val2 TEXT, ...)→ TEXT

Concatenates arguments with a separator between each. The first argument is the separator. NULL values in the list are skipped.

Parameter	Type	Required	Default	Description
separator	TEXT	Yes	—	String placed between each non-NULL value
val1, val2, ...	TEXT	Yes	—	Values to join; NULLs are skipped

SQL

SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Carol');   -- 'Alice, Bob, Carol'
SELECT CONCAT_WS('-', '2024', '01', '15');          -- '2024-01-15'
SELECT CONCAT_WS(', ', 'Alice', NULL, 'Carol');     -- 'Alice, Carol'

▶ Run

SUBSTRING

SUBSTRING

SUBSTRING(string TEXT, start INT [, length INT])→ TEXT

Extracts a substring starting at position start (1-based). Also supports the SQL standard form: SUBSTRING(string FROM start FOR length). Supports regex extraction: SUBSTRING(string FROM pattern).

Parameter	Type	Required	Default	Description
string	TEXT	Yes	—	Source string
start	INT	Yes	—	Starting position (1-based)
length	INT	No	to end	Number of characters to extract

SQL

SELECT SUBSTRING('PostgreSQL', 1, 4);         -- 'Post'
SELECT SUBSTRING('PostgreSQL' FROM 5);         -- 'greSQL'
SELECT SUBSTRING('foo@bar.com' FROM '@(.*)'); -- 'bar.com'

▶ Run

REPLACE

REPLACE

REPLACE(string TEXT, from TEXT, to TEXT)→ TEXT

Replaces all occurrences of substring from in string with to.

SQL

SELECT REPLACE('Hello World', 'World', 'DB9');  -- 'Hello DB9'
SELECT REPLACE('aabbcc', 'bb', 'XX');           -- 'aaXXcc'

▶ Run

FORMAT

FORMAT

FORMAT(formatstr TEXT [, args ANY, ...])→ TEXT

Formats a string using printf-style format specifiers. Supports %s (string), %I (quoted identifier), %L (quoted literal), and %% (literal %). Particularly useful for safely building dynamic SQL.

SQL

SELECT FORMAT('Hello, %s!', 'World');
-- 'Hello, World!'

SELECT FORMAT('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = ''42'''

▶ Run

---

Mathematical Functions

ABS

ABS(x NUMERIC) → NUMERIC — Absolute value.

SQL

SELECT ABS(-42);    -- 42

▶ Run

CEIL / CEILING / FLOOR

CEIL(x NUMERIC) → NUMERIC — Nearest integer greater than or equal to x. CEILING is an alias.

FLOOR(x NUMERIC) → NUMERIC — Nearest integer less than or equal to x.

SQL

SELECT CEIL(4.2);    -- 5
SELECT FLOOR(4.8);   -- 4

▶ Run

ROUND / TRUNC

ROUND(x NUMERIC [, s INT]) → NUMERIC — Rounds to s decimal places (default 0).

TRUNC(x NUMERIC [, s INT]) → NUMERIC — Truncates to s decimal places (default 0).

SQL

SELECT ROUND(4.567, 2);    -- 4.57
SELECT TRUNC(4.567, 2);    -- 4.56
SELECT ROUND(4.5);         -- 5

▶ Run

SQRT / CBRT

SQRT(x NUMERIC) → NUMERIC — Square root.

CBRT(x DOUBLE PRECISION) → DOUBLE PRECISION — Cube root.

SQL

SELECT SQRT(16);    -- 4
SELECT CBRT(27);    -- 3

▶ Run

POWER / EXP / LN / LOG

POWER(base NUMERIC, exp NUMERIC) → NUMERIC — Raises base to the power of exp.

EXP(x NUMERIC) → NUMERIC — Exponential (e^x).

LN(x NUMERIC) → NUMERIC — Natural logarithm.

LOG(b NUMERIC, x NUMERIC) → NUMERIC — Logarithm of x to base b. LOG(x) = log base 10.

SQL

SELECT POWER(2, 10);     -- 1024
SELECT EXP(1);           -- 2.718281828...
SELECT LN(EXP(1));       -- 1
SELECT LOG(100);         -- 2
SELECT LOG(2, 8);        -- 3

▶ Run

MOD

MOD(y NUMERIC, x NUMERIC) → NUMERIC — Remainder of y / x.

SQL

SELECT MOD(10, 3);    -- 1

▶ Run

SIGN

SIGN(x NUMERIC) → NUMERIC — Returns -1, 0, or 1 based on the sign of x.

SQL

SELECT SIGN(-5);    -- -1
SELECT SIGN(0);     -- 0
SELECT SIGN(5);     -- 1

▶ Run

PI / DEGREES / RADIANS

PI() → DOUBLE PRECISION — Returns π (3.14159…).

DEGREES(x DOUBLE PRECISION) → DOUBLE PRECISION — Converts radians to degrees.

RADIANS(x DOUBLE PRECISION) → DOUBLE PRECISION — Converts degrees to radians.

SQL

SELECT PI();              -- 3.14159265358979
SELECT DEGREES(PI());     -- 180
SELECT RADIANS(180);      -- 3.14159265358979

▶ Run

Trigonometric Functions

SIN(x), COS(x), TAN(x) — Sine, cosine, tangent (argument in radians).

SQL

SELECT SIN(PI() / 2);     -- 1
SELECT COS(0);             -- 1

▶ Run

RANDOM

RANDOM() → DOUBLE PRECISION — Returns a random value in [0.0, 1.0).

SQL

SELECT RANDOM();                              -- e.g. 0.37428...
SELECT FLOOR(RANDOM() * 100)::int AS rand;   -- random int 0–99

▶ Run

HASHTEXT

HASHTEXT(string TEXT) → INT — Returns an integer hash of the string. Useful for sharding or bucketing.

SQL

SELECT HASHTEXT('hello') % 10 AS shard;    -- consistent bucket 0–9

▶ Run

---

---

Date/Time Functions

DATE_TRUNC

DATE_TRUNC

DATE_TRUNC(field TEXT, source TIMESTAMP|TIMESTAMPTZ|INTERVAL [, timezone TEXT])→ TIMESTAMP|TIMESTAMPTZ|INTERVAL

Truncates a timestamp or interval to the specified precision. Supported fields: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.

SQL

SELECT DATE_TRUNC('month', '2024-03-15 14:30'::timestamp);   -- 2024-03-01 00:00:00
SELECT DATE_TRUNC('hour', NOW());                              -- current hour

SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*) AS n
FROM logs GROUP BY 1 ORDER BY 1;

▶ Run

EXTRACT / DATE_PART

EXTRACT

EXTRACT(field FROM source TIMESTAMP|TIMESTAMPTZ|INTERVAL|DATE)→ NUMERIC

Extracts a numeric subfield. DATE_PART(field, source) is an equivalent functional form. Common fields: year, month, day, hour, minute, second, epoch, dow (0=Sun), doy, quarter, week.

SQL

SELECT EXTRACT(year FROM '2024-03-15'::date);   -- 2024
SELECT EXTRACT(epoch FROM INTERVAL '1 day');     -- 86400
SELECT DATE_PART('dow', NOW());                  -- day of week 0-6

▶ Run

NOW / CURRENT_TIMESTAMP

NOW

NOW()→ TIMESTAMPTZ

Returns the current transaction start time. Within a transaction, NOW() always returns the same value. Use CLOCK_TIMESTAMP() for the actual wall-clock time.

SQL

SELECT NOW();               -- transaction start time
SELECT CURRENT_TIMESTAMP;   -- alias for NOW()
SELECT CLOCK_TIMESTAMP();   -- actual current time (changes mid-transaction)

▶ Run

CURRENT_DATE

CURRENT_DATE → DATE — Current date (no time component).

SQL

SELECT CURRENT_DATE;      -- 2024-03-15

▶ Run

AGE

AGE(timestamp1 TIMESTAMPTZ, timestamp2 TIMESTAMPTZ) → INTERVAL — Computes the difference between two timestamps as a symbolic interval.

AGE(timestamp TIMESTAMPTZ) → INTERVAL — Difference between NOW() and the argument.

SQL

SELECT AGE('2024-03-15'::date, '2020-01-01'::date);
-- 4 years 2 mons 14 days

SELECT AGE(birth_date) AS age FROM users;
-- Age of each user

TO_CHAR

TO_CHAR

TO_CHAR(value TIMESTAMP|NUMERIC|INTERVAL, format TEXT)→ TEXT

Formats a timestamp, number, or interval. Common patterns: YYYY MM DD HH24 MI SS MS.

SQL

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');   -- '2024-03-15 14:30:00'
SELECT TO_CHAR(1234.56, 'FM9999.99');              -- '1234.56'

▶ Run

DATE_TRUNC

DATE_TRUNC

DATE_TRUNC(field TEXT, source TIMESTAMP|TIMESTAMPTZ|INTERVAL [, timezone TEXT])→ TIMESTAMP|TIMESTAMPTZ|INTERVAL

Truncates a timestamp or interval to the specified precision. Returns the same type as the input. The optional timezone argument controls which timezone is used for truncation of TIMESTAMPTZ values.

Parameter	Type	Required	Default	Description
field	TEXT	Yes	—	Precision: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium
source	TIMESTAMP | TIMESTAMPTZ | INTERVAL	Yes	—	Value to truncate
timezone	TEXT	No	session timezone	Timezone for TIMESTAMPTZ truncation (e.g. 'America/New_York')

SQL

SELECT DATE_TRUNC('month', '2024-03-15 14:30:00'::timestamp);
-- 2024-03-01 00:00:00

SELECT DATE_TRUNC('hour', NOW());
-- e.g. 2024-03-15 14:00:00+00

-- Group events by day
SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*) AS events
FROM logs
GROUP BY 1
ORDER BY 1;

▶ Run

EXTRACT / DATE_PART

EXTRACT

EXTRACT(field FROM source TIMESTAMP|TIMESTAMPTZ|INTERVAL|DATE)→ NUMERIC

Extracts a numeric subfield from a date/time value. DATE_PART(field, source) is an equivalent functional form. Returns a NUMERIC (not integer).

Parameter	Type	Required	Default	Description
field	TEXT	Yes	—	year, month, day, hour, minute, second, dow (0=Sun), doy, epoch, quarter, week, timezone, timezone_hour, timezone_minute
source	TIMESTAMP | TIMESTAMPTZ | INTERVAL | DATE	Yes	—	Value to extract from

SQL

SELECT EXTRACT(year FROM '2024-03-15'::date);    -- 2024
SELECT EXTRACT(month FROM NOW());                  -- current month number
SELECT EXTRACT(epoch FROM INTERVAL '1 day');       -- 86400
SELECT DATE_PART('dow', '2024-03-15'::date);       -- 5 (Friday)

▶ Run

NOW / CURRENT_TIMESTAMP

NOW

NOW()→ TIMESTAMPTZ

Returns the current transaction start time as TIMESTAMPTZ. Within a transaction, NOW() always returns the same value — the time the transaction began. Use CLOCK_TIMESTAMP() for the actual current wall-clock time that advances during a transaction.

SQL

SELECT NOW();                  -- 2024-03-15 14:30:00.123456+00
SELECT CURRENT_TIMESTAMP;      -- same as NOW()
SELECT CLOCK_TIMESTAMP();      -- actual wall-clock time (changes within transaction)

▶ Run

TO_CHAR

TO_CHAR

TO_CHAR(value TIMESTAMP|NUMERIC|INTERVAL, format TEXT)→ TEXT

Formats a timestamp, number, or interval as a string using a format template. Common patterns: YYYY (4-digit year), MM (month 01-12), DD (day 01-31), HH24 (hour 0-23), MI (minute), SS (second), MS (millisecond).

Parameter	Type	Required	Default	Description
value	TIMESTAMP | NUMERIC | INTERVAL	Yes	—	Value to format
format	TEXT	Yes	—	Format template string

SQL

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');             -- '2024-03-15'
SELECT TO_CHAR(NOW(), 'HH24:MI:SS');             -- '14:30:00'
SELECT TO_CHAR(NOW(), 'Day, DD Month YYYY');     -- 'Friday   , 15 March    2024'
SELECT TO_CHAR(1234567.89, '9,999,999.99');      -- '1,234,567.89'
SELECT TO_CHAR(INTERVAL '2 hours 30 mins', 'HH24:MI'); -- '02:30'

▶ Run

MAKE_INTERVAL

PostgreSQL-compatible interval constructor. All parameters optional, default 0.

SQL

MAKE_INTERVAL(years, months, weeks, days, hours, mins, secs)

SELECT MAKE_INTERVAL(days => 10);                    -- 10 days
SELECT MAKE_INTERVAL(hours => 1, mins => 30);        -- 01:30:00
SELECT MAKE_INTERVAL(years => 1, months => 6);       -- 1 year 6 mons
SELECT MAKE_INTERVAL(weeks => 2, days => 3, secs => 1.5);  -- 17 days 00:00:01.5

Returns INTERVAL. Named-argument syntax (param => value) is supported and recommended.

TIMEZONE

TIMEZONE(zone TEXT, timestamp TIMESTAMP) → TIMESTAMPTZ — Treat timestamp as being in zone and convert to TIMESTAMPTZ.

TIMEZONE(zone TEXT, timestamptz TIMESTAMPTZ) → TIMESTAMP — Convert TIMESTAMPTZ to local timestamp in zone.

SQL

SELECT TIMEZONE('America/New_York', NOW());       -- time in NY as local TIMESTAMP
SELECT NOW() AT TIME ZONE 'America/New_York';     -- equivalent operator form

▶ Run

---

---

Aggregate Functions

COUNT

COUNT(*) → BIGINT — Counts all rows including NULLs.

COUNT(expression) → BIGINT — Counts non-NULL values of expression.

COUNT(DISTINCT expression) → BIGINT — Counts distinct non-NULL values.

SQL

SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;           -- excludes NULL emails
SELECT COUNT(DISTINCT country) FROM users;

SUM / AVG / MIN / MAX

SUM(expression) — Sum of non-NULL values.

AVG(expression) — Average of non-NULL values.

MIN(expression) — Minimum non-NULL value.

MAX(expression) — Maximum non-NULL value.

SQL

SELECT SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders;

▶ Run

STRING_AGG

STRING_AGG

STRING_AGG(expression TEXT, delimiter TEXT [ORDER BY sort_expression])→ TEXT

Concatenates non-NULL string values with a delimiter. NULLs are skipped.

SQL

SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users;
-- 'Alice, Bob, Carol'

▶ Run

ARRAY_AGG

ARRAY_AGG

ARRAY_AGG(expression ANY [ORDER BY sort_expression])→ ARRAY

Collects values into an array. Supports ORDER BY. Use FILTER (WHERE expr IS NOT NULL) to exclude NULLs.

SQL

SELECT ARRAY_AGG(id ORDER BY id) FROM users;    -- {1,2,3,4,5}

▶ Run

BOOL_AND / BOOL_OR / EVERY

BOOL_AND(expression BOOLEAN) → BOOLEAN — True if all non-NULL values are true.

BOOL_OR(expression BOOLEAN) → BOOLEAN — True if any non-NULL value is true.

EVERY(expression BOOLEAN) → BOOLEAN — Alias for BOOL_AND.

SQL

SELECT BOOL_AND(active) FROM users;         -- true only if all users are active
SELECT BOOL_OR(has_premium) FROM users;     -- true if any user has premium

JSON_AGG / JSONB_AGG

JSON_AGG(expression) → JSON — Aggregates values into a JSON array, preserving NULL entries.

JSONB_AGG(expression) → JSONB — Same but returns JSONB.

SQL

SELECT JSONB_AGG(ROW_TO_JSON(u)) FROM users u;
-- [{"id":1,"name":"Alice"}, {"id":2,"name":"Bob"}, ...]

▶ Run

---

STRING_AGG

STRING_AGG

STRING_AGG(expression TEXT, delimiter TEXT [ORDER BY sort_expression])→ TEXT

Concatenates non-NULL string values with a delimiter. Supports ORDER BY within the aggregate to control concatenation order. Returns NULL when no rows match.

Parameter	Type	Required	Default	Description
expression	TEXT	Yes	—	Value to aggregate (NULLs are skipped)
delimiter	TEXT	Yes	—	Separator placed between values

SQL

SELECT STRING_AGG(name, ', ')
FROM users;
-- 'Alice, Bob, Carol'

-- With ORDER BY for deterministic ordering
SELECT STRING_AGG(name, ', ' ORDER BY name)
FROM users;
-- 'Alice, Bob, Carol'

-- Per-group tags
SELECT post_id, STRING_AGG(tag, ' | ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY post_id;

▶ Run

ARRAY_AGG

ARRAY_AGG

ARRAY_AGG(expression ANY [ORDER BY sort_expression])→ ARRAY

Collects values into an array. NULLs are included by default — use FILTER (WHERE expr IS NOT NULL) to exclude them. Supports ORDER BY within the aggregate.

SQL

SELECT ARRAY_AGG(id ORDER BY id) FROM users;
-- {1,2,3,4,5}

SELECT user_id, ARRAY_AGG(tag ORDER BY tag) AS tags
FROM user_tags
GROUP BY user_id;

-- Exclude NULLs
SELECT ARRAY_AGG(email) FILTER (WHERE email IS NOT NULL) FROM users;

---

Window Functions

All aggregate functions can also be used as window functions with an OVER clause.

ROW_NUMBER

ROW_NUMBER

ROW_NUMBER() OVER ([PARTITION BY expr] ORDER BY expr)→ BIGINT

Sequential integer from 1 within partition. No gaps — every row gets a unique number.

SQL

-- Most recent order per user
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t WHERE rn = 1;

▶ Run

RANK / DENSE_RANK

RANK() OVER (...) — Rank within partition, with gaps for ties (1, 1, 3…).

DENSE_RANK() OVER (...) — Rank within partition, without gaps for ties (1, 1, 2…).

SQL

SELECT name, score,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM leaderboard;

NTILE

NTILE(buckets INT) OVER ([PARTITION BY expr] ORDER BY expr) → INT — Divides rows into buckets number of approximately equal groups and assigns a bucket number to each row.

SQL

SELECT user_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM orders;

▶ Run

PERCENT_RANK / CUME_DIST

PERCENT_RANK() OVER (...) — Relative rank: (rank - 1) / (total rows - 1). Returns 0 to 1.

CUME_DIST() OVER (...) — Cumulative distribution: fraction of rows ≤ current row’s value. Returns (0, 1].

SQL

SELECT score,
  PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
  CUME_DIST()    OVER (ORDER BY score) AS cume_dist
FROM test_results;

LAG

LAG

LAG(value ANY [, offset INT [, default ANY]]) OVER ([PARTITION BY expr] ORDER BY expr)→ same as value

Returns the value from a row offset rows before the current row. Default offset is 1. Returns default (or NULL) if no such row exists.

SQL

SELECT date, revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue
FROM daily_sales;

LEAD

LEAD

LEAD(value ANY [, offset INT [, default ANY]]) OVER ([PARTITION BY expr] ORDER BY expr)→ same as value

Returns the value from a row offset rows after the current row. Mirror of LAG.

SQL

SELECT event_id, scheduled_at,
  LEAD(scheduled_at) OVER (ORDER BY scheduled_at) AS next_event
FROM events;

FIRST_VALUE / LAST_VALUE / NTH_VALUE

FIRST_VALUE(value) OVER (...) — Value at the first row of the window frame.

LAST_VALUE(value) OVER (...) — Value at the last row of the window frame.

NTH_VALUE(value, n INT) OVER (...) — Value at the nth row of the window frame (1-based).

SQL

SELECT user_id, amount,
  FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_amount,
  LAST_VALUE(amount)  OVER (PARTITION BY user_id ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM orders;

▶ Run

---

ROW_NUMBER

ROW_NUMBER

ROW_NUMBER() OVER ([PARTITION BY expr] ORDER BY expr)→ BIGINT

Assigns a sequential integer starting from 1 to each row within its window partition. Unlike RANK(), there are never gaps — every row gets a unique number within its partition.

SQL

-- Number rows per user
SELECT id, user_id, created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM orders;

-- Get the most recent order per user
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t WHERE rn = 1;

▶ Run

LAG

LAG

LAG(value ANY [, offset INT [, default ANY]]) OVER ([PARTITION BY expr] ORDER BY expr)→ same as value

Returns the value from a row that is offset rows before the current row within the window partition. If no such row exists, returns default (or NULL if unspecified).

Parameter	Type	Required	Default	Description
value	ANY	Yes	—	Expression to evaluate at the lagged row
offset	INT	No	1	Number of rows back to look
default	ANY	No	NULL	Value to return when the offset row does not exist

SQL

-- Previous day's revenue and delta
SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY date) AS delta
FROM daily_sales;

LEAD

LEAD

LEAD(value ANY [, offset INT [, default ANY]]) OVER ([PARTITION BY expr] ORDER BY expr)→ same as value

Returns the value from a row that is offset rows after the current row within the window partition. Mirror of LAG — same parameters, looks forward instead of backward.

SQL

-- Next scheduled event timestamp
SELECT event_id, scheduled_at,
  LEAD(scheduled_at) OVER (ORDER BY scheduled_at) AS next_event
FROM events;

FIRST_VALUE

FIRST_VALUE

FIRST_VALUE(value ANY) OVER ([PARTITION BY expr] ORDER BY expr [frame])→ same as value

Returns the value evaluated at the first row of the window frame. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to always get the first row of the entire partition.

SQL

-- First order amount per user alongside each order
SELECT user_id, order_id, amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_order_amount
FROM orders;

---

JSON/JSONB Functions

JSONB_SET

JSONB_SET

JSONB_SET(target JSONB, path TEXT[], new_value JSONB [, create_missing BOOLEAN])→ JSONB

Returns target with the element at path replaced by new_value. If create_missing is true (default), creates missing path segments.

SQL

SELECT JSONB_SET('{"name":"Alice"}'::jsonb, '{email}', '"alice@example.com"', true);
-- {"name": "Alice", "email": "alice@example.com"}

UPDATE users SET metadata = JSONB_SET(metadata, '{theme}', '"dark"') WHERE id = 1;

JSONB_BUILD_OBJECT / JSONB_BUILD_ARRAY

JSONB_BUILD_OBJECT

JSONB_BUILD_OBJECT(key1 TEXT, value1 ANY [, key2, value2, ...])→ JSONB

Builds a JSONB object from alternating key/value pairs.

JSONB_BUILD_ARRAY(val1 ANY, val2 ANY, ...) → JSONB — Builds a JSONB array from arguments.

SQL

SELECT JSONB_BUILD_OBJECT('id', 1, 'name', 'Alice');   -- {"id": 1, "name": "Alice"}
SELECT JSONB_BUILD_ARRAY(1, 'two', true);               -- [1, "two", true]

▶ Run

JSONB_EXTRACT_PATH / JSONB_EXTRACT_PATH_TEXT

JSONB_EXTRACT_PATH(from_json JSONB, VARIADIC path_elem TEXT[]) → JSONB — Extracts a nested element as JSONB.

JSONB_EXTRACT_PATH_TEXT(from_json JSONB, VARIADIC path_elem TEXT[]) → TEXT — Extracts a nested element as TEXT.

SQL

SELECT JSONB_EXTRACT_PATH('{"a":{"b":1}}'::jsonb, 'a', 'b');      -- 1 (JSONB)
SELECT JSONB_EXTRACT_PATH_TEXT('{"a":{"b":1}}'::jsonb, 'a', 'b'); -- '1' (TEXT)

▶ Run

JSONB_ARRAY_ELEMENTS / JSONB_ARRAY_ELEMENTS_TEXT

JSONB_ARRAY_ELEMENTS(from_json JSONB) → SETOF JSONB — Expands a JSON array to a set of JSONB elements.

JSONB_ARRAY_ELEMENTS_TEXT(from_json JSONB) → SETOF TEXT — Expands a JSON array to a set of TEXT values.

SQL

SELECT * FROM JSONB_ARRAY_ELEMENTS('[1, "two", true]'::jsonb);
-- 1 / "two" / true  (as JSONB rows)

SELECT * FROM JSONB_ARRAY_ELEMENTS_TEXT('["a","b","c"]'::jsonb);
-- a / b / c  (as text rows)

▶ Run

JSONB_EACH / JSONB_EACH_TEXT

JSONB_EACH

JSONB_EACH(from_json JSONB)→ SETOF (key TEXT, value JSONB)

Expands a JSON object into key/value rows. Use JSONB_EACH_TEXT to get both key and value as TEXT.

SQL

SELECT key, value FROM JSONB_EACH('{"a":1,"b":2}'::jsonb);
SELECT key, value FROM JSONB_EACH_TEXT('{"a":"x","b":"y"}'::jsonb);

▶ Run

JSONB_TYPEOF

JSONB_TYPEOF(from_json JSONB) → TEXT — Returns the type of the top-level JSON value: object, array, string, number, boolean, null.

SQL

SELECT JSONB_TYPEOF('{"a":1}'::jsonb);   -- 'object'
SELECT JSONB_TYPEOF('[1,2,3]'::jsonb);   -- 'array'
SELECT JSONB_TYPEOF('42'::jsonb);        -- 'number'

▶ Run

JSONB_ARRAY_LENGTH

JSONB_ARRAY_LENGTH(from_json JSONB) → INT — Returns the number of elements in a JSON array.

SQL

SELECT JSONB_ARRAY_LENGTH('[1,2,3,4]'::jsonb);    -- 4

▶ Run

JSONB_EXISTS / JSONB_EXISTS_ANY / JSONB_EXISTS_ALL

JSONB_EXISTS(from_json JSONB, key TEXT) → BOOLEAN — True if the given key exists at the top level.

JSONB_EXISTS_ANY(from_json JSONB, keys TEXT[]) → BOOLEAN — True if any key from the array exists.

JSONB_EXISTS_ALL(from_json JSONB, keys TEXT[]) → BOOLEAN — True if all keys from the array exist.

SQL

SELECT JSONB_EXISTS('{"a":1,"b":2}'::jsonb, 'a');                       -- true
SELECT JSONB_EXISTS_ANY('{"a":1}'::jsonb, ARRAY['a','c']);              -- true
SELECT JSONB_EXISTS_ALL('{"a":1,"b":2}'::jsonb, ARRAY['a','b']);        -- true

▶ Run

JSONB_OBJECT_KEYS

JSONB_OBJECT_KEYS(from_json JSONB) → SETOF TEXT — Returns the set of top-level keys of the JSON object.

SQL

SELECT JSONB_OBJECT_KEYS('{"name":"Alice","age":30}'::jsonb);
-- 'name' / 'age'

▶ Run

JSONB_PRETTY

JSONB_PRETTY(from_json JSONB) → TEXT — Returns a human-readable, indented JSON string.

SQL

SELECT JSONB_PRETTY('{"a":1,"b":{"c":2}}'::jsonb);
-- {
--     "a": 1,
--     "b": {
--         "c": 2
--     }
-- }

▶ Run

TO_JSON / TO_JSONB / ROW_TO_JSON

TO_JSON(any) → JSON — Converts any SQL value to its JSON representation.

TO_JSONB(any) → JSONB — Same but returns JSONB.

ROW_TO_JSON(record [, pretty_bool]) → JSON — Converts a row to a JSON object.

SQL

SELECT TO_JSONB(ARRAY[1,2,3]);                    -- [1, 2, 3]
SELECT ROW_TO_JSON(u) FROM users u LIMIT 1;       -- {"id":1,"name":"Alice",...}

▶ Run

JSON_BUILD_OBJECT / JSON_BUILD_ARRAY / JSON_OBJECT_KEYS / JSON_ARRAY_ELEMENTS / JSON_ARRAY_ELEMENTS_TEXT

These are the non-JSONB variants. They preserve original JSON key order and are set-returning.

JSON_BUILD_OBJECT(...), JSON_BUILD_ARRAY(...) — Same as JSONB variants but return JSON.

JSON_OBJECT_KEYS(from_json JSON) → SETOF TEXT — Returns top-level keys.

JSON_ARRAY_ELEMENTS(from_json JSON) → SETOF JSON — Expands a JSON array.

JSON_ARRAY_ELEMENTS_TEXT(from_json JSON) → SETOF TEXT — Expands to text.

JSONB_SET

JSONB_SET

JSONB_SET(target JSONB, path TEXT[], new_value JSONB [, create_missing BOOLEAN])→ JSONB

Returns target with the item at path replaced by new_value. If create_missing is true (the default) and a path segment does not exist, it is created. Use the #- operator to delete a path.

Parameter	Type	Required	Default	Description
target	JSONB	Yes	—	Source JSONB value to update
path	TEXT[]	Yes	—	Path to the element, e.g. '{addr,city}' or '{0}' for array index
new_value	JSONB	Yes	—	Replacement value (must be valid JSON)
create_missing	BOOLEAN	No	true	If true, creates keys that do not exist along the path

SQL

SELECT JSONB_SET('{"name":"Alice","age":30}'::jsonb, '{age}', '31');
-- {"name": "Alice", "age": 31}

SELECT JSONB_SET('{"user":{"name":"Alice"}}'::jsonb, '{user,email}', '"alice@example.com"', true);
-- {"user": {"name": "Alice", "email": "alice@example.com"}}

-- Update a column in place
UPDATE users
SET metadata = JSONB_SET(metadata, '{preferences,theme}', '"dark"')
WHERE id = 1;

JSONB_BUILD_OBJECT

JSONB_BUILD_OBJECT

JSONB_BUILD_OBJECT(key1 TEXT, value1 ANY [, key2, value2, ...])→ JSONB

Builds a JSONB object from a list of alternating key/value pairs. Keys must be strings; values are converted to their JSON equivalents automatically.

SQL

SELECT JSONB_BUILD_OBJECT('name', 'Alice', 'age', 30);
-- {"name": "Alice", "age": 30}

-- Build a response object from row data
SELECT JSONB_BUILD_OBJECT(
  'id', id,
  'name', name,
  'email', email,
  'created_at', created_at
) AS user_json
FROM users WHERE id = 1;

▶ Run

JSONB_EXTRACT_PATH_TEXT

JSONB_EXTRACT_PATH_TEXT

JSONB_EXTRACT_PATH_TEXT(from_json JSONB, VARIADIC path_elem TEXT[])→ TEXT

Extracts a nested field as TEXT using a variadic path. Equivalent to the #>> operator. Returns NULL if the path doesn't exist. Use JSONB_EXTRACT_PATH (without _TEXT) to return JSONB instead.

SQL

SELECT JSONB_EXTRACT_PATH_TEXT('{"user":{"name":"Alice","city":"NYC"}}'::jsonb, 'user', 'city');
-- 'NYC'

-- Equivalent operator form
SELECT '{"user":{"name":"Alice"}}'::jsonb #>> '{user,name}';
-- 'Alice'

▶ Run

JSONB_ARRAY_ELEMENTS

JSONB_ARRAY_ELEMENTS

JSONB_ARRAY_ELEMENTS(from_json JSONB)→ SETOF JSONB

Expands a JSON array into a set of JSONB values, one row per element. Use JSONB_ARRAY_ELEMENTS_TEXT to get plain text values instead of JSONB.

SQL

SELECT value FROM JSONB_ARRAY_ELEMENTS('[1, 2, 3]'::jsonb);
-- 1
-- 2
-- 3

-- Expand an array column for per-element filtering
SELECT id, tag
FROM products, JSONB_ARRAY_ELEMENTS_TEXT(tags) AS tag
WHERE tag = 'electronics';

▶ Run

JSONB_EACH

JSONB_EACH

JSONB_EACH(from_json JSONB)→ SETOF (key TEXT, value JSONB)

Expands a JSON object into a set of (key, value) rows, one row per top-level key. Use JSONB_EACH_TEXT to get both key and value as TEXT.

SQL

SELECT key, value FROM JSONB_EACH('{"a":1,"b":2,"c":3}'::jsonb);
-- key | value
-- a   | 1
-- b   | 2
-- c   | 3

-- Pivot metadata keys into rows
SELECT id, key, value::text
FROM users, JSONB_EACH_TEXT(metadata)
WHERE key LIKE 'pref_%';

JSON Operators

Operator	Description	Example
->	Get JSON element by key/index	data->'name'
->>	Get JSON element as text	data->>'name'
#>	Get element by path	data#>'{addr,city}'
#>>	Get element by path as text	data#>>'{addr,city}'
#-	Delete key/path	data #- '{addr}'
@>	Contains	data @> '{"a":1}'
<@	Contained by	'{"a":1}' <@ data
?	Key exists	data ? 'email'
?|	Any key exists	data ?| array['a','b']
?&	All keys exist	data ?& array['a','b']

---

Array Functions

ARRAY_LENGTH / ARRAY_UPPER / ARRAY_LOWER / CARDINALITY

ARRAY_LENGTH(array, dimension INT) → INT — Length of the array along the given dimension (1-based).

ARRAY_UPPER(array, dimension INT) → INT — Upper bound of array dimension.

ARRAY_LOWER(array, dimension INT) → INT — Lower bound of array dimension (usually 1).

CARDINALITY(array) → INT — Total number of elements across all dimensions.

SQL

SELECT ARRAY_LENGTH(ARRAY[1,2,3], 1);    -- 3
SELECT ARRAY_UPPER(ARRAY[1,2,3], 1);     -- 3
SELECT ARRAY_LOWER(ARRAY[1,2,3], 1);     -- 1
SELECT CARDINALITY(ARRAY[[1,2],[3,4]]);  -- 4

▶ Run

ARRAY_POSITION

ARRAY_POSITION(array, element [, subscript INT]) → INT — Returns the position of the first occurrence of element in array (1-based; NULL if not found).

SQL

SELECT ARRAY_POSITION(ARRAY['a','b','c','b'], 'b');    -- 2
SELECT ARRAY_POSITION(ARRAY['a','b','c','b'], 'b', 3); -- 4 (start from position 3)

▶ Run

ARRAY_CAT / ARRAY_APPEND / ARRAY_PREPEND / ARRAY_REMOVE

ARRAY_CAT(array1, array2) → ARRAY — Concatenates two arrays. Equivalent to || operator.

ARRAY_APPEND(array, element) → ARRAY — Appends an element to the end.

ARRAY_PREPEND(element, array) → ARRAY — Prepends an element to the beginning.

ARRAY_REMOVE(array, element) → ARRAY — Removes all occurrences of element.

SQL

SELECT ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]);        -- {1,2,3,4}
SELECT ARRAY_APPEND(ARRAY[1,2,3], 4);             -- {1,2,3,4}
SELECT ARRAY_PREPEND(0, ARRAY[1,2,3]);            -- {0,1,2,3}
SELECT ARRAY_REMOVE(ARRAY[1,2,3,2], 2);           -- {1,3}

▶ Run

ARRAY_TO_STRING / STRING_TO_ARRAY

ARRAY_TO_STRING(array, delimiter TEXT [, null_string TEXT]) → TEXT — Converts array to a delimited string.

STRING_TO_ARRAY(string TEXT, delimiter TEXT [, null_string TEXT]) → TEXT[] — Splits a string into a text array.

SQL

SELECT ARRAY_TO_STRING(ARRAY[1,2,3], ',');           -- '1,2,3'
SELECT ARRAY_TO_STRING(ARRAY['a',NULL,'c'], ',', 'X'); -- 'a,X,c'
SELECT STRING_TO_ARRAY('a,b,c', ',');                -- {a,b,c}

▶ Run

UNNEST

UNNEST

UNNEST(array ANYARRAY [, array2, ...])→ SETOF element

Expands an array into a set of rows. Multiple arrays are expanded in parallel. Use WITH ORDINALITY to get element positions.

SQL

SELECT UNNEST(ARRAY['a','b','c']);
-- a / b / c

SELECT val, pos FROM UNNEST(ARRAY['x','y','z']) WITH ORDINALITY AS t(val, pos);
-- x|1, y|2, z|3

▶ Run

---

ARRAY_LENGTH

ARRAY_LENGTH

ARRAY_LENGTH(array ANYARRAY, dimension INT)→ INT

Returns the length of the requested array dimension. For 1-D arrays, dimension is 1. Returns NULL for an empty array. CARDINALITY() is a simpler alternative that returns the total element count of a 1-D array.

SQL

SELECT ARRAY_LENGTH(ARRAY[1, 2, 3, 4, 5], 1);        -- 5
SELECT ARRAY_LENGTH(ARRAY[['a','b'],['c','d']], 2);   -- 2 (second dimension)
SELECT CARDINALITY(ARRAY[1,2,3]);                       -- 3

▶ Run

UNNEST

UNNEST

UNNEST(array ANYARRAY [, array2, ...])→ SETOF element

Expands an array into a set of rows, one row per element. When multiple arrays are provided, they are expanded in parallel (zip), stopping at the shortest. Use WITH ORDINALITY to get element positions.

SQL

SELECT UNNEST(ARRAY['a', 'b', 'c']);
-- a
-- b
-- c

-- With ordinality to get element position
SELECT val, pos
FROM UNNEST(ARRAY['x','y','z']) WITH ORDINALITY AS t(val, pos);
-- x | 1
-- y | 2
-- z | 3

-- Join array column elements as rows
SELECT id, tag
FROM articles, UNNEST(tags) AS tag
WHERE 'postgres' = ANY(tags);

---

Regular Expression Functions

REGEXP_REPLACE

REGEXP_REPLACE

REGEXP_REPLACE(string TEXT, pattern TEXT, replacement TEXT [, flags TEXT])→ TEXT

Replaces POSIX regex matches. Flags: g = replace all, i = case-insensitive. Use \\1, \\2 for back-references.

SQL

SELECT REGEXP_REPLACE('foo bar baz', '\s+', '-', 'g');   -- 'foo-bar-baz'
SELECT REGEXP_REPLACE('  trim  ', '^\s+|\s+$', '', 'g'); -- 'trim'

▶ Run

REGEXP_SPLIT_TO_ARRAY

REGEXP_SPLIT_TO_ARRAY(string TEXT, pattern TEXT [, flags TEXT]) → TEXT[] — Splits string using a regular expression delimiter, returning a text array.

SQL

SELECT REGEXP_SPLIT_TO_ARRAY('one two   three', '\s+');
-- {one,two,three}

▶ Run

REGEXP_REPLACE

REGEXP_REPLACE

REGEXP_REPLACE(string TEXT, pattern TEXT, replacement TEXT [, flags TEXT])→ TEXT

Replaces substring(s) matching a POSIX regular expression. By default replaces only the first match; use the 'g' flag to replace all occurrences. Use \\1, \\2 in replacement for back-references.

Parameter	Type	Required	Default	Description
string	TEXT	Yes	—	Input string
pattern	TEXT	Yes	—	POSIX regular expression
replacement	TEXT	Yes	—	Replacement string; use \1, \2 for capture group back-references
flags	TEXT	No	''	g = replace all matches, i = case-insensitive

SQL

SELECT REGEXP_REPLACE('Hello World', 'World', 'DB9');      -- 'Hello DB9'
SELECT REGEXP_REPLACE('abc123def', '[0-9]+', 'NUM');        -- 'abcNUMdef'
SELECT REGEXP_REPLACE('  spaces  ', '^\s+|\s+$', '', 'g'); -- 'spaces'
SELECT REGEXP_REPLACE('foo bar baz', '\s+', '-', 'g');      -- 'foo-bar-baz'

▶ Run

Regular Expression Operators

Operator	Description
~	Matches regex (case-sensitive)
~*	Matches regex (case-insensitive)
!~	Does not match (case-sensitive)
!~*	Does not match (case-insensitive)

---

Full-Text Search

TO_TSVECTOR

TO_TSVECTOR([config REGCONFIG,] document TEXT) → TSVECTOR — Converts text to a tsvector of lexemes for full-text search. The optional config sets the text search configuration (e.g. 'english').

SQL

SELECT TO_TSVECTOR('english', 'the quick brown fox');
-- 'brown':3 'fox':4 'quick':2

-- Indexed column (GIN index recommended)
CREATE INDEX ON articles USING GIN (TO_TSVECTOR('english', body));

▶ Run

TO_TSQUERY / PLAINTO_TSQUERY / PHRASETO_TSQUERY / WEBSEARCH_TO_TSQUERY

TO_TSQUERY([config,] querytext TEXT) → TSQUERY — Parses a query string with explicit operators (&, |, !, <->).

PLAINTO_TSQUERY([config,] querytext TEXT) → TSQUERY — Converts plain text to a tsquery treating all words as AND-joined terms.

PHRASETO_TSQUERY([config,] querytext TEXT) → TSQUERY — Creates a phrase query requiring words to appear adjacent.

WEBSEARCH_TO_TSQUERY([config,] querytext TEXT) → TSQUERY — Converts web-search style query (quoted phrases, - exclusions) to a tsquery.

SQL

SELECT TO_TSQUERY('english', 'quick & fox');
SELECT PLAINTO_TSQUERY('english', 'quick brown fox');
SELECT PHRASETO_TSQUERY('english', 'quick brown');     -- adjacent words
SELECT WEBSEARCH_TO_TSQUERY('english', '"quick brown" -slow');

▶ Run

SETWEIGHT

SETWEIGHT

SETWEIGHT(vector TSVECTOR, weight "char")→ TSVECTOR

Assigns weight A/B/C/D to all lexemes in a tsvector. A is highest, D is default. Used to boost specific fields in TS_RANK scoring.

SQL

SELECT SETWEIGHT(TO_TSVECTOR('english', title), 'A') ||
       SETWEIGHT(TO_TSVECTOR('english', body), 'C') AS doc
FROM articles;

▶ Run

TS_RANK / TS_RANK_CD

TS_RANK

TS_RANK(vector TSVECTOR, query TSQUERY [, normalization INT])→ FLOAT4

Calculates a relevance score. Higher = more relevant. normalization bitmask controls length normalization: 0=none, 1=1/log(ndoc), 2=1/ndoc.

TS_RANK_CD(vector, query [, normalization]) — Like TS_RANK but uses the “cover density” ranking algorithm, which also factors in how close matches are to each other.

SQL

SELECT title, TS_RANK(TO_TSVECTOR('english', body), query) AS rank
FROM articles, PLAINTO_TSQUERY('english', 'database') AS query
WHERE TO_TSVECTOR('english', body) @@ query
ORDER BY rank DESC LIMIT 10;

▶ Run

TS_HEADLINE

TS_HEADLINE([config REGCONFIG,] document TEXT, query TSQUERY [, options TEXT]) → TEXT — Highlights matching terms in a document fragment. Options include StartSel, StopSel, MaxWords, MinWords, ShortWord, HighlightAll, MaxFragments, FragmentDelimiter.

SQL

SELECT TS_HEADLINE(
  'english',
  body,
  PLAINTO_TSQUERY('english', 'database performance'),
  'StartSel=<b>, StopSel=</b>, MaxFragments=2'
) AS headline
FROM articles
WHERE TO_TSVECTOR('english', body) @@ PLAINTO_TSQUERY('english', 'database performance');

▶ Run

---

SETWEIGHT

SETWEIGHT

SETWEIGHT(vector TSVECTOR, weight "char")→ TSVECTOR

Assigns a weight label to all lexemes in a tsvector. Weights are A (highest), B, C, D (lowest/default). Used to boost certain document fields (e.g. title vs body) when scoring with TS_RANK.

SQL

-- Weight title higher than body
SELECT SETWEIGHT(TO_TSVECTOR('english', title), 'A') ||
       SETWEIGHT(TO_TSVECTOR('english', body), 'C') AS document
FROM articles;

-- Indexed column with weighted fields
CREATE INDEX ON articles USING GIN (
  SETWEIGHT(TO_TSVECTOR('english', title), 'A') ||
  SETWEIGHT(TO_TSVECTOR('english', body), 'C')
);

▶ Run

TS_RANK

TS_RANK

TS_RANK(vector TSVECTOR, query TSQUERY [, normalization INT])→ FLOAT4

Calculates a relevance score for a tsvector against a tsquery. Higher scores indicate better matches. The normalization bitmask controls whether document length affects the score.

Parameter	Type	Required	Default	Description
vector	TSVECTOR	Yes	—	Document tsvector (often a stored/indexed column)
query	TSQUERY	Yes	—	Search query
normalization	INT	No	0	Bitmask: 0=none, 1=1/log(ndoc), 2=1/ndoc, 4=mean harmonic distance, 8=unique words, 16=1/log(unique words), 32=rank/(rank+1)

SQL

SELECT title,
  TS_RANK(
    TO_TSVECTOR('english', body),
    PLAINTO_TSQUERY('english', 'database performance')
  ) AS rank
FROM articles
WHERE TO_TSVECTOR('english', body) @@ PLAINTO_TSQUERY('english', 'database performance')
ORDER BY rank DESC
LIMIT 10;

▶ Run

---

UUID Functions

GEN_RANDOM_UUID() → UUID — Generates a random UUID v4. This is the standard way to generate UUIDs in PostgreSQL 13+.

UUID_GENERATE_V4() → UUID — Generates a random UUID v4 (requires uuid-ossp extension). Prefer GEN_RANDOM_UUID().

UUIDV7() → UUID — Generates a UUID v7 (time-ordered, monotonically increasing). Useful for primary keys that sort by creation time.

SQL

SELECT GEN_RANDOM_UUID();    -- e.g. 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
SELECT UUIDV7();             -- time-ordered UUID v7

-- Common primary key pattern
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT UUIDV7(),
  name TEXT NOT NULL
);

▶ Run

---

---

Encoding & Hashing

ENCODE / DECODE

ENCODE(data BYTEA, format TEXT) → TEXT — Encodes binary as text. Formats: base64, hex, escape.

DECODE(string TEXT, format TEXT) → BYTEA — Decodes text back to binary.

SQL

SELECT ENCODE('Hello'::bytea, 'base64');    -- 'SGVsbG8='
SELECT ENCODE('Hello'::bytea, 'hex');       -- '48656c6c6f'
SELECT DECODE('SGVsbG8=', 'base64');        -- '\x48656c6c6f'

▶ Run

MD5

MD5(string TEXT) → TEXT — Returns a 32-character lowercase MD5 hex digest. Not suitable for security use.

SQL

SELECT MD5('hello');    -- '5d41402abc4b2a76b9719d911017c592'

▶ Run

---

ENCODE / DECODE

ENCODE

ENCODE(data BYTEA, format TEXT)→ TEXT

Encodes binary data as text. Supported formats: base64, hex, escape.

DECODE

DECODE(string TEXT, format TEXT)→ BYTEA

Decodes text-encoded binary data back to BYTEA. Supported formats: base64, hex, escape.

SQL

SELECT ENCODE('Hello DB9'::bytea, 'base64');    -- 'SGVsbG8gREI5'
SELECT ENCODE('Hello DB9'::bytea, 'hex');       -- '48656c6c6f2044423'...

SELECT DECODE('SGVsbG8gREI5', 'base64');        -- '\x48656c6c6f204442...'

▶ Run

MD5

MD5

MD5(string TEXT)→ TEXT

Returns the MD5 hash of a string as a lowercase 32-character hex string. Not recommended for security-sensitive hashing.

SQL

SELECT MD5('hello');            -- '5d41402abc4b2a76b9719d911017c592'
SELECT MD5(RANDOM()::text);     -- random 32-char hex string

▶ Run

---

Vector Functions (pgvector-compatible)

L2_DISTANCE(a VECTOR, b VECTOR) → FLOAT8 — Euclidean (L2) distance between two vectors. Equivalent to a <-> b.

INNER_PRODUCT(a VECTOR, b VECTOR) → FLOAT8 — Dot product. Negative inner product via a <#> b.

COSINE_DISTANCE(a VECTOR, b VECTOR) → FLOAT8 — Cosine distance (1 - cosine similarity). Equivalent to a <=> b.

VECTOR_DIMS(v VECTOR) → INT — Returns the number of dimensions.

VECTOR_NORM(v VECTOR) → FLOAT8 — Returns the Euclidean norm (magnitude).

SQL

-- Similarity search
SELECT id, embedding <-> query_vec AS distance
FROM items
ORDER BY embedding <-> query_vec
LIMIT 10;

SELECT VECTOR_DIMS('[1,2,3]'::vector);    -- 3
SELECT VECTOR_NORM('[3,4]'::vector);      -- 5

---

---

Embedding Functions

EMBEDDING(text TEXT [, model TEXT, dimensions INT]) → VECTOR — Generates a vector embedding inline in SQL.

EMBED_TEXT(model TEXT, text TEXT [, json_options JSONB]) → VECTOR — Explicit model-specified embedding function.

SQL

-- Requires: CREATE EXTENSION embedding
SELECT EMBEDDING('hello world');              -- uses default model

-- Semantic search
SELECT id, title
FROM docs
ORDER BY EMBEDDING(title) <=> EMBEDDING('database connection')
LIMIT 5;

▶ Run

See Vector Search for end-to-end examples.

---

---

Document Chunking

CHUNK_TEXT(content TEXT [, max_chars INT, overlap_chars INT, title TEXT]) — Table-valued function that splits text into overlapping chunks for RAG pipelines. Markdown-aware at paragraph, heading, and list boundaries.

SQL

SELECT chunk_index, chunk_text, char_count
FROM CHUNK_TEXT('Long document...', 500, 50, 'My Doc');

See CHUNK_TEXT for full reference.

---

---

HTTP Functions (Scalar)

HTTP_GET(url TEXT) → JSONB — Sends a GET request.

HTTP_POST(url TEXT, body TEXT, content_type TEXT) → JSONB — Sends a POST request.

HTTP_PUT(url TEXT, body TEXT, content_type TEXT) → JSONB — Sends a PUT request.

HTTP_DELETE(url TEXT) → JSONB — Sends a DELETE request.

HTTP_HEAD(url TEXT) → JSONB — Sends a HEAD request.

HTTP_PATCH(url TEXT, body TEXT, content_type TEXT) → JSONB — Sends a PATCH request.

HTTP(method TEXT, url TEXT, body TEXT, options JSONB) → JSONB — Generic HTTP function.

All return JSONB with keys: status (INT), content (TEXT), content_type (TEXT), headers (JSONB).

SQL

SELECT (HTTP_GET('https://api.example.com/data'))->>'content';

SELECT
  (HTTP_POST(
    'https://api.example.com/items',
    '{"name":"test"}',
    'application/json'
  ))->>'status';   -- e.g. '201'

See http extension for details.

---

---

Storage Functions

DB9_REFRESH_STORAGE_STATS() → VOID — Triggers an asynchronous storage scan. Results appear in _DB9_SYS_STORAGE_STATS and _DB9_SYS_TABLE_STORAGE_STATS. See Storage Accounting.

FS9_STORAGE_STATS() → TABLE(total_files BIGINT, total_directories BIGINT, total_logical_bytes BIGINT) — Returns filesystem storage statistics. Requires the fs9 extension. See fs9 extension.

---

---

Sequence Functions

NEXTVAL(regclass) → BIGINT — Advances a sequence and returns the new value.

CURRVAL(regclass) → BIGINT — Returns the last value from NEXTVAL in the current session.

SETVAL(regclass, value BIGINT [, called BOOLEAN]) → BIGINT — Sets the current value of a sequence.

LASTVAL() → BIGINT — Returns the last value returned by NEXTVAL in the current session, for any sequence.

PG_GET_SERIAL_SEQUENCE(table_name TEXT, column_name TEXT) → TEXT — Returns the name of the sequence associated with a serial column.

SQL

SELECT NEXTVAL('my_seq');
SELECT CURRVAL('my_seq');
SELECT SETVAL('my_seq', 100);         -- next NEXTVAL returns 101
SELECT SETVAL('my_seq', 100, false);  -- next NEXTVAL returns 100

SELECT PG_GET_SERIAL_SEQUENCE('users', 'id');  -- e.g. 'public.users_id_seq'

---

---

Conditional Expressions

COALESCE

COALESCE

COALESCE(value1 ANY, value2 ANY [, ...])→ same type

Returns the first non-NULL argument, evaluated left-to-right. All arguments must be of compatible types.

SQL

SELECT COALESCE(NULL, NULL, 'fallback');    -- 'fallback'
SELECT COALESCE(nickname, first_name, 'Anonymous') FROM users;

NULLIF

NULLIF(value1 ANY, value2 ANY) → same type — Returns NULL if value1 = value2, otherwise returns value1. The inverse of COALESCE.

SQL

SELECT NULLIF(0, 0);           -- NULL (avoids division by zero)
SELECT 100 / NULLIF(divisor, 0) FROM t;  -- safe division

GREATEST / LEAST

GREATEST(val1, val2, ...) → same type — Returns the largest non-NULL value among arguments.

LEAST(val1, val2, ...) → same type — Returns the smallest non-NULL value among arguments.

SQL

SELECT GREATEST(1, 5, 3, 2);    -- 5
SELECT LEAST('apple', 'banana', 'cherry');  -- 'apple'

▶ Run

CASE WHEN

SQL

-- Searched CASE
SELECT
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

-- Simple CASE
SELECT
  CASE status
    WHEN 'active'   THEN 'Active'
    WHEN 'inactive' THEN 'Inactive'
    ELSE 'Unknown'
  END AS label
FROM users;

---

COALESCE

COALESCE

COALESCE(value1 ANY, value2 ANY [, ...])→ same type

Returns the first non-NULL argument, evaluating left-to-right and stopping at the first non-NULL value. All arguments must be of compatible types. The classic null-safe fallback function.

SQL

SELECT COALESCE(NULL, NULL, 'fallback');                         -- 'fallback'
SELECT COALESCE(nickname, first_name, 'Anonymous') FROM users;  -- first non-null name

-- Provide 0 instead of NULL for aggregates
SELECT user_id, COALESCE(SUM(amount), 0) AS total
FROM orders
GROUP BY user_id;

---

System / Compatibility Functions

GENERATE_SERIES

GENERATE_SERIES

GENERATE_SERIES(start, stop [, step])→ SETOF value

Generates values from start to stop (inclusive). Works with INT, NUMERIC, TIMESTAMP, TIMESTAMPTZ. Default step is 1 or '1 day'.

Usage in DB9

GENERATE_SERIES must be used as a set-returning function in the FROM clause: SELECT * FROM generate_series(1, 5). Using it directly in the SELECT list (e.g. SELECT generate_series(1, 5)) returns an error about set-returning functions not being allowed there.

SQL

SELECT * FROM GENERATE_SERIES(1, 5);
SELECT * FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-07'::date, '1 day');

-- Fill time-series gaps
SELECT d.day, COALESCE(COUNT(o.id), 0) AS orders
FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-31'::date, '1 day') d(day)
LEFT JOIN orders o ON o.created_at::date = d.day
GROUP BY d.day ORDER BY d.day;

▶ Run

PG_TYPEOF

PG_TYPEOF(any) → REGTYPE — Returns the data type of its argument.

SQL

SELECT PG_TYPEOF(42);           -- 'integer'
SELECT PG_TYPEOF('hello');      -- 'unknown'
SELECT PG_TYPEOF(NOW());        -- 'timestamp with time zone'

▶ Run

SIZE FUNCTIONS

PG_COLUMN_SIZE(any) → INT — Number of bytes used to store a particular value.

SQL

SELECT PG_COLUMN_SIZE(ROW(1, 'hello', NOW()));

▶ Run

PRIVILEGE FUNCTIONS

HAS_TABLE_PRIVILEGE(user TEXT, table TEXT, privilege TEXT) → BOOLEAN — Tests if user has privilege on table.

HAS_SCHEMA_PRIVILEGE(user TEXT, schema TEXT, privilege TEXT) → BOOLEAN — Tests schema privilege.

HAS_DATABASE_PRIVILEGE(user TEXT, database TEXT, privilege TEXT) → BOOLEAN — Tests database privilege.

SQL

SELECT HAS_TABLE_PRIVILEGE('alice', 'public.users', 'SELECT');
SELECT HAS_SCHEMA_PRIVILEGE(CURRENT_USER, 'public', 'USAGE');

▶ Run

DESCRIPTION FUNCTIONS

OBJ_DESCRIPTION(oid OID, catalog_name TEXT) → TEXT — Returns the comment (description) for a database object.

COL_DESCRIPTION(table_oid OID, column_number INT) → TEXT — Returns the comment for a table column.

SQL

SELECT OBJ_DESCRIPTION('public.users'::regclass, 'pg_class');
SELECT COL_DESCRIPTION('public.users'::regclass, 1);

▶ Run

SYSTEM INFO

VERSION() → TEXT — Returns a string describing the server version.

CURRENT_USER → TEXT — Name of the current effective user.

CURRENT_DATABASE() → NAME — Name of the current database.

CURRENT_SCHEMA() → NAME — Current schema search path default.

PG_BACKEND_PID() → INT — Process ID of the server process for the current session.

FORMAT_TYPE(type_oid OID, typemod INT) → TEXT — Returns the SQL name of a data type.

PG_ENCODING_TO_CHAR(encoding INT) → TEXT — Converts an encoding number to its name.

SQL

SELECT VERSION();
SELECT CURRENT_USER, CURRENT_DATABASE(), CURRENT_SCHEMA();
SELECT PG_BACKEND_PID();

▶ Run

---

GENERATE_SERIES

GENERATE_SERIES

GENERATE_SERIES(start, stop [, step])→ SETOF value

Generates a series of values from start to stop (inclusive), incrementing by step. Works with integers, numerics, timestamps, and timestamptz. Default step is 1 for numerics or 1 day for timestamps.

Parameter	Type	Required	Default	Description
start	INT | NUMERIC | TIMESTAMP | TIMESTAMPTZ	Yes	—	Series start value (inclusive)
stop	same as start	Yes	—	Series end value (inclusive)
step	INT | NUMERIC | INTERVAL	No	1	Increment between values

Usage in DB9

GENERATE_SERIES must be used as a set-returning function in the FROM clause: SELECT * FROM generate_series(1, 5). Using it directly in the SELECT list (e.g. SELECT generate_series(1, 5)) returns an error about set-returning functions not being allowed there.

SQL

SELECT * FROM GENERATE_SERIES(1, 5);             -- 1, 2, 3, 4, 5
SELECT * FROM GENERATE_SERIES(0, 1, 0.25);       -- 0, 0.25, 0.50, 0.75, 1.00

-- Generate a date range
SELECT * FROM GENERATE_SERIES(
  '2024-01-01'::date,
  '2024-01-07'::date,
  '1 day'::interval
) AS day;

-- Fill time-series gaps with LEFT JOIN
SELECT d.day, COALESCE(COUNT(o.id), 0) AS orders
FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-31'::date, '1 day') AS d(day)
LEFT JOIN orders o ON o.created_at::date = d.day
GROUP BY d.day
ORDER BY d.day;

▶ Run

---

Background SQL

PG_BACKGROUND_LAUNCH(sql TEXT) → INT — Launches a SQL statement asynchronously in a background session. Returns a handle (integer).

PG_BACKGROUND_RESULT(handle INT) → SETOF RECORD — Waits for and retrieves the result of a background query by handle.

SQL

-- Launch a long-running query in the background
SELECT PG_BACKGROUND_LAUNCH('VACUUM ANALYZE large_table') AS handle;
-- Returns: 12345

-- Collect the result
SELECT * FROM PG_BACKGROUND_RESULT(12345) AS r(status TEXT);