Podezřelé typy

V jejich vzhledu nic nevyvolává podezření. Spíše naopak, zdají se ti jako dobře známí a dlouholetí přátelé. Ale to pouze do té doby, než je prověříš. Tehdy ukážou svou zákeřnou podstatu, když se chovají naprosto jinak, než jsi očekával. A někdy zvládnou takové kousky, že vlasy vstávají hrůzou — například ztrácejí důvěrná data, která jim byla svěřena. Když se s nimi setkáš tváří v tvář, tvrdí, že se neznají, přitom v ústraní pilně pracují pod jedním střechou. Je nejvyšší čas je vyvést na pravou míru. Pojďme se s těmito podezřelými typy také vypořádat.

Typizace dat v PostgreSQL, i přes svou logičnost, přináší někdy velmi zvláštní překvapení. V tomto článku se pokusíme objasnit některé jejich zvláštnosti, pochopit důvody jejich podivného chování a zjistit, jak se vyhnout problémům v každodenní praxi. Abych byl upřímný, tento článek jsem napsal také jako jakýsi průvodce pro sebe, který by mi umožnil snadno se obrátit na sporné případy. Bude tedy doplňován, jakmile objevíme nová překvapení od podezřelých typů. Tak pojďme na to, neúnavní badatelé databází!

Dossier číslo jedna. real/double precision/numeric/money

Na první pohled se zdá, že číselné typy jsou nejméně problémové z hlediska překvapení v chování. Ale chyba lávky. Takže s nimi začneme. Takže…

Zapomněli jsme počítat

SELECT 0.1::real = 0.1

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

Co se děje? PostgreSQL převádí netypovanou konstantu 0.1 na typ double precision a snaží se ji porovnat s 0.1 typu real. A to jsou naprosto odlišné hodnoty! Jde o vyjádření reálných čísel v strojové paměti. Protože 0.1 nelze představit jako konečnou dvojkovou zlomek (bude to 0.0(0011) v dvojkovém vyjádření), čísla s různou přesností se liší, a proto je výsledek, že nejsou rovny. Obecně je to téma na samostatný článek, tady o tom psát nebudu.

Odkud chyba?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Chyba **********
ERROR: syntax error at or near "("
SQL-stav: 42601
Symbol: 24

Mnozí vědí, že PostgreSQL umožňuje funkční zápis přetypování. To znamená, že můžete zapsat nejen 1::int, ale i int(1), což bude ekvivalentní. Ale to neplatí pro typy, jejichž název se skládá z několika slov! Proto, pokud chcete převést číselnou hodnotu na typ double precision funkčním způsobem, použijte alias tohoto typu float8, tj. SELECT float8(1).

Co je víc než nekonečno?

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

?sloupec?
boolean
---------
t

Podívejte se na to! Ukazuje se, že existuje něco, co je větší než nekonečno, a to je NaN! Přitom dokumentace PostgreSQL se na nás upřímně dívá a tvrdí, že NaN je apriori větší než jakékoli jiné číslo, a tedy i nekonečno. Podobné platí pro -NaN. Ahoj, milovníci matematické analýzy! Ale je třeba mít na paměti, že to všechno platí v kontextu reálných čísel.

Zaokrouhlení očí

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

      zaokrouhlení      |  zaokrouhlení
double precision | numeric
-----------------+---------
2                | 3

Ještě jedno nečekané pozdravení od databáze. A opět musíme pamatovat na to, že pro typy double precision a numeric platí různá zaokrouhlení. Pro numeric — běžné, kdy se 0,5 zaokrouhluje směrem nahoru, a pro double precision — zaokroulení 0,5 se děje směrem k nejbližšímu sudému celému číslu.

Peníze jsou něco zvláštního

SELECT '10'::money::float8

ERROR:  nelze převést typ money na double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Chyba **********
ERROR: nelze převést typ money na double precision
SQL-stav: 42846
Symbol: 19

Podle názoru PostgreSQL nejsou peníze reálné číslo. Podle názoru některých jednotlivců také ne. My si musíme pamatovat, že přetypování typu money je možné pouze na typ numeric, stejně jako na typ money lze převést pouze typ numeric. A s ním se už dá hrát, jak duše chce. Ale už to nebudou ty peníze.

Smallint a generování sekvencí

SELECT *
  FROM generate_series(1::smallint, 5::smallint, 1::smallint)

ERROR:  funkce generate_series(smallint, smallint, smallint) není unikátní
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
TIP:  Nelze vybrat nejlepší kandidátskou funkci. Možná budete muset přidat explicitní typové přetypování.
********** Chyba **********
ERROR: funkce generate_series(smallint, smallint, smallint) není unikátní
SQL-stav: 42725
Tip: Nelze vybrat nejlepší kandidátskou funkci. Možná budete muset přidat explicitní typové přetypování.
Symbol: 18

PostgreSQL se nechce zabývat malými detaily. Jaké sekvence na základě smallint? int, ne méně! Proto při pokusu o provedení výše uvedeného dotazu se databáze snaží převést smallint na nějaký jiný celočíselný typ a vidí, že takových převodů může být několik. Které převod zvolit? To se rozhodnout nedokáže, a proto se objeví chyba.

Dossier číslo dvě. „char“/char/varchar/text

Některé zvláštnosti se objevují také u symbolových typů. Pojďme se s nimi také seznámit.

Co to za triky?

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

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

Jaký je to typ „char“, jaký je to klaun? Takové typy nepotřebujeme… Protože se tváří jako běžný char, i když je v uvozovkách. Liší se od běžného char, který je bez uvozovek, tím, že vypisuje pouze první byte řetězcové reprezentace, zatímco normální char vypisuje první znak. V našem případě je prvním znakem písmeno P, které v unicode reprezentaci zabírá 2 byty, o čemž svědčí konverze výsledku na typ bytea. Typ „char“ bere pouze první byte této unicode reprezentace. Tak proč je tento typ potřebný? Dokumentace PostgreSQL říká, že je to speciální typ určený pro zvláštní potřeby. Takže pravděpodobně nebude nutný. Ale podívejte se mu do očí a nenechte se zmýlit, když se s ním setkáte s jeho zvláštním chováním.

Zbytečné mezery. Z očí, z srdce ven.

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

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

Podívejte se na uvedený příklad. Speciálně jsem všechny výsledky převedl na typ bytea, aby bylo jasně vidět, co tam leží. Kde jsou koncové mezery po převodu na typ varchar(6)? Dokumentace lakonicky uvádí: „Při převodu hodnoty character na jiný symbolový typ jsou doplňkové mezery odstraňovány“. Toto nepřátelství je třeba si zapamatovat. A všimněte si, že pokud je řetězcová konstanta v uvozovkách okamžitě převedena na typ varchar(6), koncové mezery se zachovávají. Takové jsou zázraky.

Dossier číslo tři. json/jsonb

JSON je samostatná struktura, která žije svým vlastním životem. Proto se její entity a entity PostgreSQL poněkud liší. Zde jsou příklady.

Johnson & Johnson. Pocítíte rozdíl

SELECT 'null'::jsonb IS NULL

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

Vše spočívá v tom, že JSON má svou entitu null, která není ekvivalentem NULL v PostgreSQL. Zároveň však samotný JSON objekt může mít hodnotu NULL, proto výraz SELECT null::jsonb IS NULL (všimněte si absence jednoduchých uvozovek) tentokrát vrátí true.

Jedno písmeno mění všechno

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še spočívá v tom, že json a jsonb jsou zcela odlišné struktury. V json je objekt uložen tak, jak je, zatímco v jsonb je uložen v již rozebrané a indexované struktuře. Právě proto v druhém případě byla hodnota objektu podle klíče 1 nahrazena z [1, 2, 3] na [7, 8, 9], které přišlo do struktury na konci se stejným klíčem.

Z vody se nepije

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

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

PostgreSQL při implementaci JSONB mění formátování reálných čísel a uvádí je do klasického formátu. Pro typ JSON to neplatí. Je to trochu zvláštní, ale je to jeho právo.

Spis číslo čtyři. date/time/timestamp

S typy data/ času jsou také určité zvláštnosti. Podíváme se na ně. Hned na začátek si poznamenám, že některé z těchto zvláštností se stávají jasnými, pokud dobře chápete podstatu práce s časovými pásmy. Ale to je také téma pro další článek.

Moje tvoje nepochopit

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.
********** Chyba **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-stav: 22008
Nápověda: Perhaps you need a different "datestyle" setting.
Symbol: 8

Zdálo by se, co na tom může být nejasného? Ale přesto databáze nechápe, zda jsme tady dali na první místo - rok nebo den? A rozhoduje, že to je 99. leden 2008, což ji dostává do potíží. Obecně platí, že v případě převodu dat v textovém formátu je třeba velmi pečlivě zkontrolovat, jak správně je databáze rozpoznala (zejména analyzovat parametr datestyle příkazem SHOW datestyle), protože nejednoznačnosti v této otázce mohou být velmi nákladné.

Odkud jsi se vzal?

VYBERTE '04:05 Evropa/Moskva'::time

CHYBA:  neplatná vstupní syntaxe pro typ time: "04:05 Evropa/Moskva"
ŘÁDEK 1: VYBERTE '04:05 Evropa/Moskva'::time
               ^
********** Chyba **********
CHYBA: neplatná vstupní syntaxe pro typ time: "04:05 Evropa/Moskva"
SQL-stav: 22007
Symbol: 8

Proč databáze nedokáže pochopit výslovně zadaný čas? Protože pro časové pásmo je uvedena nikoli zkratka, ale úplný název, který dává smysl pouze v kontextu data, jelikož zohledňuje historii změn časových pásem, a ta bez data nefunguje. A i samotná formulace řetězce času vyvolává otázky — co vlastně měl programátor na mysli? Takže to dává smysl, pokud se zamyslíte.

Co mu vlastně vadí?

Představte si situaci. Ve vaší tabulce je sloupec typu timestamptz. Chcete ho indexovat. Ale chápete, že není vždy rozumné budovat index podle tohoto sloupce vzhledem k jeho vysoké selektivitě (prakticky všechna hodnoty tohoto typu budou unikátní). Proto se rozhodnete snížit selektivitu indexu převedením tohoto typu na datum. A dostanete překvapení:

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

CHYBA:  funkce v indexovém výrazu musí být označeny jako IMMUTABLE
********** Chyba **********
CHYBA: funkce v indexovém výrazu musí být označeny jako IMMUTABLE
SQL-stav: 42P17

V čem je problém? V tom, že pro převod typu timestamptz na typ date se používá hodnota systémového parametru TimeZone, což činí funkci převodu typu závislou na konfigurovatelném parametru, tedy proměnlivou (volatile). Takové funkce nejsou v indexu přípustné. V tomto případě je třeba výslovně uvést, v jakém časovém pásmu se provádí převod typu.

Když now rozhodně není now

Jsme zvyklí, že now() vrací aktuální datum/čas s přihlédnutím k časovému pásmu. Ale podívejte se na následující dotazy:

START TRANSAKCE;
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

KONEC;

Datum/čas se vrací stejné bez ohledu na to, kolik času uplynulo od posledního dotazu! Proč? Protože now() není aktuální čas, ale čas začátku aktuální transakce. Proto se v rámci transakce nemění. Každý dotaz spuštěný mimo rámec transakce se implicitně obalí do transakce, takže si ani nevšimneme, že čas, který vrací jednoduchý dotaz SELECT now(); vlastně není aktuální… Pokud chcete získat skutečný aktuální čas, musíte použít funkci clock_timestamp().

Dossier číslo pět. bit

Trochu divné

SELECT '111'::bit(4)

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

Z které strany by se měly přidávat bity v případě rozšíření typu? Zdá se, že zleva. Ale databáze má na to jiný názor. Buďte opatrní: pokud se počet bitů při převodu typu neshoduje, dostanete něco úplně jiného, než jste chtěli. To platí jak pro přidání bitů zprava, tak pro zkracování bitů. Také zprava…

Dossier číslo šest. Pole

Ani NULL neprošel

SELECT ARRAY[1, 2] || NULL

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

Jako normální lidé, vychovaní na SQL, očekáváme, že výsledkem tohoto výrazu bude NULL. Ale tak to není. Vrací se pole. Proč? Protože v tomto případě databáze převádí NULL na celočíselné pole a implicitně vyvolává funkci array_cat. Ale pořád zůstává nejasné, proč tento "pole kočička" nevyprázdní pole. Tuto funkci je také dobré si jednoduše zapamatovat.

Shrneme to. Zvláštností je dostatek. Většina z nich samozřejmě není tak kritická, aby o nich bylo možné mluvit jako o ostentativně neadekvátním chování. A další jsou vysvětlitelné pohodlným použitím nebo četností jejich aplikovatelnosti v různých situacích. Ale přesto je spousta překvapení. Proto je dobré o nich vědět. Pokud najdete něco zvláštního nebo neobvyklého v chování nějakých typů, napište do komentářů, rád doplním existující dossier.

Zdroj: habr.com