🥇Podezřelé typy

Na jejich vzhledu není nic podezřelého. Navíc se vám dokonce zdají dobře a dlouho známé. Ale to jen do té doby, než je zkontrolujete. Tady ukazují svou zákeřnou povahu, fungují úplně jinak, než jste čekali. A občas udělají něco, z čeho se vám ježí vlasy na hlavě – například přijdou o tajná data, která jim byla svěřena. Když je konfrontujete, tvrdí, že se neznají, ačkoliv ve stínu tvrdě pracují pod stejnou kapotou. Je čas je konečně přivést k čisté vodě. Pojďme se zabývat i těmito podezřelými typy.

Psaní dat v PostgreSQL při vší své logice někdy přináší velmi zvláštní překvapení. V tomto článku se pokusíme objasnit některé jejich zvláštnosti, pochopit důvod jejich podivného chování a pochopit, jak nenarazit na problémy v každodenní praxi. Abych řekl pravdu, sestavil jsem tento článek také jako určitou referenční knihu pro sebe, referenční knihu, na kterou lze snadno odkazovat v kontroverzních případech. Proto bude doplňován, jakmile budou objevena nová překvapení od podezřelých typů. Takže, jdeme na to, neúnavní databázoví sledovači!

Dokument číslo jedna. reálná/dvojitá přesnost/numerická/peníze

Zdálo by se, že numerické typy jsou z hlediska překvapení v chování nejméně problematické. Ale bez ohledu na to, jak to je. Začněme tedy jimi. Tak…

Zapomněli jste počítat

SELECT 0.1::real = 0.1

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

Co se děje? Problém je v tom, že PostgreSQL převádí netypizovanou konstantu 0.1 na dvojnásobnou přesnost a snaží se ji porovnat s 0.1 skutečného typu. A to jsou úplně jiné významy! Cílem je reprezentovat reálná čísla v paměti stroje. Protože 0.1 nemůže být reprezentováno jako konečný binární zlomek (bylo by to 0.0(0011) v binární podobě), čísla s různou bitovou hloubkou se budou lišit, a proto se nebudou rovnat. Obecně lze říci, že je to téma na samostatný článek, podrobněji se zde rozepisovat nebudu.

Kde se chyba bere?

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

Mnoho lidí ví, že PostgreSQL umožňuje funkční zápis pro přetypování. To znamená, že můžete psát nejen 1::int, ale také int(1), což bude ekvivalentní. Ale ne pro typy, jejichž jména se skládají z několika slov! Pokud tedy chcete přetypovat číselnou hodnotu na typ s dvojnásobnou přesností ve funkční formě, použijte alias tohoto typu float8, tedy SELECT float8(1).

Co je větší než nekonečno?

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

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

Podívejte se, jaké to je! Ukázalo se, že existuje něco většího než nekonečno a je to NaN! Dokumentace PostgreSQL se na nás zároveň dívá upřímnýma očima a tvrdí, že NaN je zjevně větší než jakékoli jiné číslo, a tedy nekonečno. Opak platí také pro -NaN. Ahoj milovníci matematiky! Ale musíme si uvědomit, že to vše funguje v kontextu reálných čísel.

Zakulacení očí

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

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

Další nečekaný pozdrav ze základny. Opět si pamatujte, že dvojitá přesnost a číselné typy mají různé efekty zaokrouhlování. Pro numerické - obvyklé, kdy se 0,5 zaokrouhluje nahoru, a pro dvojnásobnou přesnost - 0,5 se zaokrouhlí na nejbližší sudé celé číslo.

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

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

Podle PostgreSQL nejsou peníze skutečné číslo. I podle některých jedinců. Musíme si pamatovat, že přetypování peněžního typu je možné pouze na číselný typ, stejně jako pouze číselný typ lze přetypovat na peněžní typ. Nyní si s ním ale můžete hrát, jak si vaše srdce přeje. Ale nebudou to stejné peníze.

Smallint a sekvenční generování

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 nerad ztrácí čas maličkostmi. Jaké jsou tyto sekvence založené na smallintu? int, nic méně! Proto při pokusu o provedení výše uvedeného dotazu se databáze pokusí přetypovat smallint na jiný celočíselný typ a zjistí, že takových přetypování může být několik. Jaké obsazení vybrat? Nemůže to rozhodnout, a proto havaruje s chybou.

Soubor číslo dvě. "char"/char/varchar/text

Řada zvláštností je také přítomna v typech postav. Pojďme se s nimi také seznámit.

Co to je za triky?

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

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

Co je to za typ "char", co je to za klauna? Nepotřebujeme ty... Protože to předstírá, že je to obyčejný znak, i když je to v uvozovkách. A liší se od běžného znaku, který je bez uvozovek, v tom, že na výstupu je pouze první bajt řetězcové reprezentace, zatímco na výstupu normální znak je 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čí převod výsledku na typ bytea. A typ „char“ zabírá pouze první bajt této reprezentace Unicode. Proč je tedy tento typ potřeba? Dokumentace PostgreSQL říká, že se jedná o speciální typ používaný pro speciální potřeby. Takže to pravděpodobně nebudeme potřebovat. Ale podívejte se mu do očí a nespletete se, když ho potkáte s jeho zvláštním chováním.

Prostory navíc. Sejde z očí, sejde z mysli

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. Všechny výsledky jsem speciálně převedl na typ bytea, aby bylo dobře vidět, co tam je. Kde jsou koncové mezery po přetypování na varchar(6)? Dokumentace stručně uvádí: "Při přetypování hodnoty znaku na jiný typ znaku jsou koncové mezery zahozeny." Tuto nechuť je třeba mít na paměti. A všimněte si, že pokud je řetězcová konstanta v uvozovkách přetypována přímo na typ varchar(6), koncové mezery se zachovají. Takové jsou zázraky.

Soubor číslo tři. json/jsonb

JSON je samostatná struktura, která si žije vlastním životem. Proto se jeho entity a entity PostgreSQL mírně liší. Zde jsou příklady.

Johnson a Johnson. cítit rozdíl

SELECT 'null'::jsonb IS NULL

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

Jde o to, že JSON má svou vlastní nulovou entitu, která není analogem NULL v PostgreSQL. Současně může mít samotný objekt JSON hodnotu NULL, takže výraz SELECT null::jsonb IS NULL (všimněte si absence jednoduchých uvozovek) tentokrát vrátí hodnotu true.

Jedno písmeno změní vše

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

Jde o to, že json a jsonb jsou zcela odlišné struktury. V json je objekt uložen tak, jak je, a v jsonb je již uložen ve formě analyzované, indexované struktury. Proto ve druhém případě byla hodnota objektu klíčem 1 nahrazena z [1, 2, 3] na [7, 8, 9], který se do struktury dostal až na samém konci se stejným klíčem.

Nepijte vodu z obličeje

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

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

PostgreSQL ve své implementaci JSONB mění formátování reálných čísel a přivádí je do klasické podoby. To se u typu JSON nestane. Trochu zvláštní, ale má pravdu.

Soubor číslo čtyři. datum/čas/časové razítko

Existují také některé zvláštnosti s typy data/času. Pojďme se na ně podívat. Dovolte mi hned učinit výhradu, že některé rysy chování budou jasné, pokud dobře rozumíte podstatě práce s časovými pásmy. Ale to je také téma na samostatný článek.

Nerozumím tvému

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

Zdá se, že co je zde nepochopitelné? Ale databáze stále nerozumí tomu, co jsme zde umístili na první místo – rok nebo den? A rozhodne se, že je 99. leden 2008, z čehož má mysl. Obecně řečeno, při přenosu dat v textovém formátu musíte velmi pečlivě zkontrolovat, jak správně je databáze rozpoznávala (zejména analyzovat parametr datestyle pomocí příkazu SHOW datestyle), protože nejasnosti v této záležitosti mohou být velmi drahé.

Odkud jsi přišel?

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

Proč databáze nerozumí explicitně zadanému času? Časové pásmo totiž nemá zkratku, ale celé jméno, což má smysl pouze v kontextu data, jelikož zohledňuje historii změn časového pásma a bez data to nefunguje. A už samotná formulace časové osy vyvolává otázky – co tím vlastně programátor myslel? Proto je zde vše logické, když se na to podíváte.

Co je s ním?

Představte si situaci. V tabulce máte pole s typem timestamptz. Chcete jej indexovat. Chápete však, že vytvoření indexu na tomto poli není vždy oprávněné kvůli jeho vysoké selektivitě (téměř všechny hodnoty tohoto typu budou jedinečné). Rozhodnete se tedy snížit selektivitu indexu přetypováním typu na datum. A čeká vás překvapení:

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

Co se děje? Faktem je, že k přetypování typu timestamptz na typ data se používá hodnota systémového parametru TimeZone, díky čemuž je funkce konverze typu závislá na uživatelském parametru, tzn. nestálý. Takové funkce nejsou v indexu povoleny. V tomto případě musíte výslovně uvést, ve kterém časovém pásmu se typ přetypování provádí.

Když teď není ani teď vůbec

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 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 je vrácen stejný bez ohledu na to, kolik času uplynulo od předchozího požadavku! Co se děje? Faktem je, že now() není aktuální čas, ale čas zahájení aktuální transakce. V rámci transakce se tedy nemění. Jakýkoli dotaz spuštěný mimo rámec transakce je implicitně zabalen do transakce, proto si nevšimneme, že čas vrácený jednoduchým SELECT now(); ve skutečnosti ne aktuální... Pokud chcete získat poctivý aktuální čas, musíte použít funkci clock_timestamp().

Spis číslo pět. bit

Trochu zvláštní

SELECT '111'::bit(4)

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

Na kterou stranu by měly být bity přidány v případě rozšíření typu? Zdá se, že je vlevo. Ale jen základna má na tuto věc jiný názor. Buďte opatrní: pokud se počet číslic při odlévání typu neshoduje, nedostanete to, co jste chtěli. To platí jak pro přidávání bitů doprava, tak pro ořezávání bitů. Také vpravo...

Spis číslo šest. Pole

Dokonce ani NULL nevystřelil

SELECT ARRAY[1, 2] || NULL

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

Jako normální lidé vychovaní na SQL očekáváme, že výsledek tohoto výrazu bude NULL. Ale nebylo to tam. Je vráceno pole. Proč? Protože v tomto případě základ přetypuje NULL na celočíselné pole a implicitně zavolá funkci array_cat. Stále však zůstává nejasné, proč tato „pole kočka“ neresetuje pole. Toto chování je také třeba mít na paměti.

Shrnout. Je tam spousta divných věcí. Většina z nich samozřejmě není tak kritická, aby mluvila o nehorázně nevhodném chování. A další jsou vysvětleny snadností použití nebo četností jejich použitelnosti v určitých situacích. Ale zároveň je tu mnoho překvapení. Proto o nich musíte vědět. Pokud se vám zdá něco divného nebo neobvyklého v chování jakéhokoli typu, napište do komentářů, rád doplním dokumentaci, která je na nich dostupná.

Zdroj: www.habr.com

Podezřelé typy