DanLevy.net

Викторина: Глубокий Postgres: Часть 1

SQL заставляет вас визжать?

Часть 1 из 2. Перейти к Части 2

PostgreSQL 🐘 — безусловно мой любимый СУБД! Я постоянно открываю новые приёмы и подводные камни, поэтому решил собрать их в новом викторине!

Этот тест охватывает как привычные, так и менее известные возможности и подводные камни PostgreSQL: от встроенных агрегатов до приведения типов, ограничений и прочего.

Удачи! 🍀

1. 

Разминка: Функции

Какая функция НЕ является встроенной агрегатной функцией в PostgreSQL?

SELECT
  MIN(grade) as lowest,
  MAX(grade) as highest,
  AVG(grade) as average,
  MEDIAN(grade) as middle
FROM grades;

MEDIAN не встроена! Нужно:

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

Распространённые встроенные агрегаты:

  • MIN, MAX, COUNT
  • AVG, SUM
  • ARRAY_AGG, STRING_AGG
  • Различные статистические функции

2. 

Разминка: Приведение типов

Какое из этих преобразований типов некорректно ❌?

PostgreSQL поддерживает три синтаксиса приведения типов:

  1. ANSI SQL: CAST(expression AS type).
  2. PostgreSQL: expression::type.
  3. Функция типа: type 'literal'.

Все они функционально эквивалентны, но:

  • CAST() наиболее переносим.
  • :: специфичен для PostgreSQL, но широко используется.
  • Инфиксный стиль type 'literal' реже встречается, но тоже валиден.

3. 

Ограничения

Сколько значений NULL разрешено здесь?

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255),
  UNIQUE(email)
);

UNIQUE‑ограничения в PostgreSQL:

  • Позволяют несколько значений NULL.
  • NULL ≠ NULL при проверке уникальности.

Чтобы запретить значения NULL, добавьте NOT NULL:

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255) NOT NULL,
  UNIQUE(email)
);

4. 

Дата/Время

Что это возвращает?

SELECT '2024-11-27'::date + interval '24 hours';

Интервалы — мощный инструмент для упрощения операций с диапазонами дат!

Арифметика дат в PostgreSQL:

  • + interval '24 hours' добавляет 24 часа
  • + interval '1 day' добавляет 1 день
  • + interval '1 month' добавляет 1 месяц
  • + interval '1 year' добавляет 1 год

Результат — 2024-11-28 00:00:00.

5. 

Метки времени

Какое самое точное утверждение о timestamptz и timestamp?

Они оба занимают 8 байт, но хранят разные типы значений.

Так в чём разница? Всё в разборе ввода.

timestamptz

  • Нормализует ввод к абсолютному моменту времени.
  • Учитывает настройку TimeZone сервера/соединения при разборе ввода без явного смещения и при выводе.

timestamp

  • Хранит дату и время без преобразования часового пояса.
  • Не сохраняет и не нормализует информацию о часовом поясе.

timestamp

  • Хранит дату и время без информации о часовом поясе.
  • Полезен для явного хранения стандартизированных дат, либо в UTC, либо в конкретном часовом поясе.

6. 

Типы Postgres

Какой из этих вариантов ❌ не является допустимым типом PostgreSQL?

PostgreSQL имеет богатый набор типов данных, но STRING(100) не относится к ним.

Правильные строковые типы включают:

  • VARCHAR(100) (строка переменной длины)
  • CHAR(100) (строка фиксированной длины)
  • TEXT (неограниченная длина)
  • CHARACTER VARYING(100) (то же, что VARCHAR(100))

7. 

Типы Postgres

Какой из этих вариантов ❌ не является допустимым типом PostgreSQL?

Это может показаться знакомым, поскольку decimal128 используется во многих системах (включая Mongo и Java). Это не допустимый тип PostgreSQL, правильный — decimal.

Правильные числовые типы включают:

  • int (4‑байтовое целое)
  • bigint (8‑байтовое целое)
  • real (4‑байтовый плавающий)
  • double precision (8‑байтовый плавающий)
  • bigserial (автоинкрементное 8‑байтовое целое)
  • smallserial (автоинкрементное 2‑байтовое целое)

8. 

Типы Postgres

Какой из этих типов ❌ не является действительным типом PostgreSQL?

Сделало ли это вас разочарованным, даже злым? Вы не одни! Как сказал безымянный “core” разработчик базы данных, “what the hell, Dan?! I crashed on the type questions! Thats violent sir! Not sharing my score, hah.” 😈 Пожалуйста.

Богатый набор сетевых типов PostgreSQL не включает ipv4. Каждый раз, когда я пытаюсь использовать его без поиска в Google, я ошибаюсь. Возможно, наличие macaddr8 заставляет меня думать, что должны существовать типы ipv4 и ipv6. Но нет, inet охватывает оба. Кроме того, cidr охватывает сетевые маски для обоих.

Допустимые сетевые типы включают:

  • cidr (сетевой адрес IPv4/IPv6)
  • inet (адрес хоста IPv4/IPv6)
  • macaddr (MAC-адрес)
  • macaddr8 (MAC-адрес EUI-64)

9. 

Типы Postgres

Какой из этих типов ❌ не является допустимым типом PostgreSQL?

PostgreSQL имеет богатый набор специализированных типов, но currency не относится к ним!

Доступные типы включают:

  • xml (XML‑данные)
  • uuid (UUID)
  • money (сумма в валюте)
  • interval (временной интервал)

10. 

Типы Postgres

Какой из этих типов ❌ не является действительным типом PostgreSQL?

PostgreSQL обладает богатым набором специализированных типов, но triangle не относится к ним.

Я считаю, что в будущих версиях GEOS будет поддержка Triangle в формате OGC/WKT, что значит, что он со временем появится в PostGIS. (По сути, этот ответ может стать неверным в будущем.)

Правильные специализированные типы включают:

  • box (прямоугольный ящик)
  • line (бесконечная линия)
  • point (2‑мерная точка)
  • circle (2‑мерный круг)
  • polygon (2‑мерный многоугольник)

11. 

Целочисленная ариметика

Что происходит при вычислении общего количества возможных идентификаторов студентов?

SELECT 256 * 256 * 256 * 256;

Тип integer в PostgreSQL — 32‑битный знаковый, диапазон от -2,147,483,648 до 2,147,483,647.

Вычисление 256^4 = 4,294,967,296 превышает этот диапазон.

Чтобы работать с большими числами:

-- Use BIGINT
SELECT 256::bigint * 256 * 256 * 256;


-- Or numeric for arbitrary precision
SELECT 256::numeric * 256 * 256 * 256;

12. 

Дата/Время

Какой наименьший литерал timestamp превышает максимальную точность time в Postgres?

CREATE TABLE class_sessions (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  start_time timestamptz,
  end_time timestamptz
);

У timestamps в PostgreSQL микросекундная точность (6 знаков после запятой).

  • Максимум: .123456 (6 цифр)
  • Наносекунды (9 цифр) округляются или отбрасываются до поддерживаемой точности
  • Смещения часового пояса принимаются для timestamptz, но не обязательны

Неочевидный нюанс: Некоторые языки/фреймворки передают наносекундную точность, однако PostgreSQL хранит timestamps с микросекундной точностью.

13. 

Типы Postgres

Какой из этих типов ❌ не является действительным типом PostgreSQL?

(Серьёзно, это (в основном) реальные типы.)

PostgreSQL имеет несколько встроенных геометрических и типов полнотекстового поиска, но tsrank не относится к ним.

Правильные геометрические и типы полнотекстового поиска включают:

  • lseg (отрезок линии)
  • bytea (двоичные данные)
  • tsquery (запрос полнотекстового поиска)
  • tsvector (документ полнотекстового поиска)

14. 

Ограничения

Когда проверяется это ограничение оценки?

ALTER TABLE students
ADD CONSTRAINT valid_grade
CHECK (
  (grade >= 0 AND grade <= 100) OR
  grade IS NULL
) NOT VALID;

NOT VALID ограничения:

  • Проверяются сразу для новых вставок и обновлений
  • Не проверяют существующие строки
  • Могут проверить существующие строки позже с помощью VALIDATE CONSTRAINT
  • Полезны для больших таблиц

Без NOT VALID:

  • Ограничение проверяется сразу
  • Все существующие строки проверяются
  • Может быть медленно на больших таблицах

Отлично! Вы углубились в несколько областей PostgreSQL! 🐘

Надеюсь, вы узнали что‑то новое или хотя бы получили результат, которым можно похвастаться! 🏆

Посмотрите Часть 2 для продолжения PostgreSQL‑забавы! 🚀

Хотите больше острых ощущений? Загляните в мою Коллекцию викторин — бесконечное* развлечение!

Loading comments...