Сегодня решаем конфликты👩‍⚖️👨‍⚖️

ON CONFLICT DO — конструкция в PostgreSQL для обработки конфликтов при вставке (INSERT).

Зачем?
Позволяет указать, что делать, если попытка вставки сталкивается с нарушением ограничения (например, по первичному ключу или уникальному индексу).

Как решаем конфликты?
- ON CONFLICT DO NOTHING — игнорировать конфликт (не вставлять строку).
- ON CONFLICT DO UPDATE — при конфликте выполнить обновление существующей строки.

Примеры:

Простое игнорирование конфликта

INSERT INTO users(id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT DO NOTHING;

Игнорирование по конкретному уникальному ограничению

INSERT INTO users(id, username, email)
VALUES (2, 'bob', '[email protected]')
ON CONFLICT (username) DO NOTHING;

Upsert — обновление при конфликте по PK

INSERT INTO products(id, name, price)
VALUES (10, 'Widget', 9.99)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    price = EXCLUDED.price;

Переосмысливаю презентации базового курса, выбор на сегодня - ленивец Жора. Как Вам?)

Сегодня обсудим каскадное удаление записей в таблице

Что это?
Механизм в PostgreSQL, при котором при удалении записи из родительской таблицы автоматически удаляются все связанные записи из ссылающихся таблиц.

Зачем ?
- Упрощает управление связанными данными
- Предотвращает нарушение целостности данных
- Автоматизирует удаление зависимых записей

Пример:

CREATE TABLE parent (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE child (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(id) ON DELETE CASCADE,
    data TEXT
);

-- При удалении записи из parent автоматически удалятся все связанные child записи

DELETE FROM parent WHERE id = 1;

Как понять, что произошло выше?
В запросе выше при удалении записи с id=1 в таблице parent автоматически удалятся все записи из child с parent_id = 1.

Вопрос с урока: У меня есть столбец с денежными суммами. Какой тип данных выбрать: money или numeric?

Обсудим money в PostgreSQL

Тип money в PostgreSQL предназначен для хранения денежных значений с фиксированной точностью и форматированием. Это специализированный тип для удобной работы с финансовыми данными.

Основные характеристики

- Хранит денежные суммы с точностью до двух десятичных знаков (центы).
- Значения автоматически форматируются при выводе в виде строк с символом валюты и разделителями тысяч (например, $1,234.56 или 1 234,56 ₽ в зависимости от локали).

НО:

- Важно учитывать, что тип money менее гибкий по сравнению с числовыми типами numeric или decimal, особенно при необходимости высокой точности и работы с другими валютами или крупными суммами.
- Для простых финансовых данных, требующих стандартного форматирования — тип money идеальный вариант!
- А еще важно за настройками локали, так как они могут влиять на парсинг и формат вывода.

♥️ - если было полезно!)

Я сегодня безумно счастливая, потому что новая версия сайта завершена🎉🤩

Миллион правок уже учтен, и я приглашаю вас стать первыми пользователями и тестировщиками 😂

Тык сюда: https://sqlclass.ru/

Циклы в PostgreSQL - это что?

В PostgreSQL циклы используются внутри процедур и функций, написанных на языке PL/pgSQL, чтобы повторять выполнение блока кода несколько раз.

Зачем нужны циклы

- Автоматизация повторяющихся операций
- Обработка и обход наборов данных построчно
- Выполнение сложной логики с многократными итерациями

Основные типы циклов в PostgreSQL с примерами

1. LOOP
Бесконечный цикл, который прерывается явно через EXIT.

DO $$
DECLARE
  counter INT := 1;
BEGIN
  LOOP
    RAISE NOTICE 'Итерация %', counter;
    counter := counter + 1;
    EXIT WHEN counter > 5;
  END LOOP;
END $$;

2. WHILE
Цикл с условием в начале, выполняется пока условие истинно.

DO $$
DECLARE
  counter INT := 1;
BEGIN
  WHILE counter <= 5 LOOP
    RAISE NOTICE 'Итерация %', counter;
    counter := counter + 1;
  END LOOP;
END $$;

3. FOR (numeric)
Цикл для числового диапазона.
Прерывается, когда диапазон заканчивается.

DO $$
BEGIN
  FOR i IN 1..5 LOOP
    RAISE NOTICE 'Итерация %', i;
  END LOOP;
END $$;

Хотите рассмотреть подробно каждый пример?
♥️ - жду продолжения
😎 - мне все давно понятно

Считаю, что в понедельничный вечер можно поймать немного хаха)

Вы из какой группы? Мне очень нравится самая правая 😎

Привет всем!

Я с отличными новостями!🎉
Для всех новичков в это воскресенье в 12:00
состоится бесплатный урок, который познакомит вас с основами работы с PostgreSQL!

Вы научитесь:
- Понимать структуру SQL-запросов и особенности PostgreSQL
- Создавать таблицы в базе данных (Create)
- Чтение и выборка данных (Read)
- Обновление/изменение существующих данных (Update)
- Удаление записей (Delete)

Почему стоит участвовать:
- Бесплатно без вложений вы получите ценные практические знания
- Пошаговое объяснение для новичков — даже если вы никогда не работали с базами данных
- Возможность задать вопросы прямо на уроке
- Отличный старт в изучении SQL

👉 Для регистрации напишите мне: @sql_class

🌐Ты кто такая, ER-диаграмма?

❓Что такое ERD?
ER-диаграмма (Entity-Relationship Diagram) — это графическое представление таблиц в базе данных и их взаимосвязей.

📝Зачем нужны ERD?
Понимание взаимосвязей между таблицами может быть сложным, но ERD помогает визуализировать их.

👩‍💻Когда используются ERD?
• При проектировании БД ERD позволяет понять ее структуру всем участникам проекта.

• В процессе эксплуатации ERD используется как удобный инструмент для документирования структуры БД.

Как выглядит ERD? 👀👆🏼

‼Сегодня дедлайн для записи на бесплатный урок!
Если все еще хотите присоединиться, дайте знать☺

Для всех, кто уже зарегистировался и хочет на уроке практиковаться в написании запросов вместе с нами, просьба скачать приложение (бесплатная версия бело-синего цвета): https://dbeaver.io/download/

⏱ За 30 минут до занятия каждому пришлю ссылку для подключения в личные сообщения. Если будут проблемы, пишите мне: @sql_class

🎓Присоединяйтесь, пожалуйста, по ссылке в 12:00 по Москве.

До встречи в Толке!

🔒 ОГРАНИЧЕНИЯ🔒

При работе с данными функция CONSTRAINT (ограничение) просто незаменима! Она контролирует набор возможных значений для столбца или всей таблицы.

🔧СУБД внимательно следит за изменениями данных: если они не соответствуют установленным ограничениям, данные не будут изменены, и вы получите сообщение об ошибке.

NOT NULL — запрещает отсутствующие значения.
DEFAULT — устанавливает значение по умолчанию.
UNIQUE — запрещает повторение значений в колонке.
CHECK — проверяет определенные условия.

👉 Пример запроса:

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age > 18),
    Position VARCHAR(50) DEFAULT 'Employee'
);

Уже в субботу начинаем наш Базовый курс консультаций «SQL с нуля» с новой группой 👨‍🏫👩‍🏫

Следующий ещё не скоро. Так что если ещё хотите присоединиться, дайте знать: @sql_class

Отличного дня🫂

Стэнфорд открыл бесплатный курс по LLM! Учат обучать и запускать свои модели уровня GPT🚀

Разбирают оптимизацию, создание ИИ-агентов. В комплекте презентации и полезные шпаргалки.

Тык сюда: https://cme295.stanford.edu/syllabus/

🎯Различия между DISTINCT и DISTINCT ON

DISTINCT используется для удаления дубликатов по всей строке. Он возвращает уникальные строки, основываясь на всех выбранных столбцах.

SELECT DISTINCT category, price
FROM products;

Пример выгрузки:

| category | price |
|----------|-------|
| A | 10 |
| A | 15 |
| A | 10 |
| B | 20 |
| B | 25 |
| C | 30 |
| C | 35 |

DISTINCT ON вернёт только строки с уникальными значениями столбцов, указанных в скобках, а для остальных вернёт первое значение в соответствии с порядком сортировки. Если порядок сортировки не указан, будет использована сортировка по возрастанию (ASC).

SELECT DISTINCT ON (category) category, price
FROM products
ORDER BY category ASC, price DESC;

Пример выгрузки:
| category | price |
|----------|-------|
| A | 15 |
| B | 25 |
| C | 35 |

📊 EXPLAIN ANALYZE в PostgreSQL

EXPLAIN ANALYZE — это команда в PostgreSQL, которая показывает, как будет выполняться SQL-запрос, при этом фактически выполняя его и выводя реальные данные о времени и количестве обработанных строк на каждом шаге.

Зачем использовать:

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

Пример:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 12345;

Пояснения:

Результат будет содержать дерево операций, например:

- Seq Scan — последовательное сканирование всей таблицы
- Количество строк, обработанных на каждом шаге
- Время выполнения каждого шага в миллисекундах

Оконная функция LEAD в PostgreSQL

LEAD — это оконная функция, которая позволяет получить «следующее» значение без использования JOIN или подзапросов.

Как это работает?

LEAD(выражение, смещение, значение_по_умолчанию) OVER (PARTITION BY ... ORDER BY ...)

- выражение — столбец или результат вычисления,
- смещение — сколько строк вперед смотреть (по умолчанию 1),
- значение_по_умолчанию — что вернуть, если следующей строки нет (по умолчанию NULL).

Пример в PostgreSQL

SELECT  
  date,  
  sales,  
  LEAD(sales) OVER (PARTITION BY product ORDER BY date) AS next_day_sales  
FROM sales_table;

Результат:

date,sales,next_day_sales
2024-01-01,100,150
2024-01-02,150,130
2024-01-03,130,NULL