Sumnjivi tipovi

Na njihovom vanjskom izgledu ništa ne izaziva sumnju. Štoviše, čini se da su ti dobro poznati i dugi niz godina. No, to je samo do trenutka kada ih ne provjeriš. Tada će pokazati svoju zlu prirodu, djelujući potpuno suprotno od tvojih očekivanja. Ponekad izbacuju stvari zbog kojih ti se kose dižu na glavu — na primjer, gube povjerljive podatke koji su im povjereni. Kada ih suočiš, tvrde da jedni druge ne poznaju, iako u sjeni marljivo rade zajedno. Vrijeme je da ih konačno otkrijemo. Razmotrimo zajedno ove sumnjive tipove.

Tipizacija podataka u PostgreSQL-u, uz svu svoju logičnost, doista ponekad donosi vrlo čudne iznenađenja. U ovom članku pokušat ćemo razjasniti neke njihove osobitosti, razumjeti uzrok njihovog čudnog ponašanja i shvatiti kako izbjeći probleme u svakodnevnoj praksi. Istina, ovaj članak sam sastavio i kao svojevrsni vodič za sebe, vodič na koji se lako može obratiti u spornih situacijama. Stoga će se dodatno dopunjavati kako budem otkrivao nova iznenađenja od sumnjivih tipova. Pa, krenimo, neumorni tragači za podacima!

Dosje broj jedan. real/double precision/numeric/money

Naizgled, numerički tipovi su najmanje problematični s obzirom na iznenađenja u ponašanju. Ali, kako to obično biva, nije uvijek tako. Zato počinjemo s njima. Dakle...

Zaboravili smo brojiti

SELECT 0.1::real = 0.1

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

U čemu je problem? Riječ je o tome da PostgreSQL neuređenu konstantu 0.1 prevodi u tip double precision i pokušava je usporediti s 0.1 tipa real. A to su apsolutno različite vrijednosti! Suština leži u reprezentaciji stvarnih brojeva u računalnoj memoriji. Budući da 0.1 nije moguće predstaviti kao konačnu binarnu frakciju (to bi u binarnom formatu bilo 0.0(0011)), brojevi različitih preciznosti će se razlikovati, odatle i rezultat koji ukazuje da nisu jednaki. Uopće, ovo je tema za poseban članak, za detaljnije objašnjenje ovdje neću ići.

Odakle greška?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Greška **********
ERROR: syntax error at or near "("
SQL stanje: 42601
Simbol: 24

Mnogi znaju da PostgreSQL dopušta funkcionalno zapisivanje konverzija tipova. Dakle, možete pisati ne samo 1::int, već i int(1), što će biti ekvivalentno. No, to ne važi za tipove čija imena se sastoje od više riječi! Stoga, ako želite konvertirati numeričku vrijednost u tip double precision u funkcionalnom obliku, koristite alias ovog tipa float8, tj. SELECT float8(1).

Što je veće od beskonačnosti?

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

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

Eto ga! Ispostavlja se da postoji nešto veće od beskonačnosti, a to je NaN! Pri tome, dokumentacija PostgreSQL gledajući nas iskreno tvrdi da je NaN unaprijed veći od bilo kojeg drugog broja, pa tako i od beskonačnosti. Isto vrijedi i za -NaN. Pozdrav, ljubitelji matematičke analize! No, treba imati na umu da se sve to događa u kontekstu realnih brojeva.

Zaokruživanje očiju

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

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

Još jedan neočekivani pozdrav iz baze. Ponovno treba zapamtiti da za tipove double precision i numeric vrijede različita zaokruživanja. Za numeric - obični, kada se 0,5 zaokružuje na veću stranu, a za double precision - zaokruživanje 0,5 se događa prema najbližem parnom cijelom broju.

Novac je nešto posebno

SELECT '10'::money::float8

ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Greška **********
ERROR: cannot cast type money to double precision
SQL stanje: 42846
Simbol: 19

Prema PostgreSQL-u, novac nije realan broj. Prema nekim pojedincima, također. No, trebamo zapamtiti da je konverzija tipa money moguća samo u tip numeric, kao što se tip money može konvertirati samo u tip numeric. A onda se s njim već može igrati kako duša želi. Ali to neće više biti ti novci.

Smallint i generacija sekvenci

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.
********** Greška **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL stanje: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Simbol: 18

PostgreSQL ne voli sitničarenje. Koje to sekvence na temelju smallint? int, najmanje! Stoga, kada pokušajte izvršiti gornji upit, baza pokušava pretvoriti smallint u neki drugi cjelobrojni tip i vidi da može biti više takvih pretvorbi. Koju pretvorbu odabrati? Ona to ne može odlučiti, i stoga pada s greškom.

Dosje broj dva. „char“/char/varchar/text

Slijedi niz čudnosti i kod tipova znakova. Upoznajmo se i s njima.

Što su ovo za trikovi?

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

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

Što je to za tip „char“, tko je taj klaun? Takvi nam nisu potrebni… Jer se ponaša kao običan char, bez obzira što je u navodnicima. A razlikuje se od običnog char, koji je bez navodnika, po tome što ispisuje samo prvi bajt nizovnog prikaza, dok normalni char ispisuje prvi znak. U našem slučaju, prvi znak je slovo P, koje u unicode prikazu zauzima 2 bajta, što potvrđuje konverzija rezultata u tip bytea. A tip „char“ uzima samo prvi bajt ovog unicode prikaza. Pa zašto je ovaj tip potreban? Dokumentacija PostgreSQL-a kaže da je to poseban tip koji se koristi za posebne potrebe. Dakle, malo je vjerojatno da će nam zatrebati. Ali pogledajte mu u oči i ne pogriješite kada ga sretnete s njegovim posebnim ponašanjem.

Suvišni razmaci. Izvan vida, iz srca van.

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

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

Pogledajte prikazani primjer. Namjerno sam sve rezultate pretvorio u tip bytea kako bi bilo očigledno što se unutra nalazi. Gdje su završni razmaci nakon pretvaranja u tip varchar(6)? Dokumentacija suho tvrdi: „Prilikom pretvaranja vrijednosti karaktera u neki drugi tip znakova, dodani razmaci se odbacuju“. Ovu nepodnosivost treba zapamtiti. I primijetite da, ako se nizovna konstanta u navodnicima odmah pretvori u tip varchar(6), završni razmaci se čuvaju. Takva čuda.

Dosje broj tri. json/jsonb

JSON je posebna struktura koja živi svojim životom. Stoga se njene entitete i entiteti PostgreSQL-a malo razlikuju. Evo primjera.

Johnson & Johnson. Osjetite razliku

SELECT 'null'::jsonb IS NULL

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

Cijela stvar je u tome što JSON ima svoju null vrijednost, koja nije ekvivalent NULL-u u PostgreSQL-u. U isto vrijeme, sam JSON objekt može imati NULL vrijednost, pa će izraz SELECT null::jsonb IS NULL (obratite pažnju na odsustvo jednostrukih navodnika) ovaj put vratiti true.

Jedno slovo mijenja sve

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

Cijela stvar je u tome što su json i jsonb potpuno različite strukture. U json-u objekt se čuva onakav kakav jest, dok se u jsonb čuva već u obliku razrađene indeksirane strukture. Zbog toga je u drugom slučaju vrijednost objekta po ključu 1 zamijenjena s [1, 2, 3] na [7, 8, 9], koja je došla u strukturu na samom kraju s istim ključem.

S lica vode se ne pije

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

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

PostgreSQL u implementaciji JSONB mijenja formatiranje stvarnih brojeva, pretvarajući ih u klasični oblik. Za tip JSON to se ne dešava. Malo je to čudno, ali je to njegovo pravo.

Dosije broj četiri. datum/vrijeme/timestamp

S tipovima datuma/vremena također postoje određene nejasnoće. Pogledajmo ih. Odmah ću napomenuti da neke od osobitosti ponašanja postaju jasne kada dobro razumijete suštinu rada s vremenskim zonama. No to je također tema za poseban članak.

Moja tvoja ne razumije

SELECT '08-Jan-99'::date

ERROR:  datum/vrijeme vrijednost izvan opsega: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Možda trebate drugačije podešavanje "datestyle".
********** Greška **********
ERROR: datum/vrijeme vrijednost izvan opsega: "08-Jan-99"
SQL stanje: 22008
Savjet: Možda trebate drugačije podešavanje "datestyle".
Simbol: 8

Naizgled nema tu ništa nejasno? No ipak baza ne razumije što smo tu stavili na prvo mjesto — godinu ili dan? I odlučuje da je to 99. januara 2008. godine, što joj potpuno zbunjuje. U principu, u slučaju prijenosa datuma u tekstualnom formatu, potrebno je vrlo pažljivo provjeriti koliko je tačno baza ih prepoznala (posebno analizirati parametar datestyle komandnom SHOW datestyle), jer nejasnoće u ovom pitanju mogu koštati vrlo skupo.

Odakle si se ti uzeo?

ODABERITE '04:05 Europa/Moskva'::vrijeme

GREŠKA: neispravna ulazna sintaksa za tip vremena: "04:05 Europa/Moskva"
LINIJA 1: ODABERITE '04:05 Europa/Moskva'::vrijeme
               ^
********** Greška **********
GREŠKA: neispravna ulazna sintaksa za tip vremena: "04:05 Europa/Moskva"
SQL-stanje: 22007
Simbol: 8

Zašto baza podataka ne može razumjeti jasno navedeno vrijeme? Zato što je za vremensku zonu navedena ne kratica, već puno ime, koje ima smisla samo u kontekstu datuma, jer uzima u obzir povijest promjena vremenskih zona, a ona ne funkcionira bez datuma. Sam izraz vremenskog niza također postavlja pitanja — što je programer zapravo mislio? Tako da je ovdje sve logično, ako se razjasni.

Što mu nije u redu?

Zamislite situaciju. Imate u tablici polje tipa timestamptz. Želite ga indeksirati. No shvaćate da gradnja indeksa po ovom polju nije uvijek opravdana zbog njegove visoke selektivnosti (gotovo sve vrijednosti ovog tipa bit će jedinstvene). Stoga odlučujete smanjiti selektivnost indeksa, pretvarajući ovaj tip u datum. I dobijate iznenađenje:

KREIRAJ INDEKS "iIdent-DateLastUpdate"
  NA javnom."Ident" KORIŠTENJEM btree
  (("DTLastUpdate"::datum));

GREŠKA: funkcije u izrazu indeksa moraju biti označene kao IMMUTABLE
********** Greška **********
GREŠKA: funkcije u izrazu indeksa moraju biti označene kao IMMUTABLE
SQL-stanje: 42P17

U čemu je stvar? U tome što se za pretvaranje tipa timestamptz u tip datum koristi vrijednost sistemskog parametra TimeZone, što čini funkciju konverzije tipa ovisnom o podešavanju, tj. promjenjivom (volatile). Takve funkcije nisu dopuštene u indeksu. U ovom slučaju potrebno je izričito navesti u kojoj vremenskoj zoni se vrši konverzija tipa.

Kada now nije baš now

Navikli smo da now() vraća trenutni datum/vrijeme uzimajući u obzir vremensku zonu. No pogledajte sljedeće upite:

POČNI TRANSAKCIJU;
ODABERITE now();

            now
  vremenska oznaka s vremenskom zonom
-----------------------------
2019-11-26 13:13:04.271419+03

...

ODABERITE now();

            now
  vremenska oznaka s vremenskom zonom
-----------------------------
2019-11-26 13:13:04.271419+03

...

ODABERITE now();

            now
  vremenska oznaka s vremenskom zonom
-----------------------------
2019-11-26 13:13:04.271419+03

POPRAVI;

Datum/vrijeme ostaje isto bez obzira koliko vremena je prošlo od prethodnog zahtjeva! Što nije u redu? To je zato što now() nije trenutno vrijeme, već vrijeme početka trenutne transakcije. Stoga se unutar transakcije ne mijenja. Svaki zahtjev pokrenut izvan okvira transakcije, implicitno se obavija u transakciju, zbog čega ne primjećujemo da vrijeme koje se dobije jednostavnim upitom SELECT now(); zapravo nije trenutačno... Ako želite dobiti ispravno trenutno vrijeme, trebate koristiti funkciju clock_timestamp().

Dosje broj pet. bit

Pomalo čudno

SELECT '111'::bit(4)

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

S koje strane treba dodavati bitove u slučaju proširenja tipa? Čini se da s lijeve strane. No, baza ima drugačije mišljenje o tome. Budite oprezni: ako se broj bitova ne podudara pri konverziji tipa, dobit ćete posve drugačije od onoga što ste željeli. To se odnosi i na dodavanje bitova zdesna, kao i na skraćivanje bitova. Također zdesna…

Dosje broj šest. Masevi

Čak ni NULL nije eksplodirao

SELECT ARRAY[1, 2] || NULL

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

Kao obični ljudi, odgojeni na SQL-u, očekujemo da će rezultat ovog izraza biti NULL. No, to nije slučaj. Vraća se niz. Zašto? Zato što baza u ovom slučaju konvertira NULL u niz cijelih brojeva i implicitno poziva funkciju array_cat. No, još uvijek nije jasno zašto taj „nizovni mačak” ne nulira niz. Ovo ponašanje također treba samo pamtiti.

Zaključimo. Ima puno čudnosti. Većina njih ne predstavlja toliko kritične probleme da bismo govorili o očigledno neadekvatnom ponašanju. Ostale se objašnjavaju praktičnošću ili učestalošću njihove primjenjivosti u određenim situacijama. No, u isto vrijeme, iznenađenja je mnogo. Stoga ih treba poznavati. Ako pronađete još nešto čudno ili neobično u ponašanju bilo kojih tipova, slobodno pišite u komentarima, s veseljem ću dopuniti postojeće dosjee.

Izvor: habr.com

Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere 🔥 Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere | ProHoster