14137
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
Нужно быстро выгрузить данные? \copy записывает результат любого запроса в локальный файл.
\copy (SELECT * FROM users WHERE created_at > now() - interval '7 days') TO '~/new_users.csv' WITH CSV HEADER
Вы можете использовать ORDER BY по столбцам, которых нет в SELECT-списке запроса.
...за исключением случая:
SELECT DISTINCT this FROM t ORDER BY that -- некорректно!
DISTINCT удаляет дубликаты из выбранных значений и формирует новую таблицу,SELECT
Сравнение с предыдущим. Решение.
Решение: использование DATEDIFF: эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.
DATEDIFF
Если сформулировать обычным языком следующий запрос, то окажется, что он выражает следующую идею: нужно выбрать такие идентификаторы, чтобы температура, соответствующая представляемым ими датам, была бы больше, чем температура на «вчерашние» по отношению к ним даты.
SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1
Руководство по стилю SQL
Последовательное руководство по стилю кода SQL для обеспечения разборчивости и сопровождения проектов
⛓ Ссылка: тык
👉 @SQLPortal
Перенаправляете вывод psql куда-то ещё? В psql есть настройки, позволяющие сократить вывод до одиночных строк данных.psql -qtA
- -q — тихий режим
- -t — только строки (tuples only)
- -A — без выравнивания (unaligned)
👉 @SQLPortal
Вчера мы говорили о функциях pg_get_*. Чтобы получить отформатированный SQL, передайте true вместо одной длинной нечитаемой строки.pg_get_viewdef — представленияpg_get_indexdef — индексыpg_get_constraintdef — ограничения
SELECT pg_get_viewdef('active_orders'::regclass, true);
Функции pg_get_* — это встроенные функции интроспекции в Postgres.pg_get_viewdef — «Покажи SQL, лежащий за этим представлением»pg_get_indexdef — «Покажи CREATE INDEX для этого индекса»pg_get_constraintdef — «Покажи, как определён этот внешний ключ / check-ограничение»
👉 @SQLPortal
Вопрос по SQL
Нужно получить:
- все товары дешевле 100
- плюс товары из категории Electronics дешевле 500
Какое условие корректно вернет такой результат?
A)
price < 100 AND category = 'Electronics' OR price < 500
price < 100 OR (category = 'Electronics' AND price < 500)
(price < 100 OR category = 'Electronics') AND price < 500
Open-source инструмент Text2SQL, который превращает запросы на естественном языке в SQL, используя понимание схемы на базе графа. Задавай вопросы к базе простым языком, а QueryWeaver сам сделает всю склейку и соберет нужный SQL.
https://github.com/FalkorDB/QueryWeaver
👉 @SQLPortal
Краткая шпаргалка по SQL с примерами запросов
https://github.com/enochtangg/quick-SQL-cheatsheet
👉 @SQLPortal
JSONB-колонки Postgres и TOAST: гайд по производительности
Postgres даёт огромный набор фич на уровне пользователя под кучу разных кейсов, а под капотом там довольно сложная абстракция.
Работа с API и массивами в типе jsonb в последнее время стала заметно популярнее, и хранить куски данных приложения в jsonb превратилось в распространённый паттерн.
Но зачем вообще “нарезать” JSON-объект на строки и колонки, а потом собирать его обратно, чтобы отправить клиенту?
Ответ: эффективность. Postgres быстрее всего работает со строками и колонками, а когда ты прячешь структуру данных внутрь JSON, движку сложнее разогнаться на максимум, как он мог бы.
👉 @SQLPortal
В Postgres есть unlogged-таблицы: для них отключается Write-Ahead Logging (WAL). За счёт этого запись становится заметно быстрее, но очевидная цена — такие таблицы не реплицируются через WAL и после крэша/перезапуска их содержимое не восстанавливается (по сути, данные считаются временными). В некоторых сценариях этот размен реально оправдан.
👉 @SQLPortal
Какой SQL-запрос работает быстрее?
SELECT * FROM orders
WHERE status = 'NEW'
OR status = 'PENDING'
OR status = 'PROCESSING';
SELECT * FROM orders
WHERE status IN ('NEW', 'PENDING', 'PROCESSING');
В Postgres можно делать функциональные индексы для запросов, которые приходят в конкретном формате. Например, для TIMESTAMP, когда в запросе фильтрация идет только по дате:
CREATE INDEX idx_orders_dt_only ON orders ((created_at::date));
SELECT count(*) FROM orders WHERE created_at::date = '2024-05-20';
SQL-приём, о котором мало кто знает
Знал ли ты, что внутри транзакции можно создавать контрольные точки с помощью SAVEPOINT? Вместо сценария, где при ошибке теряется вся работа.
SAVEPOINT позволяет зафиксировать точку внутри транзакции, к которой можно откатиться. Обычный откат отменяет всю транзакцию, а откат к SAVEPOINT возвращает состояние только до этой точки.
SAVEPOINT — это как контрольные точки в трубопроводе. Если проблема возникает дальше по потоку, можно локализовать последствия, вернувшись к последней стабильной точке, не останавливая всю систему.
Следующий код откатывает всё:
BEGIN;
INSERT INTO orders VALUES (1, 'Laptop');
INSERT INTO payments VALUES (1, 'FAILED');
-- Здесь что-то пошло не так
ROLLBACK; -- Удалит И заказ, И платёж
BEGIN;
INSERT INTO orders VALUES (1, 'Laptop');
-- Создаём контрольную точку
SAVEPOINT before_payment;
INSERT INTO payments VALUES (1, 'FAILED');
-- Платёж не прошёл — откатываем только его
ROLLBACK TO SAVEPOINT before_payment;
-- Пробуем альтернативный способ оплаты
INSERT INTO payments VALUES (1, 'SUCCESS');
COMMIT;
«Его открывал мой муж. Поэтому и покинул нас в 45», - добавила она
👉 @SQLPortal
pgcli — интерфейс командной строки для PostgreSQL
Его главные особенности — автодополнение и подсветка синтаксиса. Это позволяет упростить взаимодействие с базами данных.
Есть аналог для MySQL.
⛓ Ссылка: тык
👉 @SQLPortal
Как сгруппировать строки в пачки (batch), где сумма не превышает заданное значение — прямо в Oracle SQL
Используйте MATCH_RECOGNIZE:
SELECT ... FROM ... MATCH_RECOGNIZE (
ORDER BY ...
MEASURES ...
PATTERN ( total+ )
DEFINE total AS SUM ( ... ) <= ...
)
Этот репозиторий содержит все, что вам нужно, чтобы прокачать свои навыки работы с SQL
Более 100 упражнений и примеров по SQL.
⛓ Ссылка: тык
👉 @SQLPortal
Руководство по SQL
Это руководство охватывает различные аспекты, начиная с основ SQL и до более сложных тем
Материал дополнен практическими примерами, которые способствуют лучшему усвоению информации
Сохраняйте, чтобы не потерять
👉 @SQLPortal
Если используешь COUNT вместе с CASE, не ставь ELSE 0
Пример:
SELECT
Department,
COUNT(CASE WHEN Status = 'Active' THEN EmployeeID ELSE 0 END) AS ActiveCount
FROM Employees
GROUP BY Department;
ELSE 0, COUNT начинает считать нули как значения. COUNT игнорирует только NULL, а ноль это не NULL. Поэтому если в отделе 10 активных и 5 неактивных сотрудников, запрос вернет 15 для ActiveCount, потому что посчитает все 15 строк (10 EmployeeID + 5 нулей).ELSE 0:SELECT
Department,
COUNT(CASE WHEN Status = 'Active' THEN EmployeeID END) AS ActiveCount
FROM Employees
GROUP BY Department;
ELSE тут не нужен. Если условие не выполняется, выражение вернет NULL, а COUNT NULL-ы не считает. Еще более явно можно написать так:SELECT
Department,
COUNT(CASE WHEN Status = 'Active' THEN 1 END) AS ActiveCount
FROM Employees
GROUP BY Department;
ELSE 0 будет уместен, это если вместо COUNT ты используешь SUM.
Не используй CASE в WHERE
Один из самых частых SQL анти-паттернов это пихать CASE прямо в WHERE, чтобы разрулить условную логику для диапазонов или фильтров.
Классический пример:
SELECT * FROM products
WHERE
CASE WHEN category = 'Electronics' THEN price < 500 ELSE price < 100 END;
CASE делает предикат не SARGable в большинстве популярных СУБД. Оптимизатор уже не может нормально сделать index seek по индексу на price (или по составному индексу (category, price)). В итоге нередко получаешь full table scan, даже если индексы отличные.SELECT * FROM products
WHERE price < 100
OR (category = 'Electronics' AND price < 500);
Поток данных в Postgres для чтения/записи:
Приложение --> (опционально) пулер --> отдельный backend/клиентское соединение --> shared buffers --> кэш ОС (page cache) --> физический диск.
При чтении к кэшу ОС или диску обращается только если нужных страниц нет в shared buffers.
👉 @SQLPortal
shivambhadani_/acid-properties-explained-a-complete-guide-with-postgresql-implementation-071afa6aaf8a">Cтатья про ACID-свойства.
Не только теория, но и практическая реализация на PostgreSQL с примерами из реальной жизни.
Этот пост отлично зайдет новичкам, которые хотят начать разбираться в базах данных, и он же нужен как база перед моей серией про внутренности PostgreSQL.
👉 @SQLPortal
Как выполнить поиск по нескольким условиям с помощью Xlookup
👉 @SQLPortal
Перестань переписывать JOIN через ON; используй USING
Если ты давно пишешь JOIN’ы в SQL, ты наверняка привык к ON. Оно работает и делает свою работу. Но когда ты джоинишь две таблицы по колонкам с абсолютно одинаковым именем, есть более чистый и аккуратный вариант: USING.
Вот почему стоит подумать о переходе.
SELECT
e.Employee_ID,
e.Employee_Name,
e.Department,
s.Net_Sales
FROM Employees e
JOIN sales s
ON e.Employee_ID = s.Employee_ID;
ON ок, но оно многословное. Приходится повторять и имена таблиц, и имена колонок. Если имя колонки одинаковое в обеих таблицах, ты по сути пишешь одно и то же дважды. Хуже того: когда выбираешь колонки, можешь получить два столбца Employee_ID, если явно не квалифицировать их.USING это сокращение для join’ов по равенству, когда колонки называются одинаково. Та же самая выборка будет такой:SELECT
e.Employee_ID,
e.Employee_Name,
e.Department,
s.Net_Sales
FROM Employees e
JOIN sales s
USING (Employee_ID);
USING автоматически сопоставляет колонки с одинаковым именем и делает inner join (или left/right join, в зависимости от типа join). Ты указываешь имя колонки один раз.USING это маленькое изменение, которое делает SQL короче и более “самодокументируемым”. Меньше шансов ошибиться, и результирующий набор получается аккуратнее. В следующий раз, когда джоинишь по одинаково названным колонкам, выкинь ON и попробуй USING. Только проверь, что твоя СУБД поддерживает USING.
В Postgres 18 появился новый метод клонирования для создания баз данных из шаблонных (template) БД, который работает заметно быстрее, чем другие способы. Это работает на конкретных файловых системах, которые поддерживают copy-on-write / reflink.
👉 @SQLPortal
В PostgreSQL 19 (dev) прилетели еще 2 полезных изменения:
1. Разрешили ALTER COLUMN SET EXPRESSION для виртуальных столбцов с CHECK-ограничениями
2. Запретили CR и LF в именах баз данных, ролей и табличных пространств
Небольшие изменения, но оба коммита полезные:
- первый про более гибкую работу с virtual columns
- второй про ужесточение/санитизацию имен и снижение странных кейсов
👉 @SQLPortal