У њиховом изгледу нема ништа сумњиво. Штавише, чак вам се чине познатим и дуго времена. Али то је само док их не проверите. Овде показују своју подмуклу природу, радећи потпуно другачије него што сте очекивали. И понекад ураде нешто од чега вам се диже коса на глави – на пример, изгубе тајне податке који су им поверени. Када се суочите са њима, они тврде да се не познају, иако у сенци вредно раде под истом хаубом. Време је да их коначно доведемо у чисту воду. Хајде да се позабавимо и овим сумњивим типовима.
Унос података у ПостгреСКЛ-у, упркос својој логици, понекад представља веома чудна изненађења. У овом чланку ћемо покушати да разјаснимо неке од њихових чудака, разумемо разлог њиховог чудног понашања и разумемо како да не наиђемо на проблеме у свакодневној пракси. Истини за вољу, саставио сам овај чланак и као неку врсту приручника за себе, референцу на коју би се лако могло позвати у спорним случајевима. Стога ће се допуњавати како буду откривена нова изненађења сумњивих типова. Дакле, идемо, о неуморни трагачи базе података!
Досије број један. реална/двострука прецизност/нумеричка/новац
Чини се да су нумерички типови најмање проблематични у смислу изненађења у понашању. Али како год да је. Па почнимо са њима. Тако…
Заборавио сам да бројим
SELECT 0.1::real = 0.1
?column?
boolean
---------
fШта је било? Проблем је у томе што ПостгреСКЛ конвертује неуписану константу 0.1 у двоструку прецизност и покушава да је упореди са 0.1 реалног типа. А ово су потпуно различита значења! Идеја је да се реални бројеви представљају у машинској меморији. Пошто 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Многи људи знају да ПостгреСКЛ дозвољава функционалну нотацију за преливање типова. То јест, можете написати не само 1::инт, већ и инт(1), што ће бити еквивалентно. Али не за типове чија се имена састоје од неколико речи! Стога, ако желите да претворите нумеричку вредност у тип двоструке прецизности у функционалном облику, користите псеудоним овог типа флоат8, односно СЕЛЕЦТ флоат8(1).
Шта је веће од бесконачности?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tПогледајте како је! Испоставило се да постоји нешто веће од бесконачности, а то је НаН! У исто време, ПостгреСКЛ документација нас гледа поштеним очима и тврди да је НаН очигледно већи од било ког другог броја, а самим тим и бесконачност. За -НаН важи и супротно. Здраво, љубитељи математике! Али морамо запамтити да све ово функционише у контексту реалних бројева.
Заокруживање очију
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Још један неочекивани поздрав из базе. Опет, запамтите да типови двоструке прецизности и нумерички типови имају различите ефекте заокруживања. За нумеричку - уобичајену, када се 0,5 заокружује навише, а за двоструку прецизност - 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Према ПостгреСКЛ-у, новац није прави број. Према неким појединцима, такође. Морамо запамтити да је пребацивање типа новца могуће само на нумерички тип, као што се само нумерички тип може пребацити на тип новца. Али сада можете да се играте са њим како вам срце жели. Али то неће бити исти новац.
Смаллинт и генерисање секвенце
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ПостгреСКЛ не воли да губи време на ситнице. Које су ове секвенце засноване на смаллинту? инт, ни мање ни више! Због тога, када покушава да изврши горњи упит, база података покушава да преведе смаллинт на неки други целобројни тип и види да може постојати неколико таквих пребацивања. Коју глумачку екипу изабрати? Она не може да одлучи о овоме и стога се руши са грешком.
Фајл број два. "цхар"/цхар/варцхар/тект
Бројне необичности су такође присутне у типовима карактера. Хајде да их упознамо.
Какви су то трикови?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fКоја је ово врста "чаре", какав је ово кловн? Не требају нам... Зато што се претвара да је обичан знак, иако је под наводницима. И разликује се од обичног цхар, који је без наводника, по томе што излази само први бајт стринг репрезентације, док нормалан цхар исписује први знак. У нашем случају, први знак је слово П, које у уницоде приказу заузима 2 бајта, што се доказује претварањем резултата у бајт тип. А тип „цхар“ заузима само први бајт ове уницоде репрезентације. Зашто је онда потребан овај тип? ПостгреСКЛ документација каже да је ово посебан тип који се користи за посебне потребе. Тако да је мало вероватно да ће нам требати. Али погледајте га у очи и нећете погрешити када га упознате са његовим посебним понашањем.
Ектра спацес. Далеко од очију, далеко од мисли
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Погледајте дати пример. Све резултате сам посебно конвертовао у бајт тип, тако да се јасно видело шта се ту налази. Где су празни размаци након пребацивања на варцхар(6)? Документација сажето каже: „Када се вредност карактера пребацује на други тип карактера, завршни размак се одбацује.“ Ова несклоност се мора запамтити. И имајте на уму да ако се наведена стринг константа директно пребаци на тип варцхар(6), размаци на крају остају сачувани. Таква су чуда.
Фајл број три. јсон/јсонб
ЈСОН је засебна структура која живи свој живот. Стога се његови ентитети и ентитети ПостгреСКЛ-а мало разликују. Ево примера.
Џонсон и Џонсон. осетите разлику
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fСтвар је у томе што ЈСОН има сопствени нулл ентитет, који није аналог НУЛЛ-а у ПостгреСКЛ-у. У исто време, сам ЈСОН објекат може имати вредност НУЛЛ, тако да ће израз СЕЛЕЦТ нулл::јсонб ИС НУЛЛ (обратите пажњу на одсуство појединачних наводника) овог пута вратити труе.
Једно слово мења све
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]}Ствар је у томе што су јсон и јсонб потпуно различите структуре. У јсон-у, објекат се чува онакав какав јесте, ау јсонб-у је већ ускладиштен у облику рашчлањене, индексиране структуре. Зато је у другом случају вредност објекта кључем 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}ПостгреСКЛ у својој ЈСОНБ имплементацији мења форматирање реалних бројева, доводећи их до класичног облика. Ово се не дешава за тип ЈСОН. Мало чудно, али је у праву.
Фајл број четири. датум/време/временска ознака
Постоје и неке необичности са типовима датума/времена. Хајде да их погледамо. Одмах да резервишем да неке карактеристике понашања постају јасне ако добро разумете суштину рада са временским зонама. Али ово је такође тема за посебан чланак.
Ја не разумем твоје
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. који јој одушевљава. Уопштено говорећи, када преносите датуме у текстуалном формату, морате веома пажљиво да проверите колико их је база података исправно препознала (нарочито анализирајте параметар датестиле командом СХОВ датестиле), пошто нејасноће по овом питању могу бити веома скупе.
Одакле си дошао?
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Зашто база података не може да разуме експлицитно одређено време? Јер временска зона нема скраћеницу, већ пун назив, што има смисла само у контексту датума, пошто узима у обзир историју промена временске зоне, а не ради без датума. И сама формулација временске линије поставља питања – шта је програмер заиста мислио? Дакле, овде је све логично, ако погледате.
Шта није у реду са њим?
Замислите ситуацију. Имате поље у табели са типом тиместамптз. Желите да га индексирате. Али разумете да изградња индекса на овом пољу није увек оправдана због његове високе селективности (скоро све вредности овог типа ће бити јединствене). Зато одлучујете да смањите селективност индекса тако што ћете тип пребацити на датум. И добићете изненађење:
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Шта је било? Чињеница је да се за пребацивање типа тиместамптз на тип датума користи вредност системског параметра ТимеЗоне, што чини функцију конверзије типа зависном од прилагођеног параметра, тј. несталан. Такве функције нису дозвољене у индексу. У овом случају морате експлицитно навести у којој временској зони се врши пребацивање типа.
Када сада уопште није ни сада
Навикли смо да нов() враћамо тренутни датум/време, узимајући у обзир временску зону. Али погледајте следеће упите:
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;Датум/време се враћа исто без обзира колико је времена прошло од претходног захтева! Шта је било? Чињеница је да нов() није тренутно време, већ време почетка тренутне трансакције. Стога се не мења унутар трансакције. Сваки упит покренут ван опсега трансакције је имплицитно умотан у трансакцију, због чега не примећујемо да је време враћено једноставним СЕЛЕЦТ нов(); у ствари, не тренутно... Ако желите да добијете искрено тренутно време, потребно је да користите функцију цлоцк_тиместамп().
Фајл број пет. мало
Мало чудно
SELECT '111'::bit(4)
bit
bit(4)
------
1110Коју страну треба додати битове у случају проширења типа? Изгледа да је на левој страни. Али само база има другачије мишљење о овом питању. Будите пажљиви: ако се број цифара не поклапа при убацивању типа, нећете добити оно што сте желели. Ово се односи и на додавање битова удесно и на одсецање битова. Такође са десне стране...
Досије број шест. Низови
Чак се и НУЛЛ није активирао
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Као нормални људи одгајани на СКЛ-у, очекујемо да ће резултат овог израза бити НУЛЛ. Али није га било. Враћа се низ. Зашто? Зато што у овом случају база пребацује НУЛЛ низу целог броја и имплицитно позива функцију арраи_цат. Али и даље остаје нејасно зашто ова „мачка низа“ не ресетује низ. Ово понашање такође треба само запамтити.
Резимирати. Има доста чудних ствари. Већина њих, наравно, није толико критична да би говорила о очигледно неприкладном понашању. А други се објашњавају једноставношћу употребе или учесталошћу њихове применљивости у одређеним ситуацијама. Али у исто време има много изненађења. Стога, морате знати о њима. Ако нађете још нешто чудно или необично у понашању било које врсте, напишите у коментарима, радо ћу додати у досијее доступне на њима.
Извор: ввв.хабр.цом