🥇Gyanús típusok

A megjelenésükben nincs semmi gyanús. Sőt, még jól is és sokáig ismerősnek tűnnek. De ez csak addig van, amíg nem ellenőrzi őket. Itt mutatják meg alattomos természetüket, teljesen másképp dolgoznak, mint ahogyan azt várta. És néha olyat tesznek, amitől égnek áll a haja – például elveszítik a rájuk bízott titkos adatokat. Amikor szembesülsz velük, azt állítják, hogy nem ismerik egymást, bár az árnyékban keményen dolgoznak egy burkolat alatt. Ideje végre tiszta vízhez vinni őket. Foglalkozzunk ezekkel a gyanús típusokkal is.

Az adatok beírása a PostgreSQL-ben, minden logikája ellenére, néha nagyon furcsa meglepetéseket tartogat. Ebben a cikkben megpróbáljuk tisztázni néhány furcsaságukat, megérteni furcsa viselkedésük okát, és megérteni, hogyan ne ütközzön problémákba a mindennapi gyakorlatban. Az igazat megvallva ezt a cikket egyfajta referenciakönyvnek is összeállítottam magamnak, olyan referenciakönyvnek, amelyre vitatott esetekben könnyen lehet hivatkozni. Ezért amint újabb meglepetéseket fedeznek fel a gyanús típusoktól, akkor pótolni fogják. Szóval, gyerünk, ó, fáradhatatlan adatbázis-követők!

Első számú dosszié. valódi/dupla pontosság/numerikus/pénz

Úgy tűnik, hogy a numerikus típusok a legkevésbé problematikusak a viselkedésbeli meglepetések szempontjából. De mindegy, hogy van. Kezdjük tehát velük. Így…

Elfelejtett számolni

SELECT 0.1::real = 0.1

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

Mi a helyzet? A probléma az, hogy a PostgreSQL a 0.1 típus nélküli konstanst kétszeres pontosságra konvertálja, és megpróbálja összehasonlítani a valós típusú 0.1-gyel. És ezek teljesen más jelentések! Az ötlet az, hogy valós számokat ábrázoljunk a gép memóriájában. Mivel a 0.1 nem ábrázolható véges bináris törtként (binárisban 0.0(0011) lenne), a különböző bitmélységű számok eltérőek lesznek, ezért az eredmény, hogy nem egyenlőek. Általánosságban elmondható, hogy ez egy külön cikk témája, itt nem írok részletesebben.

Honnan ered a hiba?

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

Sokan tudják, hogy a PostgreSQL lehetővé teszi a funkcionális jelöléseket a típusöntéshez. Vagyis nem csak 1::int írhatsz, hanem int(1-et is), ami ekvivalens lesz. De nem olyan típusokra, amelyek neve több szóból áll! Ezért, ha egy numerikus értéket szeretne kétszeres pontosságú típusba önteni funkcionális formában, használja a float8 típusú álnevet, vagyis a SELECT float8(1)-et.

Mi nagyobb a végtelennél?

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

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

Nézd meg, milyen! Kiderült, hogy van valami, ami nagyobb a végtelennél, és ez a NaN! Ugyanakkor a PostgreSQL dokumentáció őszinte szemmel néz ránk, és azt állítja, hogy a NaN nyilvánvalóan nagyobb, mint bármely más szám, és ezért a végtelen. Ennek az ellenkezője is igaz a -NaN esetében. Sziasztok matek szerelmesei! De emlékeznünk kell arra, hogy mindez a valós számok kontextusában működik.

Szem kerekítése

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

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

Újabb váratlan üdvözlet a bázisról. Ne feledje, hogy a dupla pontosságú és a numerikus típusok különböző kerekítési hatásokkal rendelkeznek. Numerikus - a szokásos, amikor a 0,5-öt felfelé kerekítik, és a dupla pontosság esetén - a 0,5-öt a legközelebbi páros egészre kerekítik.

A pénz különleges dolog

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

A PostgreSQL szerint a pénz nem valós szám. Egyesek szerint is. Emlékeznünk kell arra, hogy a pénztípust csak a numerikus típusba lehet önteni, ahogyan a pénztípusba is csak a numerikus típust. De most úgy játszhatsz vele, ahogy a szíved akarja. De ez nem ugyanaz a pénz.

Smallint és sorozatgenerálás

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

A PostgreSQL nem szereti az időt apróságokra pazarolni. Mik ezek a szekvenciák a smallint alapján? int, nem kevesebb! Ezért, amikor megpróbálja végrehajtani a fenti lekérdezést, az adatbázis megpróbálja a smallint valamilyen más egész típusba önteni, és úgy látja, hogy több ilyen leadás is lehet. Melyik szereposztást válasszam? Ezt nem tudja eldönteni, ezért hibával összeomlik.

kettes számú fájl. "char"/char/varchar/text

Számos furcsaság is jelen van a karaktertípusokban. Ismerjük meg őket is.

Milyen trükkök ezek?

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

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

Milyen típusú "char" ez, milyen bohóc ez? Nincs szükségünk ezekre... Mert úgy tesz, mintha egy közönséges char lenne, pedig idézőjelben van. És abban különbözik a hagyományos karaktertől, amely idézőjelek nélkül van, csak a karakterlánc reprezentáció első bájtját adja ki, míg a normál karakter az első karaktert. Esetünkben az első karakter a P betű, amely a unicode ábrázolásban 2 bájtot foglal el, ezt bizonyítja az eredmény bytea típusúra konvertálása. És a „char” típus csak az első bájtot veszi fel ennek az unicode-ábrázolásnak. Akkor miért van szükség erre a típusra? A PostgreSQL dokumentációja szerint ez egy speciális típus, amelyet speciális igényekhez használnak. Tehát nem valószínű, hogy szükségünk lesz rá. De nézz a szemébe, és nem fogsz tévedni, ha találkozol vele a különleges viselkedésével.

Extra terek. Felejtsd el

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

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

Vessen egy pillantást a megadott példára. Az összes eredményt kifejezetten bytea típusra konvertáltam, hogy jól látható legyen, mi van ott. Hol vannak a záró szóközök a varchar(6) casting után? A dokumentáció tömören leszögezi: "Ha a karakter értékét egy másik karaktertípusba önti, a záró szóközt el kell dobni." Ezt az ellenszenvet emlékezni kell. És vegye figyelembe, hogy ha egy idézőjeles karakterlánc-állandót közvetlenül a varchar(6) típusba öntünk, a záró szóközök megmaradnak. Ilyenek a csodák.

Harmadik fájl. json/jsonb

A JSON egy különálló struktúra, amely saját életét éli. Ezért ennek entitásai és a PostgreSQL entitásai kissé eltérnek. Itt vannak példák.

Johnson és Johnson. érezd a különbséget

SELECT 'null'::jsonb IS NULL

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

A helyzet az, hogy a JSON-nak saját null entitása van, amely nem a NULL analógja a PostgreSQL-ben. Ugyanakkor magának a JSON-objektumnak is lehet a NULL értéke, így a SELECT null::jsonb IS NULL kifejezés (figyelje meg az idézőjelek hiányát) ezúttal igazat ad vissza.

Egy betű mindent megváltoztat

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

A helyzet az, hogy a json és a jsonb teljesen különböző struktúrák. A json-ban az objektum úgy van tárolva, ahogy van, a jsonb-ban pedig már elemzett, indexelt struktúra formájában. Éppen ezért a második esetben az objektum 1-es kulccsal való értékét [1, 2, 3]-ról [7, 8, 9]-re cseréltük, ami a legvégén, ugyanazzal a kulccsal került be a szerkezetbe.

Ne igyál vizet az arcodból

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

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

A PostgreSQL a JSONB implementációjában megváltoztatja a valós számok formázását, és a klasszikus formába hozza őket. Ez nem történik meg a JSON típusnál. Kicsit furcsa, de igaza van.

Négyes számú fájl. dátum/idő/időbélyeg

A dátum/idő típusokkal kapcsolatban is vannak furcsaságok. Nézzük meg őket. Hadd tegyek egy fenntartást, hogy bizonyos viselkedési jellemzők világossá váljanak, ha jól megérti az időzónákkal való munka lényegét. De ez is egy külön cikk témája.

Nem értem a tiédet

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

Úgy tűnik, mi itt az érthetetlen? De az adatbázis még mindig nem érti, mit teszünk itt az első helyre – az évet vagy a napot? És úgy dönt, hogy 99. január 2008-e van, ami feldobja a fejét. Általánosságban elmondható, hogy a dátumok szöveges formátumban történő továbbításakor nagyon alaposan ellenőrizni kell, hogy az adatbázis mennyire helyesen ismerte fel őket (különösen elemezze a datestyle paramétert a SHOW datestyle paranccsal), mivel ebben a kérdésben a kétértelműségek nagyon költségesek lehetnek.

Honnan jöttél?

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

Miért nem tudja az adatbázis megérteni a kifejezetten meghatározott időt? Ugyanis az időzónának nem rövidítése, hanem teljes neve van, aminek csak dátum összefüggésében van értelme, hiszen figyelembe veszi az időzóna változástörténetét, és dátum nélkül nem működik. Már az idővonal megfogalmazása is kérdéseket vet fel – mire gondolt valójában a programozó? Ezért itt minden logikus, ha megnézzük.

Mi a gond vele?

Képzeld el a helyzetet. A táblázatban van egy timestampz típusú mező. Indexelni szeretné. De megérti, hogy az index felépítése ezen a területen nem mindig indokolt a nagy szelektivitás miatt (majdnem minden ilyen típusú érték egyedi lesz). Tehát úgy dönt, hogy csökkenti az index szelektivitását úgy, hogy a típust dátumra adja. És kapsz egy meglepetést:

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

Mi a helyzet? A tény az, hogy egy időbélyeg típus dátumtípusba öntéséhez a TimeZone rendszerparaméter értéke kerül felhasználásra, ami a típuskonverziós függvényt egyéni paramétertől teszi függővé, pl. illó. Az ilyen funkciók nem engedélyezettek az indexben. Ebben az esetben kifejezetten meg kell adni, hogy melyik időzónában történik a típusú cast.

Amikor a most még egyáltalán nincs

Megszoktuk, hogy a now() az aktuális dátumot/időt adja vissza, figyelembe véve az időzónát. De nézze meg a következő kérdéseket:

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;

A dátum/idő ugyanaz, függetlenül attól, hogy mennyi idő telt el az előző kérés óta! Mi a helyzet? A helyzet az, hogy a now() nem az aktuális időpont, hanem az aktuális tranzakció kezdő időpontja. Ezért nem változik a tranzakción belül. A tranzakció hatókörén kívül elindított lekérdezések implicit módon tranzakcióba vannak csomagolva, ezért nem vesszük észre, hogy az egyszerű SELECT now(); valójában nem az aktuálisat... Ha őszinte pontos időt akarunk kapni, akkor a clock_timestamp() függvényt kell használnunk.

Ötös számú akta. bit

Furcsa egy kicsit

SELECT '111'::bit(4)

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

Típusbővítés esetén melyik oldalra kell hozzáadni a biteket? Úgy tűnik, hogy a bal oldalon. De csak a bázisnak van más véleménye ebben a kérdésben. Legyen óvatos: ha a számjegyek száma nem egyezik egy típus leadásakor, akkor nem azt kapja, amit akart. Ez vonatkozik mind a bitek jobb oldali hozzáadására, mind a bitek vágására. Szintén a jobb oldalon...

Hatos számú akta. Tömbök

Még a NULL sem lőtt

SELECT ARRAY[1, 2] || NULL

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

Mint az SQL-en nevelkedett normál emberek, ennek a kifejezésnek az eredménye NULL lesz. De nem volt ott. Egy tömb kerül visszaadásra. Miért? Mert ebben az esetben a bázis NULL értéket ad egy egész tömbhöz, és implicit módon meghívja az array_cat függvényt. De továbbra sem világos, hogy ez a „tömbmacska” miért nem állítja vissza a tömböt. Erre a viselkedésre is csak emlékezni kell.

Összesít. Rengeteg furcsaság van. A legtöbbjük persze nem annyira kritikus, hogy kirívóan helytelen viselkedésről beszéljen. Másokat pedig a könnyű használat vagy bizonyos helyzetekben való alkalmazhatóságuk gyakorisága magyaráz. De ugyanakkor sok meglepetés is adódik. Ezért tudnia kell róluk. Ha bármi mást furcsának vagy szokatlannak találsz bármely típus viselkedésében, írd meg kommentben, szívesen kiegészítem a rajtuk elérhető dossziékat.

Forrás: will.com

Gyanús típusok