Sumnjivi tipovi

U njihovom izgledu nema ništa sumnjivo. Štoviše, čak vam se čine dobro i dugo poznatima. Ali to je samo dok ih ne provjerite. Tu pokazuju svoju podmuklu prirodu radeći potpuno drugačije nego što ste očekivali. A ponekad učine nešto od čega vam se digne kosa na glavi - primjerice izgube tajne podatke koji su im povjereni. Kad ih suočite, tvrde da se ne poznaju, iako u sjeni vrijedno rade pod istom kapom. Vrijeme je da ih konačno izvedemo na čistu vodu. Pozabavimo se i ovim sumnjivim tipovima.

Upisivanje podataka u PostgreSQL, uza svu svoju logiku, ponekad predstavlja vrlo čudna iznenađenja. U ovom članku pokušat ćemo razjasniti neke od njihovih hirova, razumjeti razloge njihovog čudnog ponašanja i razumjeti kako izbjeći probleme u svakodnevnoj praksi. Iskreno govoreći, ovaj sam članak sastavio i kao neku vrstu priručnika za sebe, priručnika na koji se lako može pozvati u kontroverznim slučajevima. Stoga će se nadopunjavati kako se budu otkrivala nova iznenađenja sumnjivih vrsta. Pa, idemo, o neumorni tragači baza podataka!

Dosje broj jedan. stvarna/dvostruka preciznost/numerički/novac

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

Zaboravio sam brojati

SELECT 0.1::real = 0.1

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

Što je bilo? Problem je u tome što PostgreSQL pretvara netipiziranu konstantu 0.1 u dvostruku preciznost i pokušava je usporediti s 0.1 stvarnog tipa. A to su sasvim druga značenja! Ideja je predstaviti stvarne brojeve u memoriji stroja. Budući da se 0.1 ne može predstaviti kao konačni binarni razlomak (bio bi 0.0(0011) u binarnom obliku), brojevi s različitim dubinama bita bit će različiti, što znači 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 dopušta funkcionalnu notaciju za pretvaranje tipa. To jest, možete napisati ne samo 1::int, već i int(1), što će biti ekvivalentno. Ali ne za vrste čija se imena sastoje od nekoliko riječi! Stoga, ako želite pretvoriti numeričku vrijednost u tip dvostruke preciznosti u funkcionalnom obliku, koristite alias ovog tipa float8, to jest, SELECT float8(1).

Što je veće od beskonačnosti?

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

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

Pogledaj kakav 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čan. Suprotno vrijedi i za -NaN. Pozdrav, ljubitelji matematike! Ali moramo imati na umu da sve ovo funkcionira u kontekstu stvarnih 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 dvostruka preciznost i numerički tipovi imaju različite učinke zaokruživanja. Za numerički - uobičajeni, kada se 0,5 zaokružuje naviše, 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 stvaran broj. Prema nekim pojedincima također. Moramo upamtiti da je pretvaranje novčane vrste moguće samo u numeričku vrstu, baš kao što se samo numerička vrsta može pretvoriti u novčanu vrstu. Ali sada se možete igrati s njim kako vam srce želi. Ali to neće biti isti novac.

Smallint i generiranje niza

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 gubiti vrijeme na sitnice. Koje su ove sekvence temeljene na smallint-u? int, ništa manje! Stoga, kada pokušava izvršiti gornji upit, baza podataka pokušava pretvoriti smallint u neki drugi cjelobrojni tip i vidi da može biti nekoliko takvih pretvaranja. Koju glumačku postavu izabrati? Ona to ne može odlučiti i stoga se ruši s pogreškom.

Datoteka broj dva. "char"/char/varchar/tekst

Brojne su neobičnosti također prisutne u tipovima likova. Upoznajmo i njih.

Kakvi su to trikovi?

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

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

Kakav je ovo "char", kakav je ovo klaun? Takvi nam ne trebaju... Jer se pravi da je običan char, iako je pod navodnicima. I razlikuje se od običnog chara, koji je bez navodnika, po tome što ispisuje samo prvi bajt reprezentacije niza, dok normalni char ispisuje prvi znak. U našem slučaju prvi znak je slovo P, koje u unicode prikazu zauzima 2 bajta, što dokazuje pretvaranje rezultata u tip bytea. A tip "char" uzima samo prvi bajt ove unicode reprezentacije. Zašto je onda ova vrsta potrebna? PostgreSQL dokumentacija kaže da je ovo posebna vrsta koja se koristi za posebne potrebe. Dakle, malo je vjerojatno da će nam trebati. Ali pogledajte ga u oči i nećete pogriješiti kada ga sretnete s njegovim posebnim ponašanjem.

Dodatni prostori. Daleko od očiju, daleko od uma

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 navedeni primjer. Posebno sam sve rezultate pretvorio u tip bytea, tako da je bilo jasno vidljivo što je tamo. Gdje su razmaci na kraju nakon pretvaranja u varchar(6)? U dokumentaciji je sažeto navedeno: "Kada pretvarate vrijednost znaka u drugu vrstu znaka, prazna razmaka se odbacuje." Ova se nesklonost mora zapamtiti. I imajte na umu da ako se citirana string konstanta pretvori izravno u tip varchar(6), razmaci na kraju ostaju sačuvani. Takva su čuda.

Datoteka broj tri. json/jsonb

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

Johnson i Johnson. osjeti razliku

SELECT 'null'::jsonb IS NULL

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

Stvar je u tome što JSON ima svoj vlastiti null entitet, koji nije analogan NULL-u u PostgreSQL-u. U isto vrijeme, sam JSON objekt može imati vrijednost NULL, tako da će izraz SELECT null::jsonb IS NULL (imajte na umu izostanak 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]}

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

Ne pijte vodu s 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. To se ne događa za vrstu JSON. Malo čudno, ali u pravu je.

Datoteka broj četiri. datum/vrijeme/vremenska oznaka

Postoje i neke neobičnosti s vrstama datuma/vremena. Pogledajmo ih. Dopustite mi da odmah napomenem da neke značajke ponašanja postaju jasne ako dobro razumijete bit rada s vremenskim zonama. Ali ovo je također tema za poseban članak.

Moji tvoji ne razumiju

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 što je tu neshvatljivo? Ali baza podataka još uvijek ne razumije što ovdje stavljamo na prvo mjesto - godinu ili dan? I ona odluči da je 99. siječnja 2008., što joj se oduševi. Općenito govoreći, kada prenosite datume u tekstualnom formatu, morate vrlo pažljivo provjeriti koliko ih je pravilno baza podataka prepoznala (osobito analizirajte parametar datestyle s naredbom SHOW datestyle), budući da nejasnoće u 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 navedeno vrijeme? Zato što vremenska zona nema kraticu, već puni naziv, koji ima smisla samo u kontekstu datuma, jer uzima u obzir povijest promjena vremenske zone, a bez datuma ne radi. I sama formulacija vremenske linije postavlja pitanja - što je programer zapravo mislio? Dakle, ovdje je sve logično, ako pogledate.

Što ne valja s njim?

Zamislite situaciju. Imate polje u tablici s tipom timestamptz. Želite ga indeksirati. Ali razumijete da izgradnja indeksa na ovom polju nije uvijek opravdana zbog njegove visoke selektivnosti (gotovo sve vrijednosti ove vrste bit će jedinstvene). Stoga ste odlučili smanjiti selektivnost indeksa pretvaranjem tipa u datum. I dobit ć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

Što je bilo? Činjenica je da se za pretvaranje tipa timestamptz u tip datuma koristi vrijednost parametra sustava TimeZone, što čini funkciju pretvorbe tipa ovisnom o prilagođenom parametru, tj. nepostojan. Takve funkcije nisu dopuštene u indeksu. U tom slučaju, morate izričito naznačiti u kojoj se vremenskoj zoni izvodi pretvaranje tipa.

Kada sada uopće nije ni sada

Navikli smo da now() vraća 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! Što 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 implicitno je omotan u transakciju, zbog čega ne primjećujemo da je vrijeme vraćeno jednostavnim SELECT now(); zapravo, ne trenutno... Ako želite dobiti pošteno trenutno vrijeme, trebate koristiti funkciju clock_timestamp().

Datoteka broj pet. malo

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 lijevo. Ali samo baza ima drugačije mišljenje o ovom pitanju. Budite oprezni: ako se broj znamenki ne podudara prilikom bacanja vrste, nećete dobiti ono što ste željeli. Ovo se odnosi i na dodavanje bitova s ​​desne strane i na bitove za podrezivanje. Također s desne strane...

Datoteka broj šest. Nizovi

Čak se ni NULL nije aktivirao

SELECT ARRAY[1, 2] || NULL

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

Kao normalni ljudi odrasli na SQL-u, očekujemo da rezultat ovog izraza bude NULL. Ali nije bilo tamo. Vraća se niz. Zašto? Jer u ovom slučaju baza pretvara NULL u niz cijelih brojeva i implicitno poziva funkciju array_cat. Ali i dalje ostaje nejasno zašto ovaj "array cat" ne resetira niz. Ovo ponašanje također treba zapamtiti.

Rezimirati. Ima dosta čudnih stvari. Većina njih, naravno, nije toliko kritična da govori o izrazito neprimjerenom ponašanju. A drugi se objašnjavaju jednostavnošću korištenja ili učestalošću njihove primjenjivosti u određenim situacijama. Ali u isto vrijeme, postoje mnoga iznenađenja. Stoga morate znati o njima. Ako nađete još nešto čudno ili neuobičajeno u ponašanju bilo koje vrste, napišite u komentarima, rado ću dodati u dosjee dostupne o njima.

Izvor: www.habr.com