Довідник функцій PostgreSQL

Коротко про кожну функцію: що робить, як викликати, приклади, які можна змінити й запустити просто тут, і на що звернути увагу, щоб не спіткнутися.

Це основні функції, яких вистачає для більшості запитів. Усі інші — в офіційній документації PostgreSQL(відкриється в новій вкладці).

/

Умовні

COALESCE

Повертає перше значення зі списку, яке не NULL.

COALESCE(<значення1>, <значення2>, …)NULLIF

Повертає NULL, якщо два значення рівні, а інакше — перше значення.

NULLIF(<значення>, <з чим порівняти>)GREATEST

Повертає найбільше зі списку значень.

GREATEST(<значення1>, <значення2>, …)LEAST

Повертає найменше зі списку значень.

LEAST(<значення1>, <значення2>, …)CASE

Повертає різні значення залежно від умов — «якщо… то… інакше» в SQL.

CASE WHEN <умова> THEN <результат> … ELSE <інакше> END

Числа

ROUND

Округлює число до цілого або до потрібної кількості знаків після коми.

ROUND(<число>)CEIL

Округлює число вгору — до найближчого цілого, не меншого за нього.

CEIL(<число>)FLOOR

Округлює число вниз — до найближчого цілого, не більшого за нього.

FLOOR(<число>)TRUNC

Відкидає дробову частину числа без округлення.

TRUNC(<число>)ABS

Повертає модуль числа — його значення без знака.

ABS(<число>)MOD

Повертає остачу від ділення одного числа на інше.

MOD(<ділене>, <дільник>)POWER

Підносить число до степеня.

POWER(<число>, <степінь>)SQRT

Добуває квадратний корінь із числа.

SQRT(<число>)WIDTH_BUCKET

Каже, в який із рівних діапазонів потрапляє число: 1, 2, 3…

WIDTH_BUCKET(<значення>, <від>, <до>, <кількість кошиків>)RANDOM

Повертає випадкове дробове число від 0 (включно) до 1 (не включно).

RANDOM()

Рядки

LENGTH

Рахує, скільки символів у рядку.

LENGTH(<текст>)LOWER

Переводить текст у нижній регістр.

LOWER(<текст>)UPPER

Переводить текст у верхній регістр.

UPPER(<текст>)INITCAP

Робить першу літеру кожного слова великою, а решту — малими.

INITCAP(<текст>)TRIM

Прибирає пробіли (або інші символи) на початку й у кінці рядка.

TRIM(<текст>)SUBSTRING

Вирізає частину рядка: з потрібної позиції й потрібної довжини.

SUBSTRING(<текст> FROM <початок>)LEFT

Бере задану кількість символів з початку рядка.

LEFT(<текст>, <кількість>)RIGHT

Бере задану кількість символів з кінця рядка.

RIGHT(<текст>, <кількість>)POSITION

Знаходить, з якого символу в рядку починається підрядок.

POSITION(<підрядок> IN <текст>)STARTS_WITH

Перевіряє, чи починається рядок із заданого префікса.

STARTS_WITH(<текст>, <префікс>)REPLACE

Замінює в рядку всі входження одного тексту на інший.

REPLACE(<текст>, <що замінити>, <на що>)TRANSLATE

Замінює окремі символи на інші — кожен на свій, за один прохід.

TRANSLATE(<текст>, <які символи>, <на які>)REGEXP_LIKE

Перевіряє, чи підходить рядок під регулярний вираз.

REGEXP_LIKE(<текст>, '<шаблон>')REGEXP_MATCHES

Витягає з рядка частини, що підходять під регулярний вираз.

REGEXP_MATCHES(<текст>, '<шаблон>')REGEXP_SUBSTR

Повертає перший фрагмент рядка, що підходить під регулярний вираз, — звичайним текстом.

REGEXP_SUBSTR(<текст>, '<шаблон>')REGEXP_REPLACE

Замінює частини рядка, що підходять під регулярний вираз.

REGEXP_REPLACE(<текст>, '<шаблон>', <на що>)SPLIT_PART

Ділить рядок за роздільником і повертає частину з потрібним номером.

SPLIT_PART(<текст>, <роздільник>, <номер>)CONCAT

Склеює кілька значень в один рядок, пропускаючи NULL.

CONCAT(<значення1>, <значення2>, …)FORMAT

Збирає рядок за шаблоном, підставляючи значення замість %s.

FORMAT('<шаблон>', <значення1>, <значення2>, …)LPAD

Доповнює рядок зліва до потрібної довжини.

LPAD(<текст>, <довжина>)RPAD

Доповнює рядок справа до потрібної довжини.

RPAD(<текст>, <довжина>)REPEAT

Повторює рядок задану кількість разів.

REPEAT(<текст>, <скільки разів>)REVERSE

Перевертає рядок задом наперед.

REVERSE(<текст>)MD5

Рахує MD5-хеш рядка — 32 шістнадцяткові символи.

MD5(<текст>)

Дати й час

DATE_TRUNC

Обрізає дату й час до початку року, місяця, тижня, дня чи години.

DATE_TRUNC('<одиниця>', <момент>)DATE_BIN

Округлює момент часу вниз до початку інтервалу довільної довжини: 15 хвилин, 6 годин, 10 днів.

DATE_BIN(INTERVAL '<крок>', <момент>, <точка відліку>)EXTRACT

Дістає з дати чи часу окрему частину: рік, місяць, день, годину.

EXTRACT(<частина> FROM <дата або час>)DATE_PART

Дістає з дати чи часу окрему частину — те саме, що EXTRACT, але звичайною функцією.

DATE_PART('<частина>', <дата або час>)NOW

Повертає поточні дату й час — момент, коли почалася транзакція.

NOW()CURRENT_DATE

Повертає сьогоднішню дату — без часу.

CURRENT_DATEAGE

Рахує, скільки часу минуло між двома моментами, — у роках, місяцях і днях.

AGE(<пізніший момент>, <раніший момент>)MAKE_DATE

Складає дату з окремих чисел: року, місяця й дня.

MAKE_DATE(<рік>, <місяць>, <день>)MAKE_INTERVAL

Складає інтервал з окремих чисел: років, місяців, днів, годин.

MAKE_INTERVAL(<одиниця> => <кількість>, …)TO_CHAR

Перетворює дату, час або число на текст у потрібному форматі.

TO_CHAR(<значення>, '<формат>')TO_DATE

Перетворює текст на дату за вказаним форматом.

TO_DATE(<текст>, '<формат>')

Типи

CAST

Перетворює значення з одного типу в інший: текст на число, текст на дату, число на текст.

CAST(<значення> AS <тип>)TO_NUMBER

Перетворює на число текст, записаний «по-людськи»: з комами, знаком валюти.

TO_NUMBER(<текст>, '<формат>')GEN_RANDOM_UUID

Створює випадковий UUID — унікальний ідентифікатор на кшталт 3f2c9a1e-….

GEN_RANDOM_UUID()

Масиви й серії

STRING_TO_ARRAY

Розбиває рядок за роздільником на масив частин.

STRING_TO_ARRAY(<текст>, <роздільник>)ARRAY_TO_STRING

Склеює елементи масиву в один рядок через роздільник.

ARRAY_TO_STRING(<масив>, <роздільник>)UNNEST

Розгортає масив у рядки — по рядку на кожен елемент.

UNNEST(<масив>)CARDINALITY

Рахує, скільки елементів у масиві.

CARDINALITY(<масив>)ARRAY_LENGTH

Повертає довжину масиву в заданому вимірі.

ARRAY_LENGTH(<масив>, <вимір>)ARRAY_POSITION

Знаходить номер першого елемента масиву з заданим значенням.

ARRAY_POSITION(<масив>, <значення>)ARRAY_APPEND

Повертає масив із доданим у кінець елементом.

ARRAY_APPEND(<масив>, <елемент>)ARRAY_REMOVE

Повертає масив без усіх елементів із заданим значенням.

ARRAY_REMOVE(<масив>, <значення>)GENERATE_SERIES

Створює рядки з послідовністю чисел або моментів часу.

GENERATE_SERIES(<початок>, <кінець>)

JSON

TO_JSONB

Перетворює значення — число, текст, масив чи цілий рядок таблиці — на JSON.

TO_JSONB(<значення>)JSONB_BUILD_OBJECT

Складає JSON-об'єкт із пар «ключ — значення».

JSONB_BUILD_OBJECT(<ключ1>, <значення1>, <ключ2>, <значення2>, …)JSONB_AGG

Збирає значення з багатьох рядків в один JSON-масив.

JSONB_AGG(<вираз>)JSONB_SET

Повертає копію JSON з новим значенням за вказаним шляхом.

JSONB_SET(<json>, '{<шлях>}', <нове значення>)JSONB_EACH

Розгортає JSON-об'єкт у рядки — по рядку на кожну пару «ключ — значення».

JSONB_EACH(<json-об'єкт>)JSONB_OBJECT_KEYS

Повертає ключі JSON-об'єкта — по рядку на кожен.

JSONB_OBJECT_KEYS(<json-об'єкт>)JSONB_ARRAY_ELEMENTS

Розгортає JSON-масив у рядки — по рядку на кожен елемент.

JSONB_ARRAY_ELEMENTS(<json-масив>)JSONB_PATH_QUERY

Дістає з JSON значення за виразом SQL/JSON path — зокрема з умовами.

JSONB_PATH_QUERY(<json>, '<шлях>')

Повнотекстовий пошук

TO_TSVECTOR

Готує текст до повнотекстового пошуку: розбиває на слова й зводить їх до основи.

TO_TSVECTOR('<мовна конфігурація>', <текст>)TO_TSQUERY

Складає запит для повнотекстового пошуку: слова з умовами «і», «або», «не».

TO_TSQUERY('<мовна конфігурація>', '<запит>')

Агрегатні

COUNT

Рахує рядки або значення, які не NULL.

COUNT(*)SUM

Додає значення з багатьох рядків і повертає суму.

SUM(<вираз>)AVG

Рахує середнє арифметичне значень із багатьох рядків.

AVG(<вираз>)MIN

Повертає найменше значення серед рядків.

MIN(<вираз>)MAX

Повертає найбільше значення серед рядків.

MAX(<вираз>)PERCENTILE_CONT

Знаходить медіану чи інший перцентиль — значення, нижче якого лежить задана частка даних.

PERCENTILE_CONT(<частка>) WITHIN GROUP (ORDER BY <вираз>)PERCENTILE_DISC

Знаходить перцентиль серед наявних значень — медіану, яка точно є в даних.

PERCENTILE_DISC(<частка>) WITHIN GROUP (ORDER BY <вираз>)BOOL_AND

Повертає true, якщо умова правдива для всіх рядків.

BOOL_AND(<умова>)BOOL_OR

Повертає true, якщо умова правдива хоча б для одного рядка.

BOOL_OR(<умова>)STRING_AGG

Склеює значення з багатьох рядків в один текст через роздільник.

STRING_AGG(<вираз>, <роздільник>)ARRAY_AGG

Збирає значення з багатьох рядків в один масив.

ARRAY_AGG(<вираз>)GROUPING

Показує, чи є рядок підсумком ROLLUP чи CUBE для стовпця: 1 — підсумок, 0 — звичайна група.

GROUPING(<стовпець>)

Віконні

ROW_NUMBER

Нумерує рядки 1, 2, 3… у заданому порядку — у всьому результаті або в кожній групі.

ROW_NUMBER() OVER (ORDER BY <критерій>)RANK

Ставить місця з урахуванням рівних: однакові значення ділять місце, а наступне пропускається.

RANK() OVER (ORDER BY <критерій>)DENSE_RANK

Ставить місця з урахуванням рівних, але без пропусків: 1, 2, 2, 3.

DENSE_RANK() OVER (ORDER BY <критерій>)PERCENT_RANK

Показує відносне місце рядка від 0 до 1: яка частка інших рядків стоїть перед ним.

PERCENT_RANK() OVER (ORDER BY <критерій>)CUME_DIST

Показує частку рядків, що стоять перед поточним або рівні з ним, — від 0 до 1.

CUME_DIST() OVER (ORDER BY <критерій>)NTILE

Ділить рядки на задану кількість груп приблизно однакового розміру й нумерує групи.

NTILE(<кількість груп>) OVER (ORDER BY <критерій>)LAG

Бере значення з попереднього рядка, щоб порівняти з поточним.

LAG(<вираз>) OVER (ORDER BY <критерій>)LEAD

Бере значення з наступного рядка, щоб порівняти з поточним.

LEAD(<вираз>) OVER (ORDER BY <критерій>)FIRST_VALUE

Бере значення з першого рядка вікна.

FIRST_VALUE(<вираз>) OVER (ORDER BY <критерій>)LAST_VALUE

Бере значення з останнього рядка вікна — але лише з правильною рамкою.

LAST_VALUE(<вираз>) OVER (
  ORDER BY <критерій>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

NTH_VALUE

Бере значення з N-го рядка вікна: другого, третього…

NTH_VALUE(<вираз>, <номер>) OVER (
  ORDER BY <критерій>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)