В их внешнем облике ничто не вызывает подозрений. Более того, они даже кажутся тебе хорошо и давно знакомыми. Но это только до тех пор, пока ты их не проверишь. Вот тут-то они и проявят свою коварную сущность, сработав совсем не так, как ты ожидал. А иногда выкидывают такое, от чего волосы просто встают дыбом — к примеру, теряют доверенные им секретные данные. Когда ты делаешь им очную ставку, они утверждают, что не знают друг друга, хотя в тени усердно трудятся под одним колпаком. Пора уже наконец-то вывести их на чистую воду. Давайте же и мы разберемся с этими подозрительными типами.
Типизация данных в PostgreSQL, при всей своей логичности, действительно преподносит порой очень странные сюрпризы. В этой статье мы постараемся прояснить некоторые их причуды, разобраться в причине их странного поведения и понять, как не столкнуться с проблемами в повседневной практике. Сказать по правде, я составил эту статью в том числе и в качестве некоего справочника для самого себя, справочника, к которому можно было бы легко обратиться в спорных случаях. Поэтому он будет пополняться по мере обнаружения новых сюрпризов от подозрительных типов. Итак, в путь, о неутомимые следопыты баз данных!
Досье номер один. real/double precision/numeric/money
Казалось бы, числовые типы наименее проблемные с точки зрения сюрпризов в поведении. Но как бы не так. Поэтому с них и начнем. Итак…
Разучились считать
SELECT 0.1::real = 0.1
?column?
boolean
---------
fВ чем дело? В том, что PostgreSQL приводит нетипизированную константу 0.1 к типу double precision и пытается сравнить ее с 0.1 типа real. А это абсолютно разные значения! Суть в представлении вещественных чисел в машинной памяти. Поскольку 0.1 невозможно представить в виде конечной двоичной дроби (это будет 0.0(0011) в двоичном виде), числа с разной разрядностью будут отличаться, отсюда и результат, что они не равны. Вообще говоря, это тема для отдельной статьи, подробнее писать тут не буду.
Откуда ошибка?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Многие знают, что PostgreSQL допускает функциональную запись приведения типов. То есть можно написать не только 1::int, но и int(1), что будет равнозначно. Но только не для типов, название которых состоит из нескольких слов! Поэтому, если вы хотите привести числовое значение к типу double precision в функциональном виде, используйте алиас этого типа float8, то есть SELECT float8(1).
Что больше бесконечности?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tВон оно как! Оказывается, есть нечто, большее бесконечности, и это NaN! При этом документация PostgreSQL честными глазами смотрит на нас и утверждает, что NaN заведомо больше любого другого числа, а, следовательно, бесконечности. Справедливо и обратное для -NaN. Привет, любители матанализа! Но надо помнить, что все это действует в контексте вещественных чисел.
Округление глаз
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Еще один неожиданный привет от базы. И снова надо запомнить, что для типов double precision и numeric действуют разные округления. Для numeric — обычное, когда 0,5 округляется в большую сторону, а для double precision — округление 0,5 происходит в сторону ближайшего четного целого.
Деньги — это нечто особое
SELECT '10'::money::float8
ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19По мнению PostgreSQL, деньги не являются вещественным числом. По мнению некоторых индивидуумов, тоже. Нам же надо помнить, что приведение типа money возможно только к типу numeric, равно как и к типу money можно привести только тип numeric. А вот с ним уже можно играться, как душе будет угодно. Но это будут уже не те деньги.
Smallint и генерация последовательностей
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERROR: function generate_series(smallint, smallint, smallint) is not unique
LINE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
********** Ошибка **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-состояние: 42725
Подсказка: Could not choose a best candidate function. You might need to add explicit type casts.
Символ: 18Не любит PostgreSQL мелочиться. Какие такие последовательности на основании smallint? int, не меньше! Поэтому при попытке выполнения вышеприведенного запроса база пытается привести smallint к какому-то другому целочисленному типу, и видит, что таких приведений может быть несколько. Какое приведение выбрать? Это она решить не может, и поэтому падает с ошибкой.
Досье номер два. «char»/char/varchar/text
Ряд странностей присутствует и у символьных типов. Давайте тоже познакомимся с ними.
Это что за фокусы?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fЧто это за тип «char», что это за клоун? Нам таких не надо… Потому, что он прикидывается обычным char, даром что в кавычках. А отличается он от обычного char, который без кавычек, тем, что выводит только первый байт строкового представления, тогда как нормальный char выводит первый символ. В нашем случае первый символ — буква П, которая в unicode-представлении занимает 2 байта, о чем свидетельствует конвертация результата в тип bytea. А тип «char» берет только первый байт этого unicode-представления. Тогда зачем этот тип нужен? Документация PostgreSQL говорит, что это специальный тип, используемый для особых нужд. Так что он вряд ли нам потребуется. Но посмотрите ему в глаза и не ошибитесь, когда встретите его с его особенным поведением.
Лишние пробелы. С глаз долой, из сердца вон
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Взгляните на приведенный пример. Я специально все результаты привел к типу bytea, чтобы было наглядно видно, что там лежит. Где хвостовые пробелы после приведения к типу varchar(6)? Документация лаконично утверждает: «При приведении значения character к другому символьному типу дополняющие пробелы отбрасываются». Эту нелюбовь надо запомнить. И заметьте, что если строковая константа в кавычках сразу приводится к типу varchar(6), концевые пробелы сохраняются. Такие вот чудеса.
Досье номер три. json/jsonb
JSON — отдельная структура, которая живет своей жизнью. Поэтому ее сущности и сущности PostgreSQL немного отличаются. Вот примеры.
Джонсон и Джонсон. Почувствуйте разницу
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fВсе дело в том, что у JSON есть своя сущность null, которая не является аналогом NULL в PostgreSQL. В то же время, сам JSON-объект вполне может иметь значение NULL, поэтому выражение SELECT null::jsonb IS NULL (обратите внимание на отсутствие одинарных кавычек) на сей раз вернет true.
Одна буква меняет все
SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json
json
json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}
---
SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb
jsonb
jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}Все дело в том, что json и jsonb — совершенно разные структуры. В json объект хранится как есть, а в jsonb он хранится уже в виде разобранной проиндексированной структуры. Именно поэтому во втором случае значение объекта по ключу 1 было заменено с [1, 2, 3] на [7, 8, 9], которое пришло в структуру в самом конце с тем же ключом.
С лица воды не пить
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL в реализации JSONB меняет форматирование вещественных чисел, приводя их к классическому виду. Для типа JSON такого не происходит. Странно немного, но его право.
Досье номер четыре. date/time/timestamp
С типами даты/времени тоже есть некоторые странности. Посмотрим на них. Сразу оговорюсь, что некоторые из особенностей поведения становятся понятными, если хорошо понимать суть работы с часовыми поясами. Но это также тема для отдельной статьи.
Моя твоя не понимать
SELECT '08-Jan-99'::date
ERROR: date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Perhaps you need a different "datestyle" setting.
********** Ошибка **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-состояние: 22008
Подсказка: Perhaps you need a different "datestyle" setting.
Символ: 8Казалось бы, что тут непонятного? Но все же база не понимает, что мы тут поставили на первое место — год или день? И решает, что это 99 января 2008 года, что взрывает ей мозг. Вообще говоря, в случае передачи дат в текстовом формате нужно очень внимательно проверять то, насколько правильно база их распознала (в частности, анализировать параметр datestyle командой SHOW datestyle), поскольку неоднозначности в этом вопросе могут стоить очень дорого.
Ты откуда такой взялся?
SELECT '04:05 Europe/Moscow'::time
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
^
********** Ошибка **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL-состояние: 22007
Символ: 8Почему база не может понять явно указанное время? Потому что для часового пояса указана не аббревиатура, а полное наименование, которое имеет смысл только в контексте даты, поскольку учитывает историю изменения часовых поясов, а она без даты не работает. Да и сама формулировка строки времени вызывает вопросы — а что же на самом деле имел в виду программист? Поэтому тут все логично, если разобраться.
Что ему не так?
Представьте себе ситуацию. У вас в таблице есть поле с типом timestamptz. Вы хотите его проиндексировать. Но понимаете, что строить по этому полю индекс не всегда оправдано ввиду его высокой селективности (почти все значения этого типа будут уникальными). Поэтому вы решаете снизить селективность индекса, приведя этот тип к дате. И получаете сюрприз:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
ERROR: functions in index expression must be marked IMMUTABLE
********** Ошибка **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL-состояние: 42P17В чем дело? В том, что для приведения типа timestamptz к типу date используется значение системного параметра TimeZone, что делает функцию приведения типа зависимой от настраиваемого параметра, т.е. изменчивой (volatile). Такие функции в индексе недопустимы. В этом случае надо явно указывать, в каком часовом поясе производится приведение типа.
Когда now совсем даже не now
Мы привыкли, что now() возвращает текущую дату/время с учетом часового пояса. Но посмотрите на следующие запросы:
START TRANSACTION;
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
COMMIT;Дата/время возвращаются одинаковыми независимо от того, сколько времени прошло с момента предыдущего запроса! В чем дело? В том, что now() — это не текущее время, а время начала текущей транзакции. Поэтому в рамках транзакции оно не меняется. Любой запрос, запускаемый вне рамок транзакции, оборачивается в транзакцию неявно, поэтому мы и не замечаем, что время, выдаваемое простым запросом SELECT now(); на самом деле-то не текущее… Если хотите получить честное текущее время, нужно пользоваться функцией clock_timestamp().
Досье номер пять. bit
Strange a little bit
SELECT '111'::bit(4)
bit
bit(4)
------
1110С какой стороны следует добавлять биты в случае расширения типа? Кажется, что слева. Но только у базы на этот счет другое мнение. Будьте осторожны: при несоответствии количества разрядов при приведении типа вы получите совсем не то, что хотели. Это относится как к добавлению битов справа, так и к урезанию битов. Тоже справа…
Досье номер шесть. Массивы
Даже NULL не стрельнул
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Как нормальные люди, воспитанные на SQL, мы ожидаем, что результатом этого выражения будет NULL. Но не тут-то было. Возвращается массив. Почему? Потому что в данном случае база приводит NULL к целочисленному массиву и неявно вызывает функцию array_cat. Но все равно остается неясным, почему этот «массивовый котик» не обнуляет массив. Такое поведение тоже надо просто запомнить.
Подведем итог. Странностей хватает. Большинство из них, конечно, не настолько критичны, чтобы говорить о вопиюще неадекватном поведении. А другие объясняются удобством использования или частотой их применимости в тех или иных ситуациях. Но в то же время неожиданностей много. Поэтому надо о них знать. Если найдете еще что-то странное или необычное в поведении каких-либо типов, пишите в комментариях, с удовольствием дополню имеющиеся на них досье.
Источник: habr.com