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

Нема ништо сомнително во нивниот изглед. Згора на тоа, тие дури ви изгледаат добро и долго време познати. Но, тоа е само додека не ги проверите. Тука тие ја покажуваат својата подмолна природа, работејќи сосема поинаку отколку што очекувавте. И понекогаш прават нешто што ви ја крева косата - на пример, ги губат тајните податоци што им се доверени. Кога ќе се соочите со нив, тие тврдат дека не се познаваат, иако во сенка напорно работат под иста качулка. Време е конечно да ги доведеме до чиста вода. Ајде да се справиме и со овие сомнителни типови.

Внесувањето податоци во PostgreSQL, и покрај сета своја логика, понекогаш претставува многу чудни изненадувања. Во оваа статија ќе се обидеме да разјасниме некои од нивните чуда, да ја разбереме причината за нивното чудно однесување и да разбереме како да не наидете на проблеми во секојдневната пракса. Да ја кажам вистината, оваа статија ја составив и како своевидна референтна книга за себе, референтна книга која лесно може да се повика во контроверзни случаи. Затоа, ќе се надополнува бидејќи ќе се откријат нови изненадувања од сомнителни типови. Значи, ајде да одиме, о неуморни тракери на бази на податоци!

Досие број еден. реална/двојна прецизност/нумеричка/пари

Се чини дека нумеричките типови се најмалку проблематични во однос на изненадувањата во однесувањето. Но како и да е. Па да почнеме со нив. Така…

Заборавивте како да броите

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

Што е проблемот? Проблемот е што PostgreSQL ја конвертира нетипизираната константа 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

Многу луѓе знаат дека PostgreSQL овозможува функционална нотација за кастинг на типови. Односно, можете да напишете не само 1::int, туку и int(1), што ќе биде еквивалентно. Но, не за типови чии имиња се состојат од неколку зборови! Затоа, ако сакате да фрлите нумеричка вредност за двојно прецизен тип во функционална форма, користете го алијасот од овој тип 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

Уште еден неочекуван поздрав од базата. Повторно, запомнете дека двојната прецизност и нумеричките типови имаат различни ефекти на заокружување. За нумерички - вообичаен начин, кога 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

Според PostgreSQL, парите не се реална бројка. Според некои поединци, исто така. Треба да запомниме дека фрлањето на типот на пари е можно само за нумеричкиот тип, исто како што само нумеричкиот тип може да се фрли на типот на пари. Но, сега можете да си играте со него како што сака вашето срце. Но, тоа нема да бидат истите пари.

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 не сака да губи време на ситници. Кои се овие секвенци засновани на малата боја? инт, ни помалку! Затоа, кога се обидуваме да го извршиме горенаведеното барање, базата на податоци се обидува да фрли ситно на некој друг тип на цел број и гледа дека може да има неколку такви фрлања. Која актерска екипа да се избере? Таа не може да одлучи за ова, и затоа се урива со грешка.

Датотека број два. „char“/char/varchar/текст

Голем број на необичности се присутни и кај типовите на карактери. Ајде да ги запознаеме и нив.

Какви трикови се овие?

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

Каков тип на „ќар“ е ова, каков кловн е ова? Тие не ни требаат... Затоа што се преправа дека е обичен знак, иако е во наводници. И се разликува од обичниот знак, кој е без наводници, по тоа што го прикажува само првиот бајт од претставата на низата, додека нормалниот знак го издава првиот знак. Во нашиот случај, првиот знак е буквата P, која во претставата на уникод зафаќа 2 бајти, што е потврдено со конвертирање на резултатот во типот бајти. И типот „char“ го зема само првиот бајт од оваа уникодна репрезентација. Тогаш зошто е потребен овој тип? Документацијата 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

Погледнете го дадениот пример. Специјално ги конвертирав сите резултати во типот бајт, така што јасно се гледаше што има таму. Каде се заостанувањата по кастинг до варчар(6)? Во документацијата накратко се вели: „Кога се префрла вредноста на карактерот на друг тип на знаци, празното место задоцна се отфрла“. Ова несакање мора да се запомни. И забележете дека ако цитираната константа на стрингот се фрли директно на типот varchar(6), заостанувањата се зачувани. Такви се чудата.

Датотека број три. json/jsonb

JSON е посебна структура која живее свој живот. Затоа, неговите ентитети и оние на PostgreSQL се малку различни. Еве примери.

Џонсон и Џонсон. почувствувајте ја разликата

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

Работата е што JSON има свој нула ентитет, кој не е аналог на NULL во PostgreSQL. Во исто време, самиот објект JSON може да ја има вредноста NULL, па изразот SELECT null::jsonb IS NULL (забележете го отсуството на единечни наводници) овој пат ќе се врати точно.

Една буква менува сè

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), бидејќи нејаснотијата во ова прашање може да биде многу скапа.

Од каде го добивте ова?

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 на тип на датум, се користи вредноста на системскиот параметар TimeZone, што ја прави функцијата за конверзија на типот зависна од прилагодениот параметар, т.е. испарливи. Ваквите функции не се дозволени во индексот. Во овој случај, мора експлицитно да наведете во која временска зона се изведува типот на фрлање.

Кога сега воопшто не е ни сега

Навикнати сме сега() да го враќа тековниот датум/време, земајќи ја предвид временската зона. Но, погледнете ги следните прашања:

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().

Датотека број пет. малку

Малку чудно

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

На која страна битовите треба да се додадат во случај на проширување на типот? Се чини дека е лево. Но, само базата има поинакво мислење за ова прашање. Бидете внимателни: ако бројот на цифри не се совпаѓа при фрлање тип, нема да го добиете она што го сакате. Ова важи и за додавање битови на десната и за отсекување битови. Исто така од десната ...

Датотека број шест. Низи

Дури ни NULL не пукаше

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

Како нормални луѓе израснати на SQL, очекуваме резултатот од овој израз да биде NULL. Но, тоа го немаше. Се враќа низа. Зошто? Бидејќи во овој случај базата фрла NULL на цела низа и имплицитно ја повикува функцијата array_cat. Но, сè уште останува нејасно зошто оваа „мачка со низа“ не ја ресетира низата. Ова однесување, исто така, само треба да се запомни.

Резимирајте. Има многу чудни работи. Повеќето од нив, се разбира, не се толку критични за да зборуваат за очигледно несоодветно однесување. А други се објаснуваат со леснотијата на користење или зачестеноста на нивната применливост во одредени ситуации. Но, во исто време, има многу изненадувања. Затоа, треба да знаете за нив. Ако најдете нешто друго чудно или невообичаено во однесувањето на кој било тип, пишете во коментарите, со задоволство ќе додадам во досиејата достапни на нив.

Извор: www.habr.com

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