Функции и процедуры на самом деле очень похожи, и многие называют их как попало, но в Postgres это разные вещи.

Functions:

- Считают и возвращают значения
- Не управляют транзакциями
- Можно использовать внутри SELECT или WHERE
- SELECT function()

Procedures:

- Действия и батчи
- COMMIT и ROLLBACK
- Нельзя вызывать внутри SQL
- CALL procedure()

👉 @SQLPortal

Data Modeling

Мы заранее загрузили данные в таблицу calories с одним текстовым столбцом calories_count. В исходном файле пустая строка использовалась как разделитель групп, и это нужно учитывать в решении.

Теперь у нас есть таблица с одной строкой на каждую строку файла. Как сгруппировать такие данные? Группировку похожих строк обычно делают через window-функции. В нашем случае нужно «складывать» строки до тех пор, пока не встретится пустая строка, после чего начинать новую группу. Создадим псевдоколонку и будем увеличивать sequence только когда значение в calories_count пустое. Также вызовем setval() для задания начального значения, чтобы функция currval() работала.

CREATE SEQUENCE aoc;
SELECT setval('aoc', 1);
SELECT calories_count,
       CASE WHEN calories_count = ''  THEN nextval('aoc')
            ELSE currval('aoc') END
FROM calories LIMIT 10;

Результат:

calories_count | currval 
---------------+---------
 9686          |       1
 10178         |       1
 3375          |       1
 9638          |       1
 6318          |       1
 4978          |       1
 5988          |       1
 6712          |       1
               |       2
 10422         |       2

Как видно, currval меняется, когда sequence обнаруживает новую группу. Используем это свойство, чтобы посчитать сумму по каждой группе. Поскольку у нас текстовый столбец, придется привести его к int перед суммированием. Дополнительно пустая строка не может быть приведена к int, поэтому явно детектируем строку-разделитель и считаем ее значение равным 0.

👉 @SQLPortal

Group and Sum

SELECT SUM(calories_count) OVER(partition by currval) FROM
  (SELECT CASE WHEN calories_count = '' THEN 0
               ELSE calories_count :: int END AS calories_count,
          CASE WHEN calories_count = '' THEN nextval('aoc')
               ELSE currval('aoc') END
   FROM calories
  ) x LIMIT 10;

Результат:

sum  
-------
 56873
 56873
 56873
 56873
 56873
 56873
 56873
 56873
 43456
 43456

В данном случае мы используем window-функцию, поэтому сумма считается для каждой строки, но итог по группе будет одинаковым для всех строк этой группы.

👉 @SQLPortal

Дан запрос:

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC
LIMIT 5;

Вопрос: какой реальный порядок выполнения?

Варианты:

A) FROM → WHERE → SELECT → GROUP BY → HAVING → ORDER BY → LIMIT
B) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
C) FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY → LIMIT
D) WHERE → FROM → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

👉 @SQLPortal

Руководство по SQL

Это руководство охватывает различные аспекты, начиная с основ SQL и до более сложных тем

Материал дополнен практическими примерами, которые способствуют лучшему усвоению информации

Сохраняйте, чтобы не потерять

👉 @SQLPortal

Повышение производительности PostgreSQL: пошаговое руководство по использованию pg_hint_plan

Планировщик запросов PostgreSQL - это сложный инженерный механизм, обычно принимающий блестящие решения относительно того, как выполнять ваши запросы. Однако в сложных сценариях или при необычных распределениях данных вы можете знать лучший способ. Именно в таких ситуациях на помощь приходит pg_hint_plan - мощное расширение, которое позволяет вам руководить, или "советовать", планировщиком для выбора специфичного пути выполнения.

Это руководство проведет вас через весь процесс, начиная с установки pg_hint_plan из источника до использования его, чтобы принудительно выполнить сканирование индекса на большом наборе данных, демонстрируя возможности непосредственного управления производительностью вашего запроса.

👉 @SQLPortal

Совет по Postgres: используй filter вместо case when для условных агрегатов. Читается проще, выглядит более идиоматично для SQL и часто работает быстрее.

--  не делай так: громоздко и хуже читается
select
  count(case when status = 'active' then 1 end) as active_count,
  count(case when status = 'archived' then 1 end) as archived_count
from projects;

--  лучше так: чище и оптимизируется Postgres
select
  count(*) filter (where status = 'active') as active_count,
  count(*) filter (where status = 'archived') as archived_count
from projects;

👉 @SQLPortal

Нужно посчитать все строки в здоровенной таблице в Postgres?

SELECT count(*) FROM table;

Это может быть медленно. Можно спросить внутренние таблицы.

SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table';

Это оценка, но обычно довольно точная.

👉 @SQLPortal

В Postgres каждая sequence хранится как отдельная таблица. Детали по sequence можно посмотреть напрямую, делая select из этой таблицы, или через команду \d в psql, чтобы получить описание.

Как искать sequences в Postgres

1. \d таблицы, ищем sequence-колонку

postgres=# \d user_events
             Table "public.user_events"
 Column     | Type                      | Collation | Nullable | Default
------------+---------------------------+-----------+----------+--------------------------------------------------
 id         | integer                   |           | not null | nextval('user_events_id_seq'::regclass)
 data       | text                      |           |          |
 created_at | timestamp without time zone|          |          | now()

Indexes:
    "user_events_pkey" PRIMARY KEY, btree (id)

2. SELECT * из sequence-таблицы, чтобы посмотреть текущее значение

postgres=# SELECT * FROM user_events_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
     203590 |      23 | t
(1 row)

3. \d sequence-таблицы, чтобы посмотреть максимальное значение и шаг

postgres=# \d user_events_id_seq;
          Sequence "public.user_events_id_seq"
 Type    | Start | Minimum | Maximum     | Increment | Cycles? | Cache
---------+-------+---------+-------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647  |         1 | no      |     1

👉 @SQLPortal

SQL-совет:

Есть одна особенность в SQL, на которой спотыкаются многие новички (и даже часть опытных пользователей):

ORDER BY может быть чувствителен к регистру.

Большинство думает, что сортировка строк — это просто.
Алфавит. От A до Z. И всё.

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

То есть вместо:

apple
banana
cherry

Можно получить:

Apple
Banana
apple
banana

Слова те же, порядок другой.

Почему так?

Потому что некоторые базы воспринимают заглавные и строчные символы как разные значения при сортировке.

В SQLite можно отключить чувствительность к регистру через COLLATE NOCASE.

Здесь не нужно менять сами данные, можно просто сказать SQL, как их сортировать. Пример ниже.

SELECT name FROM products ORDER BY name COLLATE NOCASE;

👉 @SQLPortal

Хочешь хранить текст? Не используй char(n) или varchar(n).

В других базах это встречается постоянно, но для Postgres — не лучшая идея.

• фиксированная ширина работает медленнее
• TEXT — переменной длины и без лимита
• если нужен размер, ставь constraint и ограничивай полем, а не типом

В итоге TEXT почти всегда выигрывает по практичности и производительности.

👉 @SQLPortal

В Postgres 18 наконец-то завезли параллельную сборку GIN-индексов при CREATE INDEX. То есть индексы под full-text (tsvector) и jsonb теперь могут строиться не в один поток, а с воркерами, как это уже давно было у B-tree/BRIN.

Фишка в том, что оно автоматом упирается в ваши настройки maintenance-параллелизма: max_parallel_maintenance_workers + общий бюджет maintenance_work_mem (и от него тоже зависит, сколько воркеров вообще дадут).

Мини-чеклист, если часто пересоздаете FTS/JSON индексы:

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 8;

CREATE INDEX gin_idx ON docs USING gin (to_tsvector('simple', body));
-- или jsonb
CREATE INDEX gin_json_idx ON docs USING gin (payload);

На больших таблицах это прям приятный буст, особенно когда билд был CPU-bound, а не в потолок по диску.

👉 @SQLPortal

psql-лайфхак, который экономит кучу времени: \i (include) для запуска локального файла прямо из psql.

Пример:

\i ~/Documents/query.sql

psql откроет файл, выполнит все SQL-команды внутри, и выведет результат сразу в текущую сессию (как будто ты вручную вставил содержимое). Удобно для повторяемых запросов, миграций, быстрых дебаг-скриптов и репортов.

👉 @SQLPortal

PlanetScale показали pg_strict для Postgres.

Идея простая: это расширение, которое добавляет в Postgres “страховочную сетку” и ловит опасные запросы до того, как они реально выполнятся.

У всех бывало: хотел поправить одну строку, а всё уехало в UPDATE без WHERE. Или случайный DELETE, или “быстренько проверю миграцию” на не той базе. В итоге урон не от бага в коде, а от одной команды в psql.

Что обещает pg_strict:

- проверять запросы заранее
- блочить потенциально опасные операции
- спасать прямо на уровне базы, а не уже после инцидента

👉 @SQLPortal

У ClickHouse появился managed-сервис Postgres.

Теперь прям очевидно, почему ClickHouse так упарывались в расширение pg_clickhouse.

Конкуренция станет еще жарче: Snowflake + Crunchy, Databricks + Neon и TigerData.

Lakehouse-платформам нужны транзакционные данные, и Postgres уже должен быть провайдером номер один.

Рад видеть, как тема Postgres для аналитики получает больше внимания и инвестиций. Для Postgres это плюс.

Ссылка: https://clickhouse.com/cloud/postgres

👉 @SQLPortal

Мифическая база данных MySQL постепенно затухает...😭

Уже больше 4 месяцев в её публичном репозитории на GitHub не было ни единого коммита. Плохой сигнал.

Пауза совпала с сентябрьскими сокращениями в Oracle в команде, которая этим занимается.

Это не значит, что MySQL исчезнет. Она стоит на миллионах серверов и будет работать ещё годами.

Но это вполне ясный маркер чего-то более глубокого:
MySQL больше не конкурирует как open-source сообщество, а живёт как чисто коммерческий продукт.

Меньше прозрачности, другие приоритеты.
Обгон со стороны PostgreSQL уже неизбежен.

👉 @SQLPortal

PL/pgSQL делает PostgreSQL чем-то большим, чем просто средством для запросов

пример функции, которая принимает массив структур с данными и внутри Postgres делает цикл и инсертит каждую запись — без участия приложения.

CREATE OR REPLACE FUNCTION public.insert_user_activities (
    param_udt_storefrontactivities udt_storefront_activities[]
) RETURNS void LANGUAGE plpgsql AS $function$
DECLARE
    var_activity udt_storefront_activities;
BEGIN
    -- Insert user activities from the provided array
    IF param_udt_storefrontactivities IS NOT NULL THEN
        FOREACH var_activity IN ARRAY param_udt_storefrontactivities
        LOOP
            INSERT INTO tbl_storefront_activities (
                providerid,
                activitycode,
                userid,
                orderid,
                notes,
                createddate
            )
            VALUES (
                var_activity.providerid,
                var_activity.activitycode,
                var_activity.userid,
                var_activity.orderid,
                var_activity.notes,
                CURRENT_TIMESTAMP
            );
        END LOOP;
    END IF;
END;
$function$

Типичное приложение без этого делало бы так:

на клиенте есть массив
клиент бежит по массиву
отправляет INSERT-ы в базу
ждёт round-trip для каждого запроса

С PL/pgSQL можно перенести всю операцию внутрь базы:

— передаёшь массив один раз
— Postgres сам крутит цикл
— пишет данные
— нет лишних round-trip’ов
— меньше сетевой задержки
— транзакция проще
— логика ближе к данным

👉 @SQLPortal

Нашел интересную статью о внутреннем устройстве Postgres

👉 @SQLPortal

OUTER JOIN или внешнее соединение позволяет возвратить все строки одной или двух таблиц, которые участвуют в соединении.

Outer Join имеет следующий формальный синтаксис:

SELECT столбцы
FROM таблица1
    {LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1
    [{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]...

Перед оператором JOIN указывается одно из ключевых слов LEFT, RIGHT или FULL, которые определяют тип соединения:

LEFT: выборка будет содержать все строки из первой или левой таблицы

RIGHT: выборка будет содержать все строки из второй или правой таблицы

FULL: выборка будет содержать все строки из обеих таблиц

Перед оператором JOIN может указываться ключевое слово OUTER, но его применение необязательно. После JOIN указывается присоединяемая таблица, а затем идет условие соединения после оператора ON.

К примеру, возьмем следующие таблицы:

CREATE TABLE Products
(
    Id SERIAL PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    Company VARCHAR(20) NOT NULL,
    ProductCount INTEGER DEFAULT 0,
    Price NUMERIC NOT NULL
);
CREATE TABLE Customers
(
    Id SERIAL PRIMARY KEY,
    FirstName VARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
    Id SERIAL PRIMARY KEY,
    ProductId INTEGER NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
    CustomerId INTEGER NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
    CreatedAt DATE NOT NULL,
    ProductCount INTEGER DEFAULT 1,
    Price NUMERIC NOT NULL
);

И соединим таблицы Orders и Customers:

SELECT FirstName, CreatedAt, ProductCount, Price, ProductId 
FROM Orders LEFT JOIN Customers 
ON Orders.CustomerId = Customers.Id;

Таблица Orders является первой или левой таблицей, а таблица Customers - правой таблицей. Поэтому, так как здесь используется выборка по левой таблице, то вначале будут выбираться все строки из Orders, а затем к ним по условию Orders.CustomerId = Customers.Id будут добавляться связанные строки из Customers.

👉 @SQLPortal

JSON types in Postgres

json

* текст
* без дубликатов ключей
* не индексируется
* парсится во время выполнения, поэтому медленнее

jsonb

* бинарный формат, заранее распарсен
* без дубликатов ключей
* индексируется
* лучше по производительности запросов

👉 @SQLPortal