Njihov videz ni nič sumljivega. Še več, zdijo se vam celo dobro in dolgo znani. Ampak to je samo dokler jih ne preverite. Tu pokažejo svojo zahrbtno naravo, saj delujejo povsem drugače, kot ste pričakovali. In včasih naredijo kaj takega, da ti gredo lasje pokonci – na primer izgubijo tajne podatke, ki so jim bili zaupani. Ko ju soočite, trdita, da se ne poznata, čeprav v senci trdo delata pod isto kapo. Čas je, da jih končno spravimo na čisto vodo. Ukvarjajmo se tudi s temi sumljivimi tipi.
Tipkanje podatkov v PostgreSQL kljub vsej svoji logiki včasih predstavlja zelo nenavadna presenečenja. V tem članku bomo poskušali razjasniti nekatere njihove muhe, razumeti razlog za njihovo čudno vedenje in razumeti, kako se izogniti težavam v vsakodnevni praksi. Resnici na ljubo sem ta članek sestavil tudi kot nekakšno referenčno knjigo zase, referenčno knjigo, na katero bi se zlahka sklicevali v spornih primerih. Zato se bo dopolnjeval, ko bodo odkrita nova presenečenja sumljivih tipov. Torej, gremo, o neumorni sledilci baz podatkov!
Dosje številka ena. realna/dvojna natančnost/številska/denar
Zdi se, da so numerični tipi najmanj problematični v smislu presenečenj v vedenju. Ampak ne glede na to, kako je. Pa začnimo z njimi. Torej …
Pozabil sem šteti
SELECT 0.1::real = 0.1
?column?
boolean
---------
fKaj je narobe? Težava je v tem, da PostgreSQL netipizirano konstanto 0.1 pretvori v dvojno natančnost in jo poskuša primerjati z 0.1 realnega tipa. In to so popolnoma različni pomeni! Ideja je predstaviti realna števila v strojnem pomnilniku. Ker 0.1 ni mogoče predstaviti kot končni binarni ulomek (binarno bi bil 0.0(0011)), bodo števila z različnimi bitnimi globinami različna, zato niso enaka. Na splošno je to tema za ločen članek, tukaj ne bom pisal podrobneje.
Od kod izvira napaka?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Mnogi ljudje vedo, da PostgreSQL omogoča funkcionalno notacijo za pretvorbo tipa. To pomeni, da lahko napišete ne samo 1::int, ampak tudi int(1), kar bo enakovredno. Vendar ne za vrste, katerih imena so sestavljena iz več besed! Če torej želite prenesti številsko vrednost v tip dvojne natančnosti v funkcionalni obliki, uporabite vzdevek tega tipa float8, to je SELECT float8(1).
Kaj je večje od neskončnosti?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tPoglej kako je! Izkazalo se je, da obstaja nekaj, kar je večje od neskončnosti, in to je NaN! Hkrati nas dokumentacija PostgreSQL gleda s poštenimi očmi in trdi, da je NaN očitno večje od katerega koli drugega števila in s tem neskončnost. Nasprotno velja tudi za -NaN. Pozdravljeni, ljubitelji matematike! Vendar se moramo zavedati, da vse to deluje v kontekstu realnih števil.
Zaokroževanje oči
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Še en nepričakovan pozdrav iz baze. Ne pozabite, da imajo dvojna natančnost in številski tipi različne učinke zaokroževanja. Za numerično - običajno, ko se 0,5 zaokroži navzgor, za dvojno natančnost pa se 0,5 zaokroži proti najbližjemu sodemu celemu številu.
Denar je nekaj posebnega
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
Символ: 19Glede na PostgreSQL denar ni realna številka. Po mnenju nekaterih posameznikov tudi. Ne smemo pozabiti, da je pretvorba denarnega tipa možna le v številski tip, prav tako kot je mogoče samo številski tip preoblikovati v denarni tip. Zdaj pa se lahko igrate z njim, kot vam srce poželi. Ampak to ne bo isti denar.
Smallint in generiranje zaporedja
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.
Символ: 18PostgreSQL ne mara zapravljati časa za malenkosti. Kakšna so ta zaporedja, ki temeljijo na smallint? int, nič manj! Zato baza podatkov, ko poskuša izvesti zgornjo poizvedbo, poskuša preoblikovati smallint v nek drug celoštevilski tip in vidi, da je morda več takšnih pretvorb. Katero zasedbo izbrati? O tem se ne more odločiti in se zato zruši z napako.
Datoteka številka dve. "char"/char/varchar/besedilo
Številne nenavadnosti so prisotne tudi v vrstah znakov. Spoznajmo jih tudi mi.
Kakšni triki so to?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fKakšen "čar" je to, kakšen klovn je to? Teh ne rabimo ... Ker se pretvarja, da je navaden char, čeprav je v narekovajih. Od običajnega char, ki je brez narekovajev, se razlikuje po tem, da izpiše samo prvi bajt predstavitve niza, medtem ko običajni char izpiše prvi znak. V našem primeru je prvi znak črka P, ki v predstavitvi unicode zavzame 2 bajta, kar dokazuje pretvorba rezultata v tip bytea. In tip "char" zavzame samo prvi bajt te predstavitve unicode. Zakaj je potem ta vrsta potrebna? Dokumentacija PostgreSQL pravi, da je to posebna vrsta, ki se uporablja za posebne potrebe. Zato ga verjetno ne bomo potrebovali. Toda poglejte mu v oči in ne boste se zmotili, ko ga boste srečali s posebnim obnašanjem.
Dodatni prostori. Daleč od oči, daleč 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 | x616263202020Oglejte si navedeni primer. Vse rezultate sem posebej pretvoril v vrsto bytea, tako da je bilo jasno vidno, kaj je tam. Kje so končni presledki po ulitju v varchar(6)? Dokumentacija jedrnato navaja: "Pri pretvorbi vrednosti znaka v drugo vrsto znakov se presledek na koncu zavrže." To nenaklonjenost si je treba zapomniti. In upoštevajte, da če je konstanta niza v citiranih nizih pretvorjena neposredno v tip varchar(6), se končni presledki ohranijo. Takšni so čudeži.
Datoteka številka tri. json/jsonb
JSON je ločena struktura, ki živi svoje življenje. Zato se njegove entitete in entitete PostgreSQL nekoliko razlikujejo. Tukaj so primeri.
Johnson in Johnson. občutite razliko
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fStvar je v tem, da ima JSON lastno ničelno entiteto, ki ni analogna NULL v PostgreSQL. Obenem ima lahko sam objekt JSON vrednost NULL, zato bo izraz SELECT null::jsonb IS NULL (upoštevajte odsotnost enojnih narekovajev) tokrat vrnil true.
Ena črka spremeni vse
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 v tem, da sta json in jsonb popolnoma različni strukturi. V json je predmet shranjen tak, kot je, v jsonb pa je že shranjen v obliki razčlenjene, indeksirane strukture. Zato je bila v drugem primeru vrednost objekta po ključu 1 zamenjana iz [1, 2, 3] v [7, 8, 9], ki je v strukturo prišel čisto na koncu z istim ključem.
Ne pijte vode z obraza
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL v svoji izvedbi JSONB spremeni oblikovanje realnih števil in jih pripelje v klasično obliko. To se ne zgodi pri vrsti JSON. Malo čudno, ampak ima prav.
Datoteka številka štiri. datum/čas/časovni žig
Nekaj nenavadnosti je tudi pri vrstah datuma/časa. Poglejmo jih. Naj takoj rezerviram, da nekatere vedenjske značilnosti postanejo jasne, če dobro razumete bistvo dela s časovnimi pasovi. Toda to je tudi tema za ločen članek.
Moji tvoji ne razumejo
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.
Символ: 8Zdi se, kaj je tukaj nerazumljivo? Toda zbirka podatkov še vedno ne razume, kaj smo tukaj postavili na prvo mesto - leto ali dan? In se odloči, da je januar 99, 2008, kar jo preseneti. Na splošno morate pri prenosu datumov v besedilni obliki zelo natančno preveriti, kako pravilno jih je baza podatkov prepoznala (zlasti analizirajte parameter datestyle z ukazom SHOW datestyle), saj so dvoumnosti v tej zadevi lahko zelo drage.
Od kje ti to?
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
Символ: 8Zakaj baza podatkov ne more razumeti izrecno določenega časa? Ker časovni pas nima okrajšave, ampak polno ime, ki je smiselno samo v kontekstu datuma, saj upošteva zgodovino menjave časovnih pasov, brez datuma pa ne gre. In že samo besedilo časovnice postavlja vprašanja – kaj je programer v resnici mislil? Zato je tukaj vse logično, če pogledate.
Kaj je narobe z njim?
Predstavljajte si situacijo. V tabeli imate polje z vrsto timestamptz. Želite ga indeksirati. Vendar razumete, da gradnja indeksa na tem polju ni vedno upravičena zaradi njegove visoke selektivnosti (skoraj vse vrednosti te vrste bodo edinstvene). Zato se odločite zmanjšati selektivnost indeksa tako, da tip spremenite v datum. In dobiš preseneč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-состояние: 42P17Kaj je narobe? Dejstvo je, da se za pretvorbo tipa timestamptz v tip datuma uporabi vrednost sistemskega parametra TimeZone, zaradi česar je funkcija pretvorbe tipa odvisna od parametra po meri, tj. hlapljivo. Takšne funkcije v indeksu niso dovoljene. V tem primeru morate izrecno navesti, v katerem časovnem pasu se izvaja pretvorba tipa.
Ko zdaj sploh ni niti zdaj
Navajeni smo, da now() vrne trenutni datum/uro ob upoštevanju časovnega pasu. Toda poglejte naslednje poizvedbe:
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/čas se vrne enak ne glede na to, koliko časa je minilo od prejšnje zahteve! Kaj je narobe? Dejstvo je, da now() ni trenutni čas, ampak začetni čas trenutne transakcije. Zato se znotraj transakcije ne spreminja. Vsaka poizvedba, sprožena zunaj obsega transakcije, je implicitno zavita v transakcijo, zato ne opazimo, da je čas, vrnjen s preprostim SELECT now(); pravzaprav ne trenutnega ... Če želite dobiti pošten trenutni čas, morate uporabiti funkcijo clock_timestamp().
Datoteka številka pet. bit
Malo čudno
SELECT '111'::bit(4)
bit
bit(4)
------
1110Na katero stran je treba dodati bite v primeru razširitve tipa? Zdi se, da je na levi. A le baza ima o tem drugačno mnenje. Bodite previdni: če se število števk ne ujema pri ulivanju vrste, ne boste dobili želenega. To velja tako za dodajanje nastavkov na desno kot za obrezovanje nastavkov. Tudi na desni...
Datoteka številka šest. Nizi
Tudi NULL se ni sprožil
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Kot običajni ljudje, vzgojeni na SQL, pričakujemo, da bo rezultat tega izraza NULL. Ampak tam ga ni bilo. Vrnjena je matrika. Zakaj? Ker v tem primeru osnova pretvori NULL v niz celih števil in implicitno pokliče funkcijo array_cat. Še vedno pa ostaja nejasno, zakaj ta "matrična mačka" ne ponastavi matrike. To vedenje si je treba tudi zapomniti.
Povzemite. Veliko je čudnih stvari. Večina jih seveda ni tako kritičnih, da bi govorili o izrazito neprimernem obnašanju. In drugi so razloženi z enostavnostjo uporabe ali pogostostjo njihove uporabe v določenih situacijah. Toda hkrati je veliko presenečenj. Zato morate vedeti o njih. Če se vam v vedenju katere koli vrste zdi še kaj čudnega ali nenavadnega, napišite v komentarje, z veseljem bom dodal dosjeje, ki so na voljo zanje.
Vir: www.habr.com