🥇Sumnjivi tipovi

Nema ništa sumnjivo u njihovom izgledu. Štaviše, čak vam se čine dobro i dugo poznatim. Ali to je samo dok ih ne provjerite. Ovdje pokazuju svoju podmuklu prirodu, radeći potpuno drugačije nego što ste očekivali. A ponekad učine nešto od čega vam se diže kosa na glavi - na primjer, izgube tajne podatke koji su im povjereni. Kada se suočite sa njima, oni tvrde da se ne poznaju, iako u senci vredno rade pod istom haubom. Vrijeme je da ih konačno dovedemo u čistu vodu. Hajde da se pozabavimo i ovim sumnjivim tipovima.

Unos podataka u PostgreSQL, uprkos svojoj logici, ponekad predstavlja vrlo čudna iznenađenja. U ovom članku pokušat ćemo razjasniti neke od njihovih čudaka, razumjeti razlog njihovog čudnog ponašanja i razumjeti kako ne nailaziti na probleme u svakodnevnoj praksi. Istini za volju, sastavio sam ovaj članak i kao neku vrstu priručnika za sebe, referencu na koju bi se lako moglo pozvati u kontroverznim slučajevima. Stoga će se dopunjavati kako budu otkrivena nova iznenađenja sumnjivih tipova. Dakle, idemo, o neumorni tragači baze podataka!

Dosije broj jedan. stvarni/dvostruke preciznosti/numerički/novac

Čini se da su numerički tipovi najmanje problematični u smislu iznenađenja u ponašanju. Ali bez obzira kako je. Pa počnimo s njima. Dakle…

Zaboravio sam da brojim

SELECT 0.1::real = 0.1

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

Sta je bilo? Problem je u tome što PostgreSQL konvertuje netipiziranu konstantu 0.1 u dvostruku preciznost i pokušava je uporediti sa 0.1 realnog tipa. A ovo su potpuno različita značenja! Ideja je da se realni brojevi predstavljaju u mašinskoj memoriji. Budući da se 0.1 ne može predstaviti kao konačni binarni razlomak (binarno bi bio 0.0(0011), brojevi s različitim dubinama bita će biti različiti, pa će rezultat biti da nisu jednaki. Općenito govoreći, ovo je tema za poseban članak, ovdje neću pisati detaljnije.

Odakle dolazi greška?

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

Mnogi ljudi znaju da PostgreSQL dozvoljava funkcionalnu notaciju za uvođenje tipova. To jest, možete napisati ne samo 1::int, već i int(1), što će biti ekvivalentno. Ali ne za tipove čija se imena sastoje od nekoliko riječi! Stoga, ako želite da pretvorite numeričku vrijednost u tip dvostruke preciznosti u funkcionalnom obliku, koristite pseudonim ovog tipa float8, odnosno SELECT float8(1).

Šta je veće od beskonačnosti?

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

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

Pogledajte kako je! Ispostavilo se da postoji nešto veće od beskonačnosti, a to je NaN! U isto vrijeme, PostgreSQL dokumentacija nas gleda poštenim očima i tvrdi da je NaN očito veći od bilo kojeg drugog broja, a samim tim i beskonačnost. Suprotno vrijedi i za -NaN. Zdravo, ljubitelji matematike! Ali moramo zapamtiti da sve ovo djeluje 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. Opet, zapamtite da tipovi dvostruke preciznosti i numerički imaju različite efekte zaokruživanja. Za numerički - uobičajeni, kada se 0,5 zaokružuje nagore, a za dvostruku preciznost - 0,5 se zaokružuje 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
                          ^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19

Prema PostgreSQL-u, novac nije pravi broj. I prema nekim pojedincima. Moramo zapamtiti da je prebacivanje tipa novca moguće samo na numerički tip, kao što se samo numerički tip može prebaciti na tip novca. Ali sada se možete igrati s njim kako vam srce želi. Ali to neće biti isti novac.

Smallint i generiranje sekvence

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 ne voli da gubi vrijeme na sitnice. Koje su ove sekvence zasnovane na smallintu? int, ni manje ni više! Stoga, kada pokušava izvršiti gornji upit, baza podataka pokušava baciti smallint na neki drugi cjelobrojni tip i vidi da može postojati nekoliko takvih cast. Koju glumačku ekipu odabrati? Ona ne može odlučiti o tome, pa se sruši s greškom.

Fajl broj dva. "char"/char/varchar/text

Brojne neobičnosti su također prisutne u tipovima likova. Hajde da ih upoznamo.

Kakvi su to trikovi?

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

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

Koja je ovo vrsta "čarke", kakav je ovo klovn? Ne trebaju nam... Zato što se pretvara da je običan char, iako je pod navodnicima. I razlikuje se od običnog char, koji je bez navodnika, po tome što izlazi samo prvi bajt string reprezentacije, dok normalan char ispisuje prvi znak. U našem slučaju, prvi znak je slovo P, koje u unicode prikazu zauzima 2 bajta, što se vidi pretvaranjem rezultata u tip bytea. A tip “char” uzima samo prvi bajt ove unicode reprezentacije. Zašto je onda potreban ovaj tip? PostgreSQL dokumentacija kaže da je ovo poseban tip koji se koristi za posebne potrebe. Tako da je malo vjerovatno da će nam trebati. Ali pogledajte ga u oči i nećete pogriješiti kada ga upoznate sa njegovim posebnim ponašanjem.

Extra spaces. Daleko od očiju daleko od srca

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 dati primjer. Posebno sam konvertovao sve rezultate u tip bytea, tako da se jasno vidi šta se tu nalazi. Gdje su zadnji razmaci nakon prebacivanja na varchar(6)? Dokumentacija sažeto kaže: "Kada se vrijednost karaktera prebacuje na drugi tip karaktera, završni razmak se odbacuje." Ovo nenaklonost se mora zapamtiti. I imajte na umu da ako se navedena string konstanta direktno prebaci na tip varchar(6), razmaci na kraju ostaju sačuvani. Takva su čuda.

Fajl broj tri. json/jsonb

JSON je zasebna struktura koja živi svoj život. Stoga se njegovi entiteti i entiteti PostgreSQL-a malo razlikuju. Evo primjera.

Džonson i Džonson. osjetite razliku

SELECT 'null'::jsonb IS NULL

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

Stvar je u tome što JSON ima svoj vlastiti null entitet, koji nije analog NULL-a u PostgreSQL-u. U isto vrijeme, sam JSON objekat može imati vrijednost NULL, tako da će izraz SELECT null::jsonb IS NULL (obratite pažnju na odsustvo pojedinačnih navodnika) ovog puta vratiti true.

Jedno slovo menja 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]}

Stvar je u tome što su json i jsonb potpuno različite strukture. U json-u, objekat je pohranjen kakav jeste, au jsonb-u je već pohranjen u obliku raščlanjene, indeksirane strukture. Zato je u drugom slučaju vrijednost objekta ključem 1 zamijenjena sa [1, 2, 3] na [7, 8, 9], koji je u strukturu došao na samom kraju sa istim ključem.

Ne pijte vodu sa lica

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

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

PostgreSQL u svojoj JSONB implementaciji mijenja formatiranje realnih brojeva, dovodeći ih u klasični oblik. Ovo se ne dešava za tip JSON. Malo čudno, ali je u pravu.

Fajl broj četiri. datum/vrijeme/vremenska oznaka

Postoje i neke neobičnosti sa tipovima datuma/vremena. Pogledajmo ih. Odmah da rezervišem da neke karakteristike ponašanja postaju jasne ako dobro razumete suštinu rada sa vremenskim zonama. Ali ovo je također tema za poseban članak.

Moje tvoje ne razumeju

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

Čini se, šta je tu neshvatljivo? Ali baza podataka još uvijek ne razumije šta stavljamo na prvo mjesto - godinu ili dan? I ona odlučuje da je 99. januar 2008. godine, što joj oduševljava. Uopšteno govoreći, kada prenosite datume u tekstualnom formatu, morate vrlo pažljivo provjeriti koliko ih je baza podataka ispravno prepoznala (posebno analizirajte parametar datestyle naredbom SHOW datestyle), budući da nejasnoće po ovom pitanju mogu biti vrlo skupe.

Odakle ti ovo?

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

Zašto baza podataka ne može razumjeti eksplicitno određeno vrijeme? Jer vremenska zona nema skraćenicu, već puni naziv, što ima smisla samo u kontekstu datuma, jer uzima u obzir istoriju promjena vremenske zone, a ne radi bez datuma. I sama formulacija vremenske linije postavlja pitanja - šta je programer zaista mislio? Dakle, ovdje je sve logično, ako pogledate.

Šta nije u redu s njim?

Zamislite situaciju. Imate polje u vašoj tabeli sa tipom timestamptz. Želite da ga indeksirate. Ali razumijete da izgradnja indeksa na ovom polju nije uvijek opravdana zbog njegove visoke selektivnosti (gotovo sve vrijednosti ovog tipa će biti jedinstvene). Stoga odlučujete da smanjite selektivnost indeksa prebacivanjem tipa na datum. I dobićete iznenađenje:

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

Sta je bilo? Činjenica je da se za pretvaranje tipa timestamptz u tip datuma koristi vrijednost sistemskog parametra TimeZone, što čini funkciju konverzije tipa ovisnom o prilagođenom parametru, tj. volatile. Takve funkcije nisu dozvoljene u indeksu. U ovom slučaju morate eksplicitno naznačiti u kojoj se vremenskoj zoni vrši prebacivanje tipa.

Kada sada uopšte nije ni sada

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

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;

Datum/vrijeme se vraća isto bez obzira koliko je vremena prošlo od prethodnog zahtjeva! Sta je bilo? Činjenica je da now() nije trenutno vrijeme, već vrijeme početka trenutne transakcije. Stoga se ne mijenja unutar transakcije. Svaki upit pokrenut izvan opsega transakcije je umotan u transakciju implicitno, zbog čega ne primjećujemo da je vrijeme vraćeno jednostavnim SELECT now(); u stvari, ne trenutno... Ako želite dobiti iskreno trenutno vrijeme, trebate koristiti funkciju clock_timestamp().

Fajl broj pet. bit

Malo čudno

SELECT '111'::bit(4)

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

Koju stranu treba dodati bitove u slučaju proširenja tipa? Čini se da je na lijevoj strani. Ali samo baza ima drugačije mišljenje o ovom pitanju. Budite pažljivi: ako se broj cifara ne poklapa pri ubacivanju tipa, nećete dobiti ono što ste željeli. Ovo se odnosi i na dodavanje bitova udesno i na podrezivanje bitova. Takođe sa desne strane...

Dosije broj šest. Nizovi

Čak se i NULL nije aktivirao

SELECT ARRAY[1, 2] || NULL

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

Kao normalni ljudi odgajani na SQL-u, očekujemo da će rezultat ovog izraza biti NULL. Ali nije ga bilo. Vraća se niz. Zašto? Jer u ovom slučaju baza izbacuje NULL na cjelobrojni niz i implicitno poziva funkciju array_cat. Ali i dalje ostaje nejasno zašto ova "mačka niza" ne resetuje niz. Ovo ponašanje takođe treba samo zapamtiti.

Sažmite. Ima dosta čudnih stvari. Većina njih, naravno, nije toliko kritična da bi govorila o očigledno neprimjerenom ponašanju. A drugi se objašnjavaju jednostavnošću upotrebe ili učestalošću njihove primjene u određenim situacijama. Ali u isto vrijeme, ima mnogo iznenađenja. Stoga, morate znati o njima. Ako vam je još nešto čudno ili neobično u ponašanju bilo koje vrste, napišite u komentarima, rado ću dodati u dosijee dostupne na njima.

izvor: www.habr.com

Sumnjivi tipovi