🥇Падазроныя тыпы

У іх вонкавым абліччы нішто не выклікае падазрэнняў. Больш за тое, яны нават здаюцца табе добра і даўно знаёмымі. Але гэта толькі датуль, пакуль ты іх не праверыш. Вось тут яны і выявяць сваю падступную сутнасць, спрацаваўшы зусім не так, як ты чакаў. А часам выкідваюць такое, ад чаго валасы проста ўстаюць дыбам - да прыкладу, губляюць давераныя ім сакрэтныя дадзеныя. Калі ты робіш ім вочную стаўку, яны сцвярджаюць, што не ведаюць адзін аднаго, хоць у цені старанна працуюць пад адным каўпаком. Час ужо вывесці іх на чыстую ваду. Давайце ж і мы разбярэмся з гэтымі падазронымі тыпамі.

Тыпізацыі дадзеных у 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

Падазроныя тыпы