🥇Tipuri suspecte

Nu este nimic suspect în privința aspectului lor. Mai mult decât atât, ți se par familiare chiar bine și de mult timp. Dar asta doar până când le verifici. Aici își arată natura insidioasă, lucrând complet diferit decât vă așteptați. Și uneori fac ceva care îți face părul să stea pe cap - de exemplu, pierd datele secrete care le-au fost încredințate. Când îi confrunți, ei susțin că nu se cunosc, deși în umbră muncesc din greu sub aceeași glugă. Este timpul să le aducem în sfârșit la apă curată. Să ne ocupăm și de aceste tipuri suspecte.

Tastarea datelor în PostgreSQL, cu toată logica sa, prezintă uneori surprize foarte ciudate. În acest articol vom încerca să clarificăm unele dintre ciudateniile lor, să înțelegem motivul comportamentului lor ciudat și să înțelegem cum să nu întâmpinăm probleme în practica de zi cu zi. Ca să spun adevărul, am alcătuit acest articol și ca un fel de carte de referință pentru mine, o carte de referință la care s-ar putea face referire cu ușurință în cazuri controversate. Prin urmare, va fi completat pe măsură ce se descoperă noi surprize de la tipuri suspecte. Deci, să mergem, o, neobosite urmăritori de baze de date!

Dosarul numărul unu. real/dublă precizie/numeric/bani

S-ar părea că tipurile numerice sunt cele mai puțin problematice în ceea ce privește surprizele în comportament. Dar indiferent cum ar fi. Deci, să începem cu ei. Asa de…

Am uitat cum să număr

SELECT 0.1::real = 0.1

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

Ce s-a întâmplat? Problema este că PostgreSQL convertește constanta netipizată 0.1 la precizie dublă și încearcă să o compare cu 0.1 de tip real. Și acestea sunt semnificații complet diferite! Ideea este de a reprezenta numere reale în memoria mașinii. Deoarece 0.1 nu poate fi reprezentat ca o fracție binară finită (ar fi 0.0(0011) în binar), numerele cu adâncimi de biți diferite vor fi diferite, de unde rezultă că nu sunt egale. În general, acesta este un subiect pentru un articol separat; nu voi scrie mai detaliat aici.

De unde vine eroarea?

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

Mulți oameni știu că PostgreSQL permite notarea funcțională pentru turnarea tipului. Adică, puteți scrie nu numai 1::int, ci și int(1), care va fi echivalent. Dar nu pentru tipurile ale căror nume constau din mai multe cuvinte! Prin urmare, dacă doriți să turnați o valoare numerică la tipul de precizie dublă în formă funcțională, utilizați alias-ul acestui tip float8, adică SELECT float8(1).

Ce este mai mare decât infinitul?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

Uite cum e! Se pare că există ceva mai mare decât infinitul și este NaN! În același timp, documentația PostgreSQL ne privește cu ochi sinceri și susține că NaN este evident mai mare decât orice alt număr și, prin urmare, infinit. Opusul este valabil și pentru -NaN. Bună, iubitori de matematică! Dar trebuie să ne amintim că toate acestea funcționează în contextul numerelor reale.

Rotunjirea ochilor

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

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

Un alt salut neașteptat de la bază. Din nou, amintiți-vă că precizia dublă și tipurile numerice au efecte de rotunjire diferite. Pentru numeric - cel obișnuit, când 0,5 este rotunjit în sus, iar pentru precizie dublă - 0,5 este rotunjit către cel mai apropiat număr întreg par.

Banii sunt ceva special

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

Potrivit PostgreSQL, banii nu sunt un număr real. Potrivit unor indivizi, de asemenea. Trebuie să ne amintim că turnarea tipului de bani este posibilă numai la tipul numeric, la fel cum numai tipul numeric poate fi turnat la tipul de bani. Dar acum te poți juca cu el așa cum dorește inima ta. Dar nu vor fi aceiași bani.

Smallint și generarea secvenței

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 nu-i place să piardă timpul cu fleacuri. Care sunt aceste secvențe bazate pe smallint? int, nu mai puțin! Prin urmare, atunci când încearcă să execute interogarea de mai sus, baza de date încearcă să arunce smallint la un alt tip de întreg și vede că pot exista mai multe astfel de turnări. Ce distributie sa aleg? Ea nu poate decide acest lucru și, prin urmare, se prăbușește cu o eroare.

Dosarul numărul doi. „char”/char/varchar/text

O serie de ciudățenii sunt, de asemenea, prezente în tipurile de caractere. Să-i cunoaștem și pe ei.

Ce fel de trucuri sunt astea?

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

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

Ce fel de „char” este acesta, ce fel de clovn este acesta? Nu avem nevoie de acestea... Pentru că se preface a fi un caracter obișnuit, chiar dacă este între ghilimele. Și diferă de un caracter obișnuit, care este fără ghilimele, prin faptul că scoate numai primul octet al reprezentării șirului, în timp ce un caracter normal scoate primul caracter. În cazul nostru, primul caracter este litera P, care în reprezentarea unicode ocupă 2 octeți, așa cum se evidențiază prin conversia rezultatului la tipul bytea. Iar tipul „char” ia doar primul octet al acestei reprezentări Unicode. Atunci de ce este nevoie de acest tip? Documentația PostgreSQL spune că acesta este un tip special folosit pentru nevoi speciale. Deci este puțin probabil să avem nevoie de el. Dar uită-te în ochii lui și nu te vei înșela când îl vei întâlni cu comportamentul lui deosebit.

Spații suplimentare. Ochii care nu se văd se uită

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

Aruncă o privire la exemplul dat. Am convertit special toate rezultatele la tipul bytea, astfel încât să se vadă clar ce era acolo. Unde sunt spațiile de sfârșit după turnarea în varchar(6)? Documentația afirmă succint: „La turnarea valorii caracterului într-un alt tip de caracter, spațiile albe din urmă sunt eliminate.” Această antipatie trebuie amintită. Și rețineți că dacă o constantă șir între ghilimele este turnată direct la tipul varchar(6), spațiile de sfârșit sunt păstrate. Așa sunt minunile.

Dosarul numărul trei. json/jsonb

JSON este o structură separată care își trăiește propria viață. Prin urmare, entitățile sale și cele ale PostgreSQL sunt ușor diferite. Iată exemple.

Johnson și Johnson. simte diferenta

SELECT 'null'::jsonb IS NULL

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

Chestia este că JSON are propria sa entitate nulă, care nu este analogul lui NULL în PostgreSQL. În același timp, obiectul JSON în sine poate avea valoarea NULL, astfel încât expresia SELECT null::jsonb IS NULL (rețineți că absența ghilimelelor simple) va returna adevărată de data aceasta.

O singură literă schimbă totul

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]}

Chestia este că json și jsonb sunt structuri complet diferite. În json, obiectul este stocat așa cum este, iar în jsonb este deja stocat sub forma unei structuri analizate, indexate. De aceea, în al doilea caz, valoarea obiectului cu cheia 1 a fost înlocuită de la [1, 2, 3] la [7, 8, 9], care a intrat în structură la sfârșit cu aceeași cheie.

Nu bea apă de pe față

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL în implementarea sa JSONB modifică formatarea numerelor reale, aducându-le la forma clasică. Acest lucru nu se întâmplă pentru tipul JSON. Puțin ciudat, dar are dreptate.

Dosarul numărul patru. data/ora/marca temporală

Există, de asemenea, câteva ciudățeni cu tipuri de dată/ora. Să ne uităm la ele. Permiteți-mi să fac o rezervare imediat că unele dintre caracteristicile comportamentale devin clare dacă înțelegeți bine esența lucrului cu fusurile orare. Dar acesta este și un subiect pentru un articol separat.

Eu nu le înțeleg pe a ta

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

S-ar părea că ce este de neînțeles aici? Dar baza de date încă nu înțelege ce am pus pe primul loc aici - anul sau ziua? Și ea hotărăște că este 99 ianuarie 2008, ceea ce o uluită. În general, atunci când transmiteți date în format text, trebuie să verificați cu mare atenție cât de corect le-a recunoscut baza de date (în special, analizați parametrul datestyle cu comanda SHOW datestyle), deoarece ambiguitățile în această chestiune pot fi foarte costisitoare.

De unde ai venit?

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

De ce baza de date nu poate înțelege ora specificată în mod explicit? Pentru că fusul orar nu are o abreviere, ci un nume complet, ceea ce are sens doar în contextul unei date, deoarece ține cont de istoricul modificărilor de fus orar și nu funcționează fără dată. Și formularea în sine a liniei temporale ridică întrebări - ce a vrut să spună cu adevărat programatorul? Prin urmare, totul este logic aici, dacă te uiți la asta.

Ce-i în neregulă cu el?

Imaginează-ți situația. Aveți un câmp în tabel cu tip timestamptz. Vrei să-l indexezi. Dar înțelegeți că construirea unui index pe acest câmp nu este întotdeauna justificată din cauza selectivității sale ridicate (aproape toate valorile de acest tip vor fi unice). Deci decideți să reduceți selectivitatea indexului prin turnarea tipului la o dată. Și ai o surpriză:

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

Ce s-a întâmplat? Faptul este că pentru a turna un tip timestamptz la un tip de dată, se folosește valoarea parametrului de sistem TimeZone, ceea ce face ca funcția de conversie a tipului să fie dependentă de un parametru personalizat, de exemplu. volatil. Astfel de funcții nu sunt permise în index. În acest caz, trebuie să indicați în mod explicit în ce fus orar se efectuează turnarea tipului.

Când acum nu este nici măcar acum

Suntem obișnuiți să returnăm acum() data/ora curentă, ținând cont de fusul orar. Dar uită-te la următoarele întrebări:

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;

Data/ora este returnată la fel indiferent de cât timp a trecut de la solicitarea anterioară! Ce s-a întâmplat? Faptul este că now() nu este ora curentă, ci ora de începere a tranzacției curente. Prin urmare, nu se modifică în cadrul tranzacției. Orice interogare lansată în afara domeniului unei tranzacții este încapsulată într-o tranzacție implicit, motiv pentru care nu observăm că timpul returnat de un simplu SELECT now(); de fapt, nu cea actuală... Dacă doriți să obțineți o oră curentă sinceră, trebuie să utilizați funcția clock_timestamp().

Dosarul numărul cinci. pic

Un pic ciudat

SELECT '111'::bit(4)

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

Ce parte ar trebui adăugate biții în cazul extinderii tipului? Se pare că este în stânga. Dar numai baza are o părere diferită în această privință. Fiți atenți: dacă numărul de cifre nu se potrivește la turnarea unui tip, nu veți obține ceea ce v-ați dorit. Acest lucru se aplică atât pentru adăugarea de biți la dreapta, cât și pentru tăierea biților. Tot in dreapta...

Dosarul numărul șase. Matrice

Nici măcar NULL nu a tras

SELECT ARRAY[1, 2] || NULL

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

Ca oameni normali crescuți pe SQL, ne așteptăm ca rezultatul acestei expresii să fie NULL. Dar nu era acolo. Se returnează o matrice. De ce? Deoarece, în acest caz, baza aruncă NULL într-un tablou întreg și apelează implicit funcția array_cat. Dar încă rămâne neclar de ce această „pisică de matrice” nu resetează matricea. Acest comportament trebuie să fie amintit.

Rezuma. Sunt o mulțime de lucruri ciudate. Cei mai mulți dintre ei, desigur, nu sunt atât de critici încât să vorbească despre un comportament vădit inadecvat. Iar altele se explică prin ușurința în utilizare sau prin frecvența aplicabilității lor în anumite situații. Dar, în același timp, sunt multe surprize. Prin urmare, trebuie să știți despre ele. Dacă găsiți ceva ciudat sau neobișnuit în comportamentul de orice tip, scrieți în comentarii, voi adăuga cu plăcere dosarele disponibile pe ele.

Sursa: www.habr.com

Tipuri suspecte