Tipuri suspecte

În aparență, ei nu stârnesc suspiciuni. Mai mult, par a fi familiarizați cu tine de mult timp. Dar asta doar până când îi verifici. Aici își arată adevărata față, comportându-se complet diferit față de cât te aștepți. Uneori, fac astfel de lucruri încât te lasă fără cuvinte — de exemplu, pierd datele confidențiale încredințate lor. Când le faci față, susțin că nu se cunosc, deși în umbră lucrează din greu sub același acoperiș. Este timpul să-i scoatem la iveală. Să ne ocupăm și noi de acești indivizi suspecti.

Tipizarea datelor în PostgreSQL, în ciuda logicii sale, poate aduce uneori surprize foarte ciudate. În acest articol, vom încerca să lămurim unele dintre aceste ciudățenii, să înțelegem cauza comportamentului lor ciudat și să vedem cum putem evita problemele în practică. Trebuie să spun că am scris acest articol și ca un ghid pentru mine, un ghid la care să pot apela ușor în caz de neclarități. Prin urmare, acesta va fi actualizat pe măsură ce descoper noi surprize de la aceste tipuri suspecte. Așadar, să ne aventurăm, neobosiți căutători de date!

Dosarul numărul unu. real/double precision/numeric/money

Părea că tipurile numerice sunt cele mai puține problematic cu privire la surprizele comportamentului. Dar nu este așa. Așadar, de aici vom începe. Iată că…

Am uitat să calculăm

SELECT 0.1::real = 0.1

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

Ce se întâmplă? PostgreSQL convertește constanta netipizată 0.1 la tipul double precision și încearcă să o compare cu 0.1 de tip real. Iar acestea sunt valori complet diferite! Problema constă în reprezentarea numărului real în memoria mașinii. Deoarece 0.1 nu poate fi reprezentat ca o fracție binară finită (va fi 0.0(0011) în format binar), numerele cu precizii diferite vor varia, de aici rezultatul că nu sunt egale. În general, aceasta ar fi o temă pentru un articol separat; nu voi detalia aici.

De unde provine eroarea?

SELECT double precision(1)

EROARE:  eroare de sintaxă la sau aproape de "("
LINE 1: SELECT double precision(1)
                               ^
********** Eroare **********
EROARE: eroare de sintaxă la sau aproape de "("
Starea SQL: 42601
Simbol: 24

Mulți știu că PostgreSQL suportă scrierea funcțională a conversiilor de tipuri. Cu alte cuvinte, poți scrie nu doar 1::int, ci și int(1), care este echivalent. Dar nu este același lucru pentru tipurile al căror nume este compus din mai multe cuvinte! Așadar, dacă vrei să convertești o valoare numerică în tipul double precision într-o formă funcțională, folosește aliasul acestui tip, float8, adică SELECT float8(1).

Ce este mai mult decât infinitatea?

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

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

Uite așa! Se dovedește că există ceva mai mare decât infinitatea, și anume NaN! În același timp, documentația PostgreSQL ne privește cu ochi cinstiți și afirmă că NaN este întotdeauna mai mare decât orice alt număr, și, prin urmare, decât infinitatea. Se poate spune același lucru și pentru -NaN. Salut, iubitorilor de analize matematice! Dar trebuie să ținem minte 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

Î încă un salut neașteptat de la bază. Și din nou trebuie să memorezi că pentru tipurile double precision și numeric se aplică rotunjiri diferite. Pentru numeric – este rotunjirea normală, când 0,5 se rotunjește în sus, iar pentru double precision – rotunjirea 0,5 se face spre cel mai apropiat număr întreg par.

Banii sunt ceva special

SELECT '10'::money::float8

EROARE:  nu se poate converti tipul money în double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Eroare **********
EROARE: nu se poate converti tipul money în double precision
Starea SQL: 42846
Simbol: 19

Din perspectiva PostgreSQL, banii nu sunt un număr real. De asemenea, din perspectiva unor indivizi, nu sunt. Trebuie să ținem minte că conversia tipului money este posibilă doar în tipul numeric, la fel cum tipul numeric poate fi convertit doar în tipul money. Cu acesta, poți juca cum îți dorești. Dar nu vor mai fi aceiași bani.

Smallint și generarea secvenței

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

EROARE:  funcția generate_series(smallint, smallint, smallint) nu este unică
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
INDICAȚIE:  Nu am putut alege cea mai bună funcție candidat. Poate că va trebui să adaugi conversii de tipuri explicite.
********** Eroare **********
EROARE: funcția generate_series(smallint, smallint, smallint) nu este unică
Starea SQL: 42725
Sugestie: Nu am putut alege cea mai bună funcție candidată. Poate că va trebui să adaugi conversii de tipuri explicite.
Simbol: 18

PostgreSQL nu acceptă compromisuri. Ce secvențe ar putea proveni din smallint? int, fără excepție! Prin urmare, atunci când se încearcă executarea interogării de mai sus, baza de date încearcă să convertească smallint într-un alt tip numeric și constată că există mai multe posibile conversii. Ce conversie ar trebui aleasă? Asta nu poate decide, așa că apare o eroare.

Dosarul numărul doi. "char"/char/varchar/text

Există o serie de ciudățenii și în cazul tipurilor de caractere. Să ne familiarizăm și cu ele.

Ce fel de trucuri sunt acestea?

SELECT 'PETYA'::"char"
     , 'PETYA'::"char"::bytea
     , 'PETYA'::char
     , 'PETYA'::char::bytea

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

Ce este acest tip "char", cine este acest clown? Nu avem nevoie de așa ceva... Pentru că se pretează ca un char obișnuit, deși este în ghilimele. Și se deosebește de char-ul obișnuit, care este fără ghilimele, prin faptul că afișează doar primul byte al reprezentării string-ului, în timp ce un char normal afișează primul caracter. În cazul nostru, primul caracter este litera П, care în reprezentarea unicode ocupă 2 byte, așa cum reiese din conversia rezultatului în tipul bytea. Iar tipul "char" ia doar primul byte al acestei reprezentări unicode. Atunci, la ce ne trebuie acest tip? Documentația PostgreSQL spune că este un tip special, utilizat pentru nevoi particulare. Așa că este puțin probabil să ne fie de folos. Dar uită-te-i în ochi și nu te înșela când îl întâlnești, cu comportamentul său special.

Spații neconforme. Din vedere, din inimă.

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

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

Privind exemplul dat. Am transformat special toate rezultatele în tipul bytea, pentru a fi vizibil ce conține. Unde sunt spațiile terminale după conversia la tipul varchar(6)? Documentația afirmă concise: „Când o valoare character este convertită într-un alt tip de caracter, spațiile de umplere sunt eliminate”. Această antipatie trebuie memorată. Și observați că, dacă o constantă de string în ghilimele este imediat convertită la tipul varchar(6), spațiile finale sunt păstrate. Iată așa minuni.

Dosarul numărul trei. json/jsonb

JSON este o structură separată, care trăiește o viață proprie. De aceea, entitățile sale și cele ale PostgreSQL diferă puțin. Iată exemple.

Johnson și Johnson. Simțiți diferența

SELECT 'null'::jsonb IS NULL

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

Problema este că JSON are propriul său concept de null, care nu este echivalent cu NULL în PostgreSQL. În același timp, obiectul JSON însă poate avea o valoare NULL, așa că expresia SELECT null::jsonb IS NULL (observați absența ghilimelelor) va returna de data aceasta true.

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

Problema este că json și jsonb sunt structuri complet diferite. În json, obiectul este stocat exact așa cum este, în timp ce în jsonb este stocat sub formă de structură indexată. De aceea, în cel de-al doilea caz, valoarea obiectului pentru cheia 1 a fost înlocuită de la [1, 2, 3] la [7, 8, 9], care a venit în structură la sfârșit cu aceeași cheie.

Din fața apei nu se bea

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 JSONB modifică formatul numerelor reale, aducându-le la forma clasică. În cazul tipului JSON, acest lucru nu se întâmplă. E un pic ciudat, dar este dreptul său.

Dosarul numărul patru. date/time/timestamp

Cu tipurile de dată/timp există și anumite ciudățenii. Să ne uităm la ele. O mențiune, unele dintre aceste comportamente devin clare dacă înțelegi bine cum funcționează fusurile orare. Dar acesta este de asemenea subiect pentru un articol separat.

Al meu, al tău, nu înțeleg

SELECT '08-Jan-99'::date

ERROR: valoarea câmpului date/timp este în afara limitelor: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT: Poate ai nevoie de o setare diferită de "datestyle".
********** Eroare **********
ERROR: valoarea câmpului date/timp este în afara limitelor: "08-Jan-99"
Starea SQL: 22008
Sugestie: Poate ai nevoie de o setare diferită de "datestyle".
Simbol: 8

Se părea că nu este nimic neclar aici? Totuși, baza nu înțelege dacă am pus pe prima poziție - anul sau ziua? Și concluzionează că este 99 ianuarie 2008, ceea ce îi explodează mintea. De fapt, în cazul transmiterii datelor în format text, trebuie să verifici foarte atent cât de corect le-a recunoscut baza (în special, să analizezi parametrul datestyle cu comanda SHOW datestyle), deoarece ambiguitățile în această privință pot costa foarte mult.

De unde ai apărut așa?

SELECT '04:05 Europa/București'::time

EROARE: sintaxă de input invalidă pentru tipul time: "04:05 Europa/București"
LINE 1: SELECT '04:05 Europa/București'::time
               ^
********** Eroare **********
EROARE: sintaxă de input invalidă pentru tipul time: "04:05 Europa/București"
Starea SQL: 22007
Simbol: 8

De ce nu poate baza de date să înțeleagă timpul specificat explicit? Pentru că pentru fusul orar este specificat un nume complet, nu o abreviere, care are sens doar în contextul unei date, deoarece ia în considerare istoria modificării fusurilor orare, iar aceasta nu funcționează fără o dată. De asemenea, expresia timpului generează întrebări — ce a vrut, de fapt, programatorul? Așadar, totul este logic dacă te gândești la detalii.

Ce nu îi convine?

Imaginați-vă o situație. Aveți un câmp în tabel cu tipul timestamptz. Doriți să-l indexați. Dar înțelegeți că construirea unui index pe acest câmp nu este întotdeauna justificată din cauza selecției sale ridicate (aproape toate valorile acestui tip vor fi unice). Prin urmare, decideți să reduceți selecția indexului, transformând acest tip în dată. Și obțineți o surpriză:

CREATE INDEX "iIdent-DateLastUpdate"
  ON public."Ident" USING btree
  (("DTLastUpdate"::date));

EROARE: funcțiile din expresia indexului trebuie să fie marcate IMMUTABLE
********** Eroare **********
EROARE: funcțiile din expresia indexului trebuie să fie marcate IMMUTABLE
Starea SQL: 42P17

Care este problema? Că pentru a transforma tipul timestamptz în tipul date se folosește valoarea parametrului de sistem TimeZone, ceea ce face funcția de conversie dependentă de parametru, adică variabilă (volatile). Astfel de funcții nu sunt permise în index. În acest caz, trebuie să specificați explicit în ce fus orar se face conversia tipului.

Când now nu este deloc now

Suntem obișnuiți ca now() să returneze data/timpul curent având în vedere fusul orar. Dar priviți la următoarele interogă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 se returnează aceleași, indiferent de cât timp a trecut de la cererea anterioară! Care este problema? Că now() nu este timpul curent, ci timpul de început al tranzacției curente. Prin urmare, în cadrul tranzacției acesta nu se schimbă. Orice cerere, lansată în afara cadrului tranzacției, este încapsulată implicit într-o tranzacție, de aceea nu observăm că timpul returnat de o cerere simplă SELECT now(); de fapt nu este cel curent... Dacă doriți să obțineți timpul curent real, trebuie să folosiți funcția clock_timestamp().

Dossier numărul cinci. bit

Puțin ciudat

SELECT '111'::bit(4)

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

Din ce parte ar trebui să adăugăm biți în cazul extinderii tipului? Pare că din stânga. Dar baza are o altă părere despre aceasta. Fiți atenți: în cazul necorespunderii numărului de biți la conversia tipului, veți obține ceva complet diferit de ceea ce ați dorit. Acest lucru se aplică atât la adăugarea biților din dreapta, cât și la tăierea biților. De asemenea, din dreapta...

Dossier numărul șase. Arhive

Chiar și NULL nu s-a întâmplat

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 este cazul. Se returnează un array. De ce? Pentru că, în acest caz, baza convertește NULL în array de întregi și cheamă implicit funcția array_cat. Dar rămâne neclar de ce acest "pisoi al array-ului" nu face array-ul să devină NULL. Un astfel de comportament trebuie pur și simplu memorat.

Să facem un rezumat. Există multe ciudățenii. Majoritatea dintre ele, desigur, nu sunt atât de critice încât să vorbim despre un comportament flagrant inadecvat. Iar altele se explică prin comoditatea utilizării sau frecvența aplicabilității lor în diferite situații. Dar, în același timp, sunt multe surprize. De aceea este bine să le cunoaștem. Dacă găsiți ceva și mai ciudat sau neobișnuit în comportamentul unor tipuri, scrieți în comentarii, voi adăuga cu plăcere la dosarele existente despre ele.

Sursa: habr.com