🥇 Verdächteg Typen

Et gëtt näischt verdächteg iwwer hir Erscheinung. Ausserdeem schéngen se Iech souguer gutt a laang Zäit vertraut. Awer dat ass nëmme bis Dir se kontrolléiert. Dëst ass wou se hir insidious Natur weisen, ganz anescht funktionnéieren wéi Dir erwaart hutt. An heiansdo maachen se eppes, wat Är Hoer ophalen mécht - zum Beispill verléieren se geheim Daten, déi hinnen uvertraut ginn. Wann Dir se konfrontéiert, behaapten se datt se sech net kennen, obwuel se an de Schatten fläisseg ënner der selwechter Hood schaffen. Et ass Zäit se endlech an proppert Waasser ze bréngen. Loosst eis och mat dëse verdächtegen Typen beschäftegen.

Datetyping am PostgreSQL, fir all seng Logik, bréngt heiansdo ganz komesch Iwwerraschungen. An dësem Artikel wäerte mir probéieren e puer vun hire Quirks ze klären, verstoen de Grond fir hir komesch Verhalen a verstoen, wéi net an alldeeglechen Praxis Problemer ze lafen. Fir d'Wourecht ze soen, hunn ech dësen Artikel och als eng Zort Referenzbuch fir mech zesummegestallt, e Referenzbuch dat einfach a kontroverse Fäll bezeechent ka ginn. Dofir gëtt et ersat wéi nei Iwwerraschungen vu verdächtegen Typen entdeckt ginn. Also, loosst eis goen, oh onermiddlech Datebank Tracker!

Dossier Nummer eent. real / duebel Präzisioun / numeresch / Suen

Et géif schéngen datt numeresch Aarte am mannsten problematesch sinn wat d'Iwwerraschungen am Verhalen ugeet. Mee egal wéi et ass. Also loosst eis mat hinnen ufänken. Also…

Vergiess wéi ze zielen

SELECT 0.1::real = 0.1

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

Ëm wat geet et? De Problem ass datt PostgreSQL déi ongetypte Konstant 0.1 op duebel Präzisioun konvertéiert a probéiert et mat 0.1 vun echtem Typ ze vergläichen. An dat si ganz aner Bedeitungen! D'Iddi ass reell Zuelen am Maschinn Erënnerung ze vertrieden. Zënter datt 0.1 net als endlech binär Fraktioun duergestallt ka ginn (et wier 0.0 (0011) am Binär), wäerten d'Zuelen mat verschiddene Bitdéiften anescht sinn, dofir d'Resultat datt se net gläich sinn. Allgemeng ass dëst en Thema fir en separaten Artikel; Ech wäert net méi am Detail hei schreiwen.

Wou kënnt de Feeler hier?

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

Vill Leit wëssen datt PostgreSQL funktionell Notatioun fir Typ Casting erlaabt. Dat ass, Dir kënnt net nëmmen 1::int schreiwen, awer och int(1), wat gläichwäerteg ass. Awer net fir Typen deenen hir Nimm aus verschiddene Wierder besteet! Dofir, wann Dir en numeresche Wäert op duebel Präzisiounstyp a funktionell Form wëllt werfen, benotzt den Alias vun dësem Typ float8, dat ass SELECT float8 (1).

Wat ass méi grouss wéi Infinity?

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

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

Kuckt wéi et ass! Et stellt sech eraus datt et eppes méi grouss ass wéi Infinity, an et ass NaN! Zur selwechter Zäit kuckt d'PostgreSQL Dokumentatioun eis mat éierlechen Aen a behaapt datt NaN offensichtlech méi grouss ass wéi all aner Zuel, an dofir onendlech. De Géigendeel ass och wouer fir -NaN. Moien, Matheliebhaber! Awer mir mussen drun erënneren datt dat alles am Kontext vun reellen Zuelen funktionnéiert.

Aen Ronn

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

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

Eng aner onerwaart Begréissung vun der Basis. Erënnert nach eng Kéier datt duebel Präzisioun an numeresch Aarte verschidde Ronneffekter hunn. Fir numeresch - déi üblech, wann 0,5 ofgerënnt ass, a fir duebel Präzisioun - 0,5 ass op déi nootste souguer ganz Zuel ofgerënnt.

Geld ass eppes Besonnesches

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

Geméiss PostgreSQL sinn d'Suen keng richteg Zuel. Laut e puer Leit och. Mir mussen drun erënneren datt d'Suentyp casting nëmme méiglech ass fir den numereschen Typ, sou wéi nëmmen déi numeresch Aart op d'Suenart gegoss ka ginn. Awer elo kënnt Dir domat spillen wéi Äert Häerz wëll. Awer et wäert net déiselwecht Suen sinn.

Smallint a Sequenz Generatioun

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 verschwënnt net gär Zäit op Trifles. Wat sinn dës Sequenzen baséiert op Smallint? int, net manner! Dofir, wann Dir probéiert déi uewe genannte Ufro auszeféieren, probéiert d'Datebank Smallint op eng aner ganz Zuelentyp ze werfen, a gesäit datt et e puer esou Casts kënne ginn. Wéi eng Besetzung ze wielen? Si kann dëst net entscheeden, an dowéinst Crash mat engem Feeler.

Dossier Nummer zwee. "char"/char/varchar/text

Eng Zuel vun oddities sinn och präsent an Charakter Zorte. Loosst eis se och kennen léieren.

Wéi eng Tricken sinn dat?

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

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

Wéi eng Zort "Char" ass dat, wéi eng Zort Clown ass dat? Mir brauchen déi net ... Well et mécht wéi eng gewéinlech Char, obwuel et an Zitaten ass. An et ënnerscheet sech vun engem reguläre Char, deen ouni Zitater ass, an datt et nëmmen den éischte Byte vun der Stringrepresentatioun ausgëtt, während en normale Char den éischte Charakter ausgëtt. An eisem Fall ass den éischte Charakter de Bréif P, deen an der Unicode Representatioun 2 Bytes ophëlt, wéi beweist duerch d'Konvertéierung vum Resultat an den Bytea Typ. An den Typ "char" hëlt nëmmen den éischte Byte vun dëser Unicode Representatioun. Da firwat ass dës Zort néideg? D'PostgreSQL Dokumentatioun seet datt dëst e speziellen Typ ass fir speziell Bedierfnesser benotzt. Also mir brauchen et onwahrscheinlech. Awer kuckt an seng Aen an Dir wäert Iech net verwiesselen wann Dir him mat sengem spezielle Verhalen begéint.

Extra Plazen. Aus Siicht, aus Verstand

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

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

Huelt e Bléck op d'Beispill uginn. Ech hunn all d'Resultater speziell an den Bytea-Typ ëmgewandelt, sou datt et kloer ze gesinn ass wat do war. Wou sinn d'Topplazen nom Goss op Varchar (6)? D'Dokumentatioun seet succinctly: "Wann de Wäert vum Charakter op eng aner Charaktertyp gegoss gëtt, gëtt de wäisse Raum verworf." Dëse Mëssverständnis muss erënnert ginn. An bemierkt datt wann eng zitéiert Stringkonstant direkt op den Typ varchar (6) gegoss gëtt, sinn déi hannendru Plazen erhale bleiwen. Sou sinn d'Wonner.

Datei Nummer dräi. json/jsonb

JSON ass eng separat Struktur déi säin eegent Liewen lieft. Dofir sinn seng Entitéiten an déi vu PostgreSQL liicht anescht. Hei sinn Beispiller.

Johnson et Johnson. fillt den Ënnerscheed

SELECT 'null'::jsonb IS NULL

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

D'Saach ass datt JSON seng eege Null Entitéit huet, déi net den Analog vun NULL am PostgreSQL ass. Zur selwechter Zäit kann den JSON-Objet selwer de Wäert NULL hunn, sou datt den Ausdrock SELECT null :: jsonb IS NULL (notéiert d'Feele vun eenzelen Zitater) wäert dës Kéier richteg zréckkommen.

Ee Bréif ännert alles

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

D'Saach ass datt json an jsonb komplett verschidde Strukture sinn. Am json gëtt den Objet gespäichert wéi ass, an am jsonb ass et scho gespäichert a Form vun enger parséierter, indexéierter Struktur. Dofir gouf am zweete Fall de Wäert vum Objet mam Schlëssel 1 vun [1, 2, 3] op [7, 8, 9] ersat, déi ganz um Enn mam selwechte Schlëssel an d'Struktur koumen.

Drénkt net Waasser aus Ärem Gesiicht

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

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

PostgreSQL a senger JSONB Implementatioun ännert d'Formatéierung vun reellen Zuelen, bréngt se an déi klassesch Form. Dëst geschitt net fir den JSON Typ. E bësse komesch, awer hien huet Recht.

Datei Nummer véier. Datum / Zäit / Zäitstempel

Et ginn och e puer oddities mat Datum / Zäit Zorte. Loosst eis se kucken. Loosst mech direkt reservéieren datt e puer vun de Verhalensmerkmale kloer ginn wann Dir d'Essenz vun der Aarbecht mat Zäitzonen gutt versteet. Mä dëst ass och en Thema fir en separaten Artikel.

Meng Är verstinn net

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

Et géif schéngen datt wat hei onverständlech ass? Awer d'Datebank versteet nach ëmmer net wat mir hei op der éischter Plaz setzen - d'Joer oder den Dag? A si entscheet, datt et den 99. Januar 2008 ass, deen hir Verstand bléist. Am Allgemengen, wann Dir Datumen am Textformat iwwerdroen, musst Dir ganz virsiichteg iwwerpréiwen wéi korrekt d'Datebank se erkannt huet (besonnesch analyséiert den Datestyle Parameter mam SHOW Datestyle Kommando), well Ambiguititéiten an dëser Matière kënne ganz deier sinn.

Wou hutt Dir dëst aus?

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

Firwat kann d'Datebank déi explizit spezifizéierter Zäit net verstoen? Well d'Zäitzone huet keng Ofkierzung, mee e ganzen Numm, wat nëmmen am Kontext vun engem Datum Sënn mécht, well se d'Geschicht vun den Zäitzonenännerungen berücksichtegt, an et funktionnéiert net ouni Datum. An déi ganz Formuléierung vun der Zäitlinn stellt Froen op - wat huet de Programméierer wierklech gemengt? Dofir ass alles logesch hei, wann Dir et kuckt.

Wat ass mat him falsch?

Stellt Iech d'Situatioun vir. Dir hutt e Feld an Ärer Tabell mat Typ timestamptz. Dir wëllt et indexéieren. Awer Dir verstitt datt de Bau vun engem Index op dësem Feld net ëmmer gerechtfäerdegt ass wéinst senger héijer Selektivitéit (bal all Wäerter vun dësem Typ wäert eenzegaarteg sinn). Also decidéiert Dir d'Selektivitéit vum Index ze reduzéieren andeems Dir den Typ op en Datum cast. An Dir kritt eng Iwwerraschung:

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

Ëm wat geet et? D'Tatsaach ass datt fir e Timestamptz Typ op en Datumtyp ze werfen, gëtt de Wäert vum TimeZone Systemparameter benotzt, wat d'Typkonversiounsfunktioun vun engem personaliséierte Parameter ofhängeg mécht, d.h. liichtflüchtege. Esou Funktiounen sinn net am Index erlaabt. An dësem Fall musst Dir explizit uginn, a wéi enger Zäitzone den Typ Goss duerchgefouert gëtt.

Wann elo guer net elo ass

Mir si gewinnt elo () den aktuellen Datum / Zäit zréckzekommen, andeems d'Zäitzone berücksichtegt gëtt. Awer kuckt op déi folgend Ufroen:

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;

Den Datum / Zäit gëtt d'selwecht zréckginn egal wéi vill Zäit zënter der viregter Ufro vergaang ass! Ëm wat geet et? De Fakt ass datt elo () net déi aktuell Zäit ass, awer d'Startzäit vun der aktueller Transaktioun. Dofir ännert sech net bannent der Transaktioun. All Ufro, déi ausserhalb vum Ëmfang vun enger Transaktioun lancéiert gëtt, ass implizit an enger Transaktioun gewéckelt, dofir bemierken mir net datt d'Zäit vun engem einfachen SELECT elo (); tatsächlech, net déi aktuell ... Wann Dir wëllt eng éierlech aktuell Zäit ze kréien, Dir musst d'Funktioun clock_timestamp benotzen ().

Datei Nummer fënnef. bëssen

Komesch e bëssen

SELECT '111'::bit(4)

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

Wéi eng Säit sollen d'Bits bäigefüügt ginn am Fall vun der Typverlängerung? Et schéngt lénks ze sinn. Awer nëmmen d'Basis huet eng aner Meenung zu dëser Matière. Sidd virsiichteg: Wann d'Zuel vun den Zifferen net entsprécht wann Dir en Typ cast, kritt Dir net wat Dir wollt. Dëst gëllt souwuel fir Bits op der rietser Säit ze addéieren an Bits ze trimmen. Och riets ...

Datei Nummer sechs. Arrays

Och NULL huet net gebrannt

SELECT ARRAY[1, 2] || NULL

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

Als normal Leit, déi op SQL opgewuess sinn, erwaarden mir datt d'Resultat vun dësem Ausdrock NULL ass. Mä et war net do. Eng Array gëtt zréck. Firwat? Well an dësem Fall werft d'Basis NULL op eng ganz Zuel Array an rifft implizit d'Array_cat Funktioun. Awer et bleift nach ëmmer onkloer firwat dës "Array Cat" d'Array net zrécksetzt. Dëst Verhalen muss och just erënnert ginn.

Zesummefaassen. Et gi vill komesch Saachen. Déi meescht vun hinnen sinn natierlech net sou kritesch fir iwwer e schrecklech onpassend Verhalen ze schwätzen. An anerer ginn duerch Einfachheet vun der Benotzung oder der Frequenz vun hirer Uwendung a bestëmmte Situatiounen erkläert. Awer gläichzäiteg ginn et vill Iwwerraschungen. Dofir musst Dir iwwer si wëssen. Wann Dir soss eppes komesch oder ongewéinlech am Verhalen vun iergendenger Aart fannt, schreiwt an de Kommentaren, ech wäert gären op d'Dossieren derzou bäidroen, déi op hinnen verfügbar sinn.

Source: will.com

Verdächteg Zorte