У їхньому зовнішньому вигляді ніщо не викликає підозр. Більше того, вони навіть здаються тобі добре та давно знайомими. Але це тільки доти, доки ти їх не перевіриш. Ось тут вони і виявлять свою підступну сутність, спрацювавши зовсім не так, як ти очікував. А іноді викидають таке, від чого волосся просто стає дибки — приміром, втрачають довірені ним секретні дані. Коли ти робиш їм очну ставку, вони стверджують, що не знають один одного, хоча в тіні старанно трудяться під одним ковпаком. Пора вже нарешті вивести їх на чисту воду. Давайте і ми розберемося з цими підозрілими типами.
Типізація даних у 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 дріб'язуватися. Які такі послідовності на підставі малої? int, не менше! Тому при спробі виконання вищенаведеного запиту база намагається привести невеликий до якогось іншого цілого типу, і бачить, що таких наведень може бути кілька. Який привід вибрати? Це вона вирішити не може і тому падає з помилкою.
Досьє номер два. "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