ProHoster > Blog > Administrácia > Podozrivé typy

Podozrivé typy

Ich nichts im äußeren Erscheinungsbild könnte Verdacht erregen. Im Gegenteil, sie scheinen dir gut und lange bekannt zu sein. Aber das gilt nur, bis du sie überprüfst. An dieser Stelle zeigen sie ihr hinterhältiges Wesen und verhalten sich ganz anders, als du es erwartet hast. Manchmal tun sie Dinge, die einem die Haare zu Berge stehen lassen — zum Beispiel verlieren sie vertrauliche Daten, die ihnen anvertraut wurden. Wenn du sie zur Verantwortung ziehst, behaupten sie, sich nicht zu kennen, während sie im Schatten eifrig unter einem Dach arbeiten. Es wird höchste Zeit, sie ans Licht zu bringen. Lass uns auch mit diesen verdächtigen Typen aufräumen.

Die Datentypisierung in PostgreSQL, trotz ihrer Logik, bringt manchmal sehr seltsame Überraschungen mit sich. In diesem Artikel werden wir versuchen, einige ihrer Eigenheiten zu klären, die Gründe für ihr merkwürdiges Verhalten zu verstehen und herauszufinden, wie man in der Praxis auf Probleme stößt. Um die Wahrheit zu sagen, habe ich diesen Artikel teilweise auch als eine Art Nachschlagewerk für mich selbst erstellt, ein Nachschlagewerk, auf das man in strittigen Fällen leicht zurückgreifen kann. Daher wird es mit der Entdeckung neuer Überraschungen von verdächtigen Typen ergänzt. Also, auf geht's, unermüdliche Datenbankentdecker!

Aktenzeichen eins. real/double precision/numeric/money

Es scheint, als wären numerische Typen die unkompliziertesten in Bezug auf Verhaltensüberraschungen. Doch weit gefehlt. Deshalb beginnen wir mit ihnen. Also...

Das Zählen verlernt

SELECT 0.1::real = 0.1

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

Was ist los? PostgreSQL konvertiert die untypisierte Konstante 0.1 in den Typ double precision und versucht, sie mit 0.1 vom Typ real zu vergleichen. Das sind absolut unterschiedliche Werte! Der Grund liegt in der Darstellung von Gleitkommazahlen im Speicher. Da 0.1 nicht als endliche binäre Fraktion dargestellt werden kann (das wäre 0.0(0011) im binären Format), werden Zahlen mit unterschiedlicher Präzision unterschiedlich sein, was zu dem Ergebnis führt, dass sie ungleich sind. Grundsätzlich ist das ein Thema für einen eigenen Artikel, ich werde hier nicht weiter darauf eingehen.

Woher kommt der Fehler?

SELECT double precision(1)

ERROR: Syntaxfehler in der Nähe von "("
LINE 1: SELECT double precision(1)
                               ^
********** Fehler **********
ERROR: Syntaxfehler in der Nähe von "("
SQL-Zustand: 42601
Symbol: 24

Mnohí vedia, že PostgreSQL umožňuje funkčné záznamy konverzie typov. Teda môžete napísať nielen 1::int, ale aj int(1), čo bude mať rovnaký význam. Ale iba nie pre typy, ktorých názvy sa skladajú z viacerých slov! Preto, ak chcete previesť číselnú hodnotu na typ double precision vo funkčnej forme, použite alias tohto typu float8, teda SELECT float8(1).

Čo je viac ako nekonečno?

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

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

Tak to je! Zjavuje sa niečo, čo je väčšie ako nekonečno, a to je NaN! Pri tom dokumentácia PostgreSQL úprimne hovorí, že NaN je zjavne väčšie ako akékoľvek iné číslo, a teda aj než nekonečno. A naopak platí to pre -NaN. Ahoj, milovníci matematickej analýzy! Ale treba pamätať, že toto všetko platí v kontexte reálnych čísel.

Zaokrúhľovanie očí

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

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

Ešte jeden neočakávaný pozdrav z databázy. A opäť treba pamätať, že pre typy double precision a numeric platia rôzne zaokrúhlenia. Pre numeric — obvyklé, keď 0,5 sa zaokrúhľuje smerom nahor, a pre double precision — zaokrúhlenie 0,5 sa deje smerom k najbližšiemu párnemu celému číslu.

Peniaze sú niečo zvláštne

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Chyba **********
ERROR: cannot cast type money to double precision
SQL-stav: 42846
Symbol: 19

Podľa PostgreSQL peniaze nie sú reálnym číslom. Podľa niektorých jednotlivcov, tiež nie. My si však musíme pamätať, že konverzia typu money je možná iba na typ numeric, rovnako ako typ money môže byť prevedený iba na typ numeric. A s tým sa už dá hrať, ako sa duša bude cítiť. Ale to už nebudú tie isté peniaze.

Smallint a generácia 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.
********** Chyba **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-stav: 42725
Tip: Nemohli sme zvoliť najlepší kandidát funkcie. Možno budete musieť pridať explicitné typové konverzie.
Symbol: 18

PostgreSQL nemá rád malé detaily. Aké sekvencie na základe smallint? int, a nie menej! Preto pri pokuse o vykonanie vyššie uvedeného dotazu sa databáza pokúša previesť smallint na iný celočíselný typ a vidí, že takéto prevody môžu byť viacere. Ktoré prevod si zvoliť? To ona nemôže rozhodnúť, a preto sa to skončí chybou.

Dossier číslo dva. „char“/char/varchar/text

Rôzne zvláštnosti sa vyskytujú aj u typov znakov. Poďme sa s nimi tiež oboznámiť.

Aké triky to sú?

SELECT 'PEŤA'::"char"
     , 'PEŤA'::"char"::bytea
     , 'PEŤA'::char
     , 'PEŤA'::char::bytea

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

Čo je to za typ „char“, čo je to za klaun? Takých nepotrebujeme... Pretože sa tvári ako obyčajný char, aj keď je v úvodzovkách. A líši sa od obyčajného char bez úvodzoviek tým, že zobrazuje iba prvý byte reťazcového zobrazenia, zatiaľ čo normálny char zobrazuje prvý znak. V našom prípade je prvý znak – písmeno P, ktoré v unicode zobrazení zaberá 2 byty, čo potvrdzuje konverzia výsledku na typ bytea. Typ „char“ vezme iba prvý byte tohto unicode zobrazenia. Tak na čo je tento typ potrebný? Dokumentácia PostgreSQL hovorí, že je to špeciálny typ určený na zvláštne potreby. Takže ho asi nebudeme potrebovať. Ale pozrite sa mu do očí a nespleťte sa, keď ho stretnete s jeho špeciálnym správaním.

Zbytočné medzery. Preč, z očí, preč zo srdca.

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

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

Pozrite sa na uvedený príklad. Úmyselne som všetky výsledky previedol na typ bytea, aby bolo jasne vidno, čo tam leží. Kde sú koncové medzery po prevode na typ varchar(6)? Dokumentácia stručne tvrdí: „Pri prevode hodnoty znaku na iný znakový typ sú nadbytočné medzery odstránené.“ Túto neobľúbenosť si treba zapamätať. A všimnite si, že ak je reťazcová konštanta v úvodzovkách hneď prevedená na typ varchar(6), koncové medzery sa uchovávajú. Takéto zázraky.

Dossier číslo tri. json/jsonb

JSON je samostatná štruktúra, ktorá žije svojím vlastným životom. Preto sa jej entity a entity PostgreSQL trochu líšia. Tu sú príklady.

Johnson & Johnson. Cíťte rozdiel

SELECT 'null'::jsonb IS NULL

?stĺpec?
bool
---------
f

Všetko spočíva v tom, že JSON má svoju vlastnú podstatu null, ktorá nie je ekvivalentom NULL v PostgreSQL. Rovnako môže mať samotný JSON objekt hodnotu NULL, preto výraz SELECT null::jsonb IS NULL (všimnite si absenciu jednoduchých úvodzoviek) tentokrát vráti true.

Jedno písmeno zmení všetko

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

Všetko spočíva v tom, že json a jsonb sú úplne odlišné štruktúry. V json je objekt uložený tak, ako je, zatiaľ čo v jsonb je uložený už ako rozložená indexovaná štruktúra. Práve preto v druhom prípade bola hodnota objektu na kľúči 1 nahradená z [1, 2, 3] na [7, 8, 9], ktorá prišla do štruktúry na úplnom konci so štatistickým kľúčom.

Z tváre vody sa neopíjaj

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

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

PostgreSQL na implementácii JSONB mení formátovanie reálnych čísel, čím ich uvádza do klasického tvaru. Pri type JSON sa to však nedeje. Trochu zvláštne, ale to je jeho právo.

Dossier číslo štyri. dátum/ čas/ timestamp

S dátumovými/ časovými typmi sú tiež isté zvláštnosti. Pozrime sa na ne. Hneď musím povedať, že niektoré z vlastností správania sa stávajú zrozumiteľnými, ak dobre chápete podstatu práce s časovými pásmami. Ale to je tiež téma na samostatný článok.

Ja a ty si nerozumieme

SELECT '08-Jan-99'::date

ERROR:  dátum/
časové pole je mimo rozsah: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Možno by ste potrebovali iné nastavenie "datestyle".
********** Chyba **********
ERROR: dátum/
časové pole je mimo rozsah: "08-Jan-99"
SQL-stav: 22008
Podnázov: Možno by ste potrebovali iné nastavenie "datestyle".
Symbol: 8

Zdalo by sa, že čo je tu nejasné? Ale aj tak databáza nerozumie, čo sme tu dali na prvé miesto — rok alebo deň? A rozhoduje sa, že to je 99. januára 2008, čo jej vybuchuje mozog. V každom prípade je pri prenose dátumov vo formáte textu potrebné veľmi pozorne skontrolovať, ako presne ich databáza rozpoznala (najmä analyzovať parameter datestyle príkazom SHOW datestyle), pretože nejednoznačnosti v tejto otázke môžu stáť veľmi draho.

Odkiaľ si sa vzal?

SELECT '04:05 Europe/Moscow'::time

ERROR:  neplatná syntaxe vstupu pro typ time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** Chyba **********
ERROR: neplatná syntaxe vstupu pre typ time: "04:05 Europe/Moscow"
SQL-stav: 22007
Symbol: 8

Prečo databáza nedokáže pochopiť jednoznačne uvedený čas? Pretože pre časové pásmo je uvedené nie skratka, ale úplný názov, ktorý má význam len v kontexte dátumu, pretože zohľadňuje históriu zmien časových pásiem, a to bez dátumu nefunguje. A ani formulácia časového reťazca nevzbudzuje otázky — čo teda programátor naozaj myslel? Preto keď sa nad tým zamyslíte, všetko je logické.

Čo mu nie je v poriadku?

Predstavte si situáciu. Vo vašej tabuľke je pole typu timestamptz. Chcete ho indexovať. Ale uvedomujete si, že vytvárať index na tomto poli nie vždy dáva zmysel vzhľadom na jeho vysokú selektivitu (takmer všetky hodnoty tohto typu budú jedinečné). Preto sa rozhodnete znížiť selektivitu indexu prevedením tohto typu na dátum. A dostanete prekvapenie:

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

ERROR:  funkcie v indexovom výraze musia byť označené ako IMMUTABLE
********** Chyba **********
ERROR: funkcie v indexovom výraze musia byť označené ako IMMUTABLE
SQL-stav: 42P17

O čo ide? O to, že na prevedenie typu timestamptz na typ date sa používa hodnota systémového parametra TimeZone, čo robí funkciu premeny typu závislou na konfigurovateľnom parametre, teda podliehajúcou zmenám (volatile). Takéto funkcie nie sú v indexe prijateľné. V tomto prípade je potrebné jasne uviesť, v akom časovom pásme sa koná konverzia typu.

Keď now nie je teraz

Sme zvyknutí, že now() vracia aktuálny dátum/čas so zohľadnením časového pásma. Ale pozrite sa na nasledujúce dotazy:

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;

Dátum/čas sa vracajú rovnaké bez ohľadu na to, koľko času uplynulo od posledného dopytu! O čo ide? O to, že now() nie je aktuálny čas, ale čas začiatku aktuálnej transakcie. Preto sa v rámci transakcie nemení. Akýkoľvek dopyt spustený mimo rámca transakcie sa implicitne obalí do transakcie, a preto si ani nevšimneme, že čas, ktorý sa vracia jednoduchým dopytom SELECT now(); naozaj nie je aktuálny... Ak chcete získať presný aktuálny čas, musíte použiť funkciu clock_timestamp().

Dossier číslo päť. bit

Trochu divné

SELECT '111'::bit(4)

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

Z ktorej strany by sa mali bity pridávať pri rozširovaní typu? Zdá sa, že zľava. Ale databáza má na to iný názor. Buďte opatrní: pri nesúlade počtu bitov pri prevode typu dostanete úplne niečo iné, než ste chceli. To sa týka jak pridávania bitov sprava, tak aj skracovania bitov. Taktiež sprava...

Dossier číslo šesť. Pole

Ani NULL nevydal náboje

SELECT ARRAY[1, 2] || NULL

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

Ako normálni ľudia, vychovaní na SQL, očakávame, že výsledkom tohto výrazu bude NULL. Ale nie tu. Vracia sa pole. Prečo? Pretože v tomto prípade databáza prevádza NULL na celočíselné pole a implicitne vyvoláva funkciu array_cat. Ale stále ostáva nejasné, prečo tento "pole cat" neoprávňuje pole. Takéto správanie treba jednoducho zapamätať.

Zhrňme to. Podivností je dosť. Väčšina z nich samozrejme nie je natoľko kritická, aby sme hovorili o krikľavom neadekvátnom správaní. A iné sa vysvetľujú pohodlím použitia alebo frekvenciou ich uplatnenia v rôznych situáciách. Ale zároveň je veľa prekvapení. Preto o nich treba vedieť. Ak nájdete ešte niečo divné alebo neobvyklé v správaní akýchkoľvek typov, napíšte do komentárov, s radosťou doplním existujúce na ne dossié.

Zdroj: habr.com

Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery 🔥 Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery | ProHoster