PostgreSQL function reference

A short guide to each function: what it does, how to call it, examples you can edit and run right here, and what to watch out for so you don't trip up.

These are the core functions — enough for most queries. For everything else, see the official PostgreSQL documentation(opens in a new tab).

/

Conditional

COALESCE

Returns the first value in the list that isn't NULL.

COALESCE(<value1>, <value2>, …)NULLIF

Returns NULL if two values are equal, otherwise the first value.

NULLIF(<value>, <compare with>)GREATEST

Returns the largest value in a list.

GREATEST(<value1>, <value2>, …)LEAST

Returns the smallest value in a list.

LEAST(<value1>, <value2>, …)CASE

Returns different values depending on conditions — “if… then… else” in SQL.

CASE WHEN <condition> THEN <result> … ELSE <otherwise> END

Numbers

ROUND

Rounds a number to an integer or to the number of decimal places you need.

ROUND(<number>)CEIL

Rounds a number up — to the nearest integer that isn't smaller than it.

CEIL(<number>)FLOOR

Rounds a number down — to the nearest integer that isn't larger than it.

FLOOR(<number>)TRUNC

Drops the fractional part of a number without rounding.

TRUNC(<number>)ABS

Returns the absolute value of a number — its value without the sign.

ABS(<number>)MOD

Returns the remainder of dividing one number by another.

MOD(<dividend>, <divisor>)POWER

Raises a number to a power.

POWER(<number>, <exponent>)SQRT

Takes the square root of a number.

SQRT(<number>)WIDTH_BUCKET

Tells you which of several equal ranges a number falls into: 1, 2, 3…

WIDTH_BUCKET(<value>, <from>, <to>, <bucket count>)RANDOM

Returns a random fractional number from 0 (inclusive) to 1 (exclusive).

RANDOM()

Strings

LENGTH

Counts how many characters a string has.

LENGTH(<text>)LOWER

Converts text to lowercase.

LOWER(<text>)UPPER

Converts text to uppercase.

UPPER(<text>)INITCAP

Capitalises the first letter of every word and makes the rest lowercase.

INITCAP(<text>)TRIM

Removes spaces (or other characters) at the start and end of a string.

TRIM(<text>)SUBSTRING

Cuts out part of a string: from the position you need and of the length you need.

SUBSTRING(<text> FROM <start>)LEFT

Takes the given number of characters from the start of a string.

LEFT(<text>, <count>)RIGHT

Takes the given number of characters from the end of a string.

RIGHT(<text>, <count>)POSITION

Finds the character where a substring starts within a string.

POSITION(<substring> IN <text>)STARTS_WITH

Checks whether a string starts with the given prefix.

STARTS_WITH(<text>, <prefix>)REPLACE

Replaces every occurrence of one piece of text in a string with another.

REPLACE(<text>, <from>, <to>)TRANSLATE

Replaces individual characters with others — each with its own, in a single pass.

TRANSLATE(<text>, <characters>, <replacements>)REGEXP_LIKE

Checks whether a string matches a regular expression.

REGEXP_LIKE(<text>, '<pattern>')REGEXP_MATCHES

Extracts the parts of a string that match a regular expression.

REGEXP_MATCHES(<text>, '<pattern>')REGEXP_SUBSTR

Returns the first fragment of a string that matches a regular expression — as plain text.

REGEXP_SUBSTR(<text>, '<pattern>')REGEXP_REPLACE

Replaces the parts of a string that match a regular expression.

REGEXP_REPLACE(<text>, '<pattern>', <to>)SPLIT_PART

Splits a string by a separator and returns the part with the number you need.

SPLIT_PART(<text>, <separator>, <part number>)CONCAT

Joins several values into one string, skipping NULL.

CONCAT(<value1>, <value2>, …)FORMAT

Builds a string from a template, substituting values for %s.

FORMAT('<template>', <value1>, <value2>, …)LPAD

Pads a string on the left to the length you need.

LPAD(<text>, <length>)RPAD

Pads a string on the right to the length you need.

RPAD(<text>, <length>)REPEAT

Repeats a string the given number of times.

REPEAT(<text>, <times>)REVERSE

Reverses a string.

REVERSE(<text>)MD5

Calculates the MD5 hash of a string — 32 hexadecimal characters.

MD5(<text>)

Dates and times

DATE_TRUNC

Truncates a date and time to the start of the year, month, week, day or hour.

DATE_TRUNC('<unit>', <timestamp>)DATE_BIN

Rounds a moment down to the start of an interval of any length: 15 minutes, 6 hours, 10 days.

DATE_BIN(INTERVAL '<step>', <timestamp>, <origin>)EXTRACT

Pulls a single part out of a date or time: the year, month, day, hour.

EXTRACT(<field> FROM <date or time>)DATE_PART

Pulls a single part out of a date or time — the same as EXTRACT, but as a regular function.

DATE_PART('<field>', <date or time>)NOW

Returns the current date and time — the moment the transaction started.

NOW()CURRENT_DATE

Returns today's date — without the time.

CURRENT_DATEAGE

Calculates how much time has passed between two moments — in years, months and days.

AGE(<later timestamp>, <earlier timestamp>)MAKE_DATE

Builds a date from separate numbers: the year, month and day.

MAKE_DATE(<year>, <month>, <day>)MAKE_INTERVAL

Builds an interval from separate numbers: years, months, days, hours.

MAKE_INTERVAL(<unit> => <amount>, …)TO_CHAR

Converts a date, time or number to text in the format you need.

TO_CHAR(<value>, '<format>')TO_DATE

Converts text to a date using the given format.

TO_DATE(<text>, '<format>')

Types

CAST

Converts a value from one type to another: text to a number, text to a date, a number to text.

CAST(<value> AS <type>)TO_NUMBER

Converts text written the “human” way — with commas or a currency sign — into a number.

TO_NUMBER(<text>, '<format>')GEN_RANDOM_UUID

Generates a random UUID — a unique identifier like 3f2c9a1e-….

GEN_RANDOM_UUID()

Arrays and series

STRING_TO_ARRAY

Splits a string by a separator into an array of parts.

STRING_TO_ARRAY(<text>, <separator>)ARRAY_TO_STRING

Joins the elements of an array into one string with a separator.

ARRAY_TO_STRING(<array>, <separator>)UNNEST

Expands an array into rows — one row per element.

UNNEST(<array>)CARDINALITY

Counts how many elements an array has.

CARDINALITY(<array>)ARRAY_LENGTH

Returns the length of an array in the given dimension.

ARRAY_LENGTH(<array>, <dimension>)ARRAY_POSITION

Finds the number of the first array element with the given value.

ARRAY_POSITION(<array>, <value>)ARRAY_APPEND

Returns an array with an element added at the end.

ARRAY_APPEND(<array>, <element>)ARRAY_REMOVE

Returns an array without any elements equal to the given value.

ARRAY_REMOVE(<array>, <value>)GENERATE_SERIES

Creates rows with a sequence of numbers or points in time.

GENERATE_SERIES(<start>, <stop>)

JSON

TO_JSONB

Converts a value — a number, text, an array or a whole table row — to JSON.

TO_JSONB(<value>)JSONB_BUILD_OBJECT

Builds a JSON object from key–value pairs.

JSONB_BUILD_OBJECT(<key1>, <value1>, <key2>, <value2>, …)JSONB_AGG

Collects values from many rows into one JSON array.

JSONB_AGG(<expression>)JSONB_SET

Returns a copy of JSON with a new value at the given path.

JSONB_SET(<json>, '{<path>}', <new value>)JSONB_EACH

Expands a JSON object into rows — one row per key–value pair.

JSONB_EACH(<json object>)JSONB_OBJECT_KEYS

Returns the keys of a JSON object — one row per key.

JSONB_OBJECT_KEYS(<json object>)JSONB_ARRAY_ELEMENTS

Expands a JSON array into rows — one row per element.

JSONB_ARRAY_ELEMENTS(<json array>)JSONB_PATH_QUERY

Gets values out of JSON with an SQL/JSON path expression — including with conditions.

JSONB_PATH_QUERY(<json>, '<path>')

Full-text search

TO_TSVECTOR

Prepares text for full-text search: splits it into words and reduces them to their stems.

TO_TSVECTOR('<language config>', <text>)TO_TSQUERY

Builds a full-text search query: words with “and”, “or” and “not” conditions.

TO_TSQUERY('<language config>', '<query>')

Aggregate

COUNT

Counts rows or values that aren't NULL.

COUNT(*)SUM

Adds up values from many rows and returns the total.

SUM(<expression>)AVG

Calculates the arithmetic mean of values from many rows.

AVG(<expression>)MIN

Returns the smallest value among the rows.

MIN(<expression>)MAX

Returns the largest value among the rows.

MAX(<expression>)PERCENTILE_CONT

Finds the median or another percentile — the value below which a given share of the data lies.

PERCENTILE_CONT(<fraction>) WITHIN GROUP (ORDER BY <expression>)PERCENTILE_DISC

Finds a percentile among existing values — a median that's definitely in the data.

PERCENTILE_DISC(<fraction>) WITHIN GROUP (ORDER BY <expression>)BOOL_AND

Returns true if a condition is true for all rows.

BOOL_AND(<condition>)BOOL_OR

Returns true if a condition is true for at least one row.

BOOL_OR(<condition>)STRING_AGG

Joins values from many rows into one piece of text with a separator.

STRING_AGG(<expression>, <separator>)ARRAY_AGG

Collects values from many rows into one array.

ARRAY_AGG(<expression>)GROUPING

Shows whether a row is a ROLLUP or CUBE subtotal for a column: 1 — a subtotal, 0 — an ordinary group.

GROUPING(<column>)

Window

ROW_NUMBER

Numbers rows 1, 2, 3… in a given order — across the whole result or within each group.

ROW_NUMBER() OVER (ORDER BY <sort key>)RANK

Assigns places with ties: equal values share a place, and the next one is skipped.

RANK() OVER (ORDER BY <sort key>)DENSE_RANK

Assigns places with ties, but without gaps: 1, 2, 2, 3.

DENSE_RANK() OVER (ORDER BY <sort key>)PERCENT_RANK

Shows a row's relative position from 0 to 1: what share of the other rows comes before it.

PERCENT_RANK() OVER (ORDER BY <sort key>)CUME_DIST

Shows the share of rows that come before the current one or tie with it — from 0 to 1.

CUME_DIST() OVER (ORDER BY <sort key>)NTILE

Splits rows into the given number of groups of roughly equal size and numbers the groups.

NTILE(<group count>) OVER (ORDER BY <sort key>)LAG

Takes a value from the previous row to compare with the current one.

LAG(<expression>) OVER (ORDER BY <sort key>)LEAD

Takes a value from the next row to compare with the current one.

LEAD(<expression>) OVER (ORDER BY <sort key>)FIRST_VALUE

Takes the value from the first row of the window.

FIRST_VALUE(<expression>) OVER (ORDER BY <sort key>)LAST_VALUE

Takes the value from the last row of the window — but only with the right frame.

LAST_VALUE(<expression>) OVER (
  ORDER BY <sort key>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

NTH_VALUE

Takes the value from the Nth row of the window: the second, the third…

NTH_VALUE(<expression>, <n>) OVER (
  ORDER BY <sort key>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)