There will be no singularity: “Пятничный SQL-WTF Понедельничный SQL-TIL #4 Динамить вас третью недел…”

~~Пятничный SQL-WTF~~
Понедельничный SQL-TIL #4

Динамить вас третью неделю подряд было как-то по-свински, по этому встречайте ;)

Disclamer: скорее всего вы никогда не столкнетесь с таким синтаксисом и архитектурой в реальной жизни. Но зато сможете блеснуть эрудицией перед своими коллегами :)

1 wtf из 5 - алиасы типов
Разных названий одних и тех же сущностей в PostgreSQL не очень много - всего 24 пары.

int2 -> smallint
int4 -> integer

и так далее.
Я не буду перечислять их все, и запоминать эти соответствия не нужно. Для этого будет правило в holistic.dev

2 wtf из 5 - тип float
Тип FLOAT (он же single-precision floating-point, он же IEEE 754, оно же число одинарной точности) в PostgreSQL отсутствует. Но т.к. в SQL он описан, то для совместимости его реализовали через хитрые алиасы.

- float(p), где p от 1 до 24 или float4 - эквивалент real
- float(p), где p от 25 до 53 или float или float8 - эквивалент double precision
- Значения p вне допустимого диапазона вызывают ошибку

Как это запомнить? Да никак! Не используйте тип float. holistic.dev вам напомнит :)

3 wtf из 5 - self join
Это пункт не совсем про wtf, больше про bad/good practice.
Self join используют обычно в 3 случаях: когда у вас EAV, когда вы храните в таблице дерево или когда нужно pivot'нуть аналитический запрос.
EAV мы обсуждать не будем (надеюсь, что никто из вас таким не занимается), деревья в таблице обрабатывать лучше через RECURSIVE CTE, а вот про пивот и аналитику стоит поговорить. Есть статья на modern-sql.com с примерами, но я расскажу коротенько.

Если вам нужно несколько агрегатов из одной таблицы с разными условиями, то отличным вариантом для этого в PostgreSQL является конструкция FILTER (WHERE ...).
Например, вы хотите посчитать количество чего-то с разными статусами для каждого юзера:

SELECT
  user_id, 
  COUNT(*) FILTER (WHERE status = 'new') AS count_new,
  COUNT(*) FILTER (WHERE status = 'canceled') AS count_canceled,
  ...
GROUP BY user_id

Или статистику по месяцам, где каждый месяц должен быть отдельным столбцом.
Проблема в том, что этот функционал реализован только в PostgreSQL :(
В других базах предлагается использовать CASE.

SELECT
  user_id, 
  SUM(CASE WHEN status = 'new' THEN 1 END) AS count_new,
  ...

В holistic.dev будет правило, находящее места, где можно использовать FILTER (WHERE ...) вместо CASE или self join.

5 wtf из 5 - 0.1 + 0.2

SELECT 0.1 + 0.2;
-- 0.3

SELECT 0.1::float + 0.2::float;
-- 0.30000000000000004

ЭЭЭ?
Спокойно, это фича.
Причем практически во всех языках программирования.

Поэтому желательно избегать тип double precision в тех случаях, когда с этими числами предполагается производить математические операции (в том числе и агрегаты).
Да, у нас в holistic.dev есть правило double-prescision-instead-numeric, которое срабатывает на определенные размерности NUMERIC. Для двукратного увеличения производительности арифметических операций с полями этого типа можно заменить NUMERIC на DOUBLE PRECISION. Но взамен мы получим проблемы, описанные выше.

Именно из-за этой проблемы не рекомендуется хранить деньги в типах с плавающей точкой. Храните числа в центах, сатоши, wei или любой другой не квантуемой единице.
И никогда, слышите, НИКОГДА не занимайтесь математикой с деньгами на фронте. Все получайте только с бэкенда и желательно в целых числах!

Первые три выпуска:
https://t.me/nosingularity/535
https://t.me/nosingularity/541
https://t.me/nosingularity/548