Пошук уроків, статей та іншого контенту
Освоїте категорії вбудованих функцій для роботи з текстом, датами, числами, JSON та агрегованими даними.
Вбудовані функції PostgreSQL — це готові операції для обробки значень у запитах. Вони дають змогу:
змінювати та аналізувати текст;
виконувати обчислення з числами;
працювати з датами й часовими інтервалами;
читати та формувати JSON;
обчислювати підсумки для груп рядків.
Функція зазвичай викликається так:
function_name(argument1, argument2)Наприклад:
SELECT lower('PostgreSQL');Результат:
postgresqlФункції можна використовувати в SELECT, WHERE, ORDER BY, GROUP BY, HAVING та інших частинах SQL-запиту.
lower(text) — перетворює текст на нижній регістр;
upper(text) — перетворює текст на верхній регістр;
initcap(text) — робить першу літеру кожного слова великою.
SELECT
lower('PostgreSQL Functions') AS lowercase_text,
upper('PostgreSQL Functions') AS uppercase_text,
initcap('postgresql functions') AS title_text;Результат:
lowercase_text | uppercase_text | title_text
------------------------+-------------------------+----------------------
postgresql functions | POSTGRESQL FUNCTIONS | Postgresql Functionslength(text) — повертає кількість символів;
char_length(text) — синонім length для тексту;
left(text, count) — повертає перші count символів;
right(text, count) — повертає останні count символів;
trim(text) — прибирає пробіли на початку та в кінці;
ltrim(text) і rtrim(text) — прибирають пробіли лише з одного боку.
SELECT
length(' PostgreSQL ') AS original_length,
length(trim(' PostgreSQL ')) AS trimmed_length,
left('PostgreSQL', 5) AS first_five,
right('PostgreSQL', 3) AS last_three;length рахує символи, а не байти. Це важливо під час роботи з текстом у кодуванні UTF-8:
SELECT length('Україна') AS character_count;Для об’єднання рядків можна використовувати оператор ||:
SELECT 'PostgreSQL' || ' ' || 'Functions' AS full_name;Функція concat об’єднує аргументи та ігнорує NULL:
SELECT concat('User: ', 'Olena', ', city: ', NULL) AS description;Результат:
User: Olena, city:Для об’єднання значень із роздільником використовується concat_ws:
SELECT concat_ws(', ', 'Olena', 'Kyiv', NULL, 'Developer') AS profile;Результат:
Olena, Kyiv, DeveloperОператор || має іншу поведінку: якщо один з операндів дорівнює NULL, результат також буде NULL.
SELECT 'User: ' || NULL AS result;replace(source, from, to) — замінює всі входження підрядка;
position(substring IN string) — повертає позицію підрядка;
strpos(string, substring) — також повертає позицію підрядка;
substring(string FROM start FOR count) — витягує частину тексту.
SELECT
replace('PostgreSQL is powerful', 'powerful', 'flexible') AS replaced_text,
position('SQL' IN 'PostgreSQL') AS sql_position,
substring('developer@example.com' FROM 1 FOR 8) AS username;У PostgreSQL позиції символів починаються з 1, а не з 0.
Функція split_part повертає частину рядка, розділеного вказаним символом:
SELECT
split_part('developer@example.com', '@', 1) AS username,
split_part('developer@example.com', '@', 2) AS domain;Для регулярних виразів використовують regexp_replace. Наприклад, нормалізуємо номер телефону, залишивши лише цифри:
SELECT regexp_replace('+38 (067) 123-45-67', '[^0-9]', '', 'g') AS phone;Третій аргумент — текст заміни, а прапорець 'g' означає замінити всі збіги, а не лише перший.
PostgreSQL має такі функції:
CURRENT_DATE — поточна дата;
CURRENT_TIME — поточний час;
CURRENT_TIMESTAMP — поточні дата й час;
now() — поточні дата й час.
SELECT
CURRENT_DATE AS today,
CURRENT_TIME AS current_time,
CURRENT_TIMESTAMP AS current_timestamp,
now() AS current_timestamp_again;CURRENT_TIMESTAMP і now() у межах однієї транзакції повертають узгоджене значення часу початку транзакції.
До дати або часу можна додавати інтервали:
SELECT
DATE '2025-01-15' + INTERVAL '10 days' AS after_ten_days,
TIMESTAMP '2025-01-15 10:30:00' + INTERVAL '2 hours' AS after_two_hours,
DATE '2025-01-15' - DATE '2025-01-01' AS days_between;Результат віднімання двох значень типу date — ціле число днів.
Інтервал можна відняти від дати:
SELECT DATE '2025-03-31' - INTERVAL '1 month' AS previous_month;Для складніших правил роботи з календарем PostgreSQL використовує календарну семантику інтервалів, тому результат для місяців може відрізнятися від простого віднімання фіксованої кількості днів.
Функція extract витягує окрему частину дати або часу:
SELECT
extract(year FROM TIMESTAMP '2025-08-20 14:35:10') AS year,
extract(month FROM TIMESTAMP '2025-08-20 14:35:10') AS month,
extract(day FROM TIMESTAMP '2025-08-20 14:35:10') AS day,
extract(hour FROM TIMESTAMP '2025-08-20 14:35:10') AS hour,
extract(isodow FROM TIMESTAMP '2025-08-20 14:35:10') AS weekday;isodow повертає номер дня тижня від 1 для понеділка до 7 для неділі.
Також можна використати date_part:
SELECT date_part('year', DATE '2025-08-20') AS year;date_trunc обнуляє менш значущі частини дати:
SELECT
date_trunc('year', TIMESTAMP '2025-08-20 14:35:10') AS start_of_year,
date_trunc('month', TIMESTAMP '2025-08-20 14:35:10') AS start_of_month,
date_trunc('day', TIMESTAMP '2025-08-20 14:35:10') AS start_of_day;Це особливо корисно для групування подій за місяцем або днем:
SELECT
date_trunc('month', created_at) AS month,
count(*) AS orders_count
FROM orders
GROUP BY date_trunc('month', created_at)
ORDER BY month;Функція to_char перетворює дату або число на текст за заданим шаблоном:
SELECT to_char(
TIMESTAMP '2025-08-20 14:35:10',
'DD.MM.YYYY HH24:MI'
) AS formatted_date;Поширені шаблони:
YYYY — рік;
MM — місяць;
DD — день;
HH24 — година у 24-годинному форматі;
MI — хвилина;
SS — секунда.
to_char призначена для відображення значень. Не варто перетворювати дату на текст, якщо над нею ще потрібно виконувати порівняння або сортування як над датою.
PostgreSQL підтримує звичайні арифметичні оператори:
SELECT
10 + 3 AS addition,
10 - 3 AS subtraction,
10 * 3 AS multiplication,
10.0 / 3 AS division,
10 % 3 AS remainder,
2 ^ 3 AS power;Оператор / залежить від типів аргументів. Для цілих чисел результатом буде цілочисельне ділення:
SELECT
5 / 2 AS integer_division,
5.0 / 2 AS decimal_division;Результат:
integer_division | decimal_division
-----------------+-----------------
2 | 2.5000000000000000Для явного цілочисельного ділення можна використати div:
SELECT div(5, 2) AS quotient, mod(5, 2) AS remainder;round(number, digits) — округлює число;
ceil(number) або ceiling(number) — округлює вгору;
floor(number) — округлює вниз;
abs(number) — повертає абсолютне значення;
sign(number) — повертає -1, 0 або 1.
SELECT
round(123.4567, 2) AS rounded,
ceil(12.01) AS rounded_up,
floor(12.99) AS rounded_down,
abs(-42) AS absolute_value,
sign(-17) AS number_sign;Тип результату round залежить від типу аргументу. Для фінансових значень зазвичай використовують numeric, а не real або double precision, щоб уникати небажаних похибок двійкової арифметики.
Функції greatest і least порівнюють кілька значень в одному рядку:
SELECT
greatest(10, 25, 7) AS maximum_value,
least(10, 25, 7) AS minimum_value;У PostgreSQL greatest і least ігнорують аргументи NULL, якщо серед інших аргументів є не-NULL значення. Якщо всі аргументи — NULL, результат буде NULL.
PostgreSQL підтримує типи json і jsonb.
json зберігає JSON-текст у початковому вигляді;
jsonb зберігає розібрану двійкову структуру та зазвичай зручніший для пошуку й обробки.
Для більшості прикладних задач використовують jsonb.
Оператори:
-> повертає JSON-значення;
->> повертає значення як текст;
#> звертається до вкладеного JSON-шляху;
#>> повертає вкладений шлях як текст.
SELECT
profile -> 'name' AS name_as_json,
profile ->> 'name' AS name_as_text,
profile #> '{address,city}' AS city_as_json,
profile #>> '{address,city}' AS city_as_text
FROM (
VALUES (
'{
"name": "Olena",
"role": "developer",
"address": {
"city": "Kyiv"
}
}'::jsonb
)
) AS data(profile);Різниця між -> і ->> важлива:
SELECT
('{"age": 30}'::jsonb -> 'age') AS json_value,
('{"age": 30}'::jsonb ->> 'age') AS text_value;Перше значення має тип jsonb, друге — тип text.
Якщо потрібно порівняти числове значення, перетворіть текст до числа:
SELECT *
FROM users
WHERE (profile ->> 'age')::integer >= 18;Функції jsonb_build_object і jsonb_build_array створюють JSON-значення:
SELECT jsonb_build_object(
'id', 42,
'name', 'Olena',
'skills', jsonb_build_array('SQL', 'JavaScript', 'Docker')
) AS profile;Для перетворення рядка або іншого значення на JSON можна використати to_jsonb:
SELECT to_jsonb(42) AS number_json;Оператор ? перевіряє наявність ключа в об’єкті:
SELECT
'{"name": "Olena", "role": "developer"}'::jsonb ? 'role'
AS has_role;Оператори @> і <@ перевіряють, чи містить один JSON-документ інший:
SELECT
'{"name": "Olena", "role": "developer"}'::jsonb
@> '{"role": "developer"}'::jsonb
AS contains_role;Такий вираз часто використовують у фільтрах:
SELECT *
FROM users
WHERE profile @> '{"role": "developer"}'::jsonb;jsonb_array_elements розгортає JSON-масив у набір рядків:
SELECT skill
FROM jsonb_array_elements_text(
'["SQL", "JavaScript", "Docker"]'::jsonb
) AS skills(skill);Функція jsonb_array_elements_text повертає елементи як текст. Варіант jsonb_array_elements повертає елементи як jsonb.
Агреговані функції обробляють кілька рядків і повертають одне значення для всієї вибірки або кожної групи.
Найпоширеніші функції:
count — кількість рядків або не-NULL значень;
sum — сума;
avg — середнє значення;
min — мінімальне значення;
max — максимальне значення;
string_agg — об’єднання текстових значень;
jsonb_agg — об’єднання значень у JSON-масив.
Приклад із тимчасовим набором даних:
WITH sales(product, category, amount) AS (
VALUES
('Keyboard', 'hardware', 120.50::numeric),
('Mouse', 'hardware', 45.00::numeric),
('SQL Course', 'education', 200.00::numeric),
('Docker Course', 'education', 150.00::numeric)
)
SELECT
count(*) AS sales_count,
sum(amount) AS total_amount,
round(avg(amount), 2) AS average_amount,
min(amount) AS minimum_amount,
max(amount) AS maximum_amount
FROM sales;count(*) і count(column)count(*) рахує всі рядки:
SELECT count(*) FROM users;count(column) рахує лише рядки, де вказаний стовпець не є NULL:
SELECT
count(*) AS all_users,
count(email) AS users_with_email
FROM users;Тому ці два вирази можуть повертати різні значення.
Агрегати часто використовують разом із GROUP BY:
WITH sales(category, amount) AS (
VALUES
('hardware', 120.50::numeric),
('hardware', 45.00::numeric),
('education', 200.00::numeric),
('education', 150.00::numeric)
)
SELECT
category,
count(*) AS sales_count,
sum(amount) AS total_amount,
round(avg(amount), 2) AS average_amount
FROM sales
GROUP BY category
ORDER BY total_amount DESC;Усі звичайні стовпці в SELECT, які не є агрегатами, мають бути присутні в GROUP BY.
Конструкція FILTER дає змогу застосувати умову до конкретного агрегату:
WITH orders(status, amount) AS (
VALUES
('paid', 100::numeric),
('paid', 250::numeric),
('cancelled', 80::numeric),
('pending', 120::numeric)
)
SELECT
count(*) AS all_orders,
count(*) FILTER (WHERE status = 'paid') AS paid_orders,
sum(amount) FILTER (WHERE status = 'paid') AS paid_amount
FROM orders;Це часто зрозуміліше за кілька окремих підзапитів або умовне додавання через CASE.
string_agg об’єднує текстові значення через вказаний роздільник:
WITH skills(user_name, skill) AS (
VALUES
('Olena', 'SQL'),
('Olena', 'PostgreSQL'),
('Olena', 'Docker'),
('Andrii', 'JavaScript')
)
SELECT
user_name,
string_agg(skill, ', ' ORDER BY skill) AS skills
FROM skills
GROUP BY user_name;ORDER BY усередині string_agg визначає порядок елементів у результаті.
jsonb_agg формує JSON-масив:
WITH skills(user_name, skill) AS (
VALUES
('Olena', 'SQL'),
('Olena', 'PostgreSQL'),
('Olena', 'Docker')
)
SELECT
user_name,
jsonb_agg(skill ORDER BY skill) AS skills
FROM skills
GROUP BY user_name;NULLNULL означає відсутнє або невідоме значення. Це не порожній рядок і не число 0.
Для перевірки NULL використовують:
SELECT *
FROM users
WHERE email IS NULL;А не:
-- Така перевірка некоректна для NULL
WHERE email = NULL;Функція coalesce повертає перше значення, яке не є NULL:
SELECT
coalesce(NULL, NULL, 'default value') AS result;Практичний приклад:
SELECT
id,
coalesce(display_name, 'Без імені') AS display_name
FROM users;Функція nullif(value1, value2) повертає NULL, якщо два значення рівні:
SELECT nullif(0, 0) AS result;Вона може запобігти діленню на нуль:
SELECT revenue / nullif(order_count, 0) AS average_revenue
FROM daily_statistics;Якщо order_count дорівнює нулю, знаменник стає NULL, а не викликає помилку.
Наступний запит поєднує роботу з текстом, датами, числами, JSON та агрегатами:
WITH orders AS (
SELECT *
FROM (
VALUES
(
1,
' Olena ',
TIMESTAMP '2025-01-15 10:30:00',
120.50::numeric,
'{"city": "Kyiv", "tags": ["new", "priority"]}'::jsonb
),
(
2,
'Andrii',
TIMESTAMP '2025-01-20 14:15:00',
75.00::numeric,
'{"city": "Lviv", "tags": ["new"]}'::jsonb
),
(
3,
'Olena',
TIMESTAMP '2025-02-05 09:00:00',
200.00::numeric,
'{"city": "Kyiv", "tags": ["priority"]}'::jsonb
)
) AS data(id, customer_name, created_at, amount, details)
)
SELECT
date_trunc('month', created_at)::date AS order_month,
initcap(trim(customer_name)) AS customer_name,
details ->> 'city' AS city,
count(*) AS orders_count,
round(sum(amount), 2) AS total_amount,
string_agg(details ->> 'city', ', ' ORDER BY created_at) AS cities
FROM orders
GROUP BY
date_trunc('month', created_at)::date,
initcap(trim(customer_name)),
details ->> 'city'
ORDER BY order_month, customer_name;У цьому запиті:
trim прибирає зайві пробіли з імені.
initcap нормалізує регістр.
date_trunc групує замовлення за місяцем.
->> отримує місто з JSON як текст.
count рахує замовлення.
sum обчислює загальну суму.
round округлює результат.
string_agg об’єднує міста в один рядок.
NULL через =Неправильно:
WHERE email = NULLПравильно:
WHERE email IS NULLабо:
WHERE email IS NOT NULLSELECT 7 / 2;Результат — 3, оскільки обидва операнди мають цілочисельний тип.
Для десяткового результату використовуйте приведення типу:
SELECT 7::numeric / 2;-> замість ->>Якщо потрібен текст для порівняння, конкатенації або приведення типу, використовуйте ->>:
SELECT (profile ->> 'age')::integer
FROM users;Оператор -> повертає JSON-значення, тому його не завжди можна безпосередньо використати як текст або число.
Не варто фільтрувати дати через форматований рядок:
-- Небажаний підхід
WHERE to_char(created_at, 'YYYY-MM') = '2025-01'Краще використовувати діапазон дат:
WHERE created_at >= TIMESTAMP '2025-01-01'
AND created_at < TIMESTAMP '2025-02-01'Таке порівняння зберігає тип дати та краще відповідає роботі з індексами.
Цей запит некоректний:
SELECT category, sum(amount)
FROM sales;Якщо category не є агрегатом, його потрібно додати до GROUP BY:
SELECT category, sum(amount)
FROM sales
GROUP BY category;Текстові функції, як-от lower, trim, replace, substring і split_part, допомагають очищати та аналізувати рядки.
Для об’єднання тексту використовують concat, concat_ws або оператор ||.
Функції extract, date_trunc, to_char і арифметика з INTERVAL спрощують роботу з датами.
round, ceil, floor, abs, greatest і least призначені для числових обчислень.
Оператори ->, ->>, #> і #>> дають змогу отримувати значення з JSON.
jsonb_build_object, jsonb_build_array і jsonb_array_elements_text використовують для формування та розгортання JSON.
Агрегати count, sum, avg, min, max, string_agg і jsonb_agg обробляють набори рядків.
FILTER дає змогу обчислювати агрегати лише для рядків, що відповідають умові.
Для безпечної роботи з відсутніми значеннями використовуйте IS NULL, coalesce і nullif.