Съмнителни типове

В техния външен вид няма нищо подозрително. По-скоро те изглеждат познати и добре известни. Но това важи само до момента, в който не ги проверите. Точно тогава ще проявят истинската си същност, работейки съвсем неочаквано. Понякога правят неща, от които просто ти настръхват косите — например, губят доверените им секретни данни. Когато ги изправиш лице в лице, те твърдят, че не се познават, въпреки че в сянка усилено работят под един покрив. Време е да ги изобличим. Нека да се справим с тези подозрителни типове.

Типизацията на данни в 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

?колона?
булев
---------
t

Ето така! Оказва се, че има нещо по-голямо от безкрайността, и това е NaN! В същото време документацията на PostgreSQL честно ни уверява, че NaN е заведомо по-голямо от всяко друго число, а следователно и от безкрайността. Обратно е вярно и за -NaN. Здравейте, любители на математическия анализ! Но трябва да помним, че всичко това важи в контекста на действителните числа.

Закръгляване на числа

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      закръглено      |  закръглено
двойна точност | число
-----------------+---------
2                | 3

Още едно неочаквано приветствие от базата. И отново трябва да запомним, че за типовете double precision и numeric важат различни правила за закръгляване. За numeric – обикновено, когато 0,5 се закръгля нагоре, а за double precision – закръгляването на 0,5 се извършва към най-близкото четно цяло число.

Парите са нещо особено

SELECT '10'::money::float8

ГРЕШКА: не може да бъде преобразуван тип money в double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Грешка **********
ГРЕШКА: не може да бъде преобразуван тип money в double precision
SQL статус: 42846
Символ: 19

Според PostgreSQL, парите не са действително число. Според някои индивиди, също. Ние трябва да помним, че преобразуването на типа money е възможно само към типа numeric, както и типа money може да бъде преобразуван само от тип numeric. А с него вече може да се играе, както душата желае. Но тогава парите вече няма да са същите.

Smallint и генериране на серии

SELECT *
  FROM generate_series(1::smallint, 5::smallint, 1::smallint)

ГРЕШКА: функция generate_series(smallint, smallint, smallint) не е уникална
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
СЪВЕТ: Не можахме да изберем най-добрата кандидат функция. Може да се наложи да добавите явни типови преобразувания.
********** Грешка **********
ГРЕШКА: функция generate_series(smallint, smallint, smallint) не е уникална
SQL статус: 42725
Подсказка: Не можахме да изберем най-добрата кандидат функция. Може да се наложи да добавите явни типови преобразувания.
Символ: 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 това не се случва. Странно, но е право.

Досие номер четири. дата/време/времева отметка

С типовете дата/време също има някои странности. Нека ги разгледаме. Отначало ще кажа, че някои от особеностите стават ясни, ако прекрасно разбирате как работят часовите зони. Но това също е тема за отделна статия.

Моя твоя не разбирам

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), тъй като неяснотите по този въпрос могат да струват много скъпо.

Откъде се взе такъв?

ИЗБЕРЕТЕ '04:05 Европа/Москва'::time

ГРЕШКА: неверный синтаксис ввода для типа time: "04:05 Европа/Москва"
СТРОКА 1: ИЗБЕРИТЕ '04:05 Европа/Москва'::time
               ^
********** Грешка **********
ГРЕШКА: неверный синтаксис ввода для типа time: "04:05 Европа/Москва"
SQL-состояние: 22007
Символ: 8

Защо базата не може да разбере явно посоченото време? Защото за часовата зона е посочено не съкращение, а пълното наименование, което има смисъл само в контекста на дата, тъй като отразява историята на промените в часовите зони, а без дата не работи. А и самата формулировка на времевия низ предизвиква въпроси — какво всъщност е имал предвид програмистът? Затова тук всичко е логично, ако разберете.

Какво не е наред с него?

Представете си ситуация. В таблицата ви има поле от тип timestamptz. Искате да го индексирате. Но разбирате, че строежът на индекс по това поле не винаги е уместен поради неговата висока селективност (почти всички стойности от този тип ще бъдат уникални). Затова решавате да намалите селективността на индекса, преобразувайки този тип в дата. И получавате изненада:

СЪЗДАЙ ИНДЕКС "iIdent-DateLastUpdate"
  НА public."Ident" ИЗПОЛЗВАЙКИ btree
  (("DTLastUpdate"::date));

ГРЕШКА: функциите в израза на индекса трябва да бъдат маркирани с IMMUTABLE
********** Грешка **********
ГРЕШКА: функциите в израза на индекса трябва да бъдат маркирани с IMMUTABLE
SQL-состояние: 42P17

В какво е проблемът? В това, че за преобразуването на типа timestamptz в тип date се използва стойността на системния параметър TimeZone, което прави функцията за преобразуване зависима от настраиваемия параметър, т.е. изменчива (volatile). Такива функции не могат да бъдат част от индекс. В този случай трябва ясно да се посочи в коя часова зона се извършва преобразуването на типа.

Когато now не е съвсем now

Смеем да приемем, че now() връща текущата дата/време с оглед на часовата зона. Но погледнете следните заявки:

НАЧАЛО ТРАНЗАКЦИЯ;
ИЗБЕРЕТЕ now();

            now
  времева маркировка с часова зона
-----------------------------
2019-11-26 13:13:04.271419+03

...

ИЗБЕРЕТЕ now();

            now
  времева маркировка с часова зона
-----------------------------
2019-11-26 13:13:04.271419+03

...

ИЗБЕРЕТЕ now();

            now
  времева маркировка с часова зона
-----------------------------
2019-11-26 13:13:04.271419+03

ЗАВЪРШИ;

Дата/времето остава непроменено независимо от това колко време е минало от последното запитване! Каква е причината? Причината е, че now() не е текущото време, а времето на началото на текущата транзакция. Затова в рамките на транзакцията то не се променя. Всяко запитване, което се стартира извън контекста на транзакция, се обвива в транзакция неявно, затова не забелязваме, че времето, което се получава от простото запитване SELECT now(); всъщност не е текущо... Ако искате да получите истинското текущо време, трябва да използвате функцията clock_timestamp().

Досье номер пет. 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