🥇Mistenkelige typer

Det er ikke noe mistenkelig med utseendet deres. Dessuten virker de til og med kjente for deg godt og lenge. Men det er bare til du sjekker dem. Det er her de viser sin lumske natur, og jobber helt annerledes enn du forventet. Og noen ganger gjør de noe som får håret til å reise seg – for eksempel mister de hemmelige data som er betrodd dem. Når du konfronterer dem, hevder de at de ikke kjenner hverandre, selv om de i skyggen jobber hardt under samme panser. Det er på tide å endelig bringe dem til rent vann. La oss også behandle disse mistenkelige typene.

Dataskriving i PostgreSQL, på tross av all logikken, gir noen ganger veldig merkelige overraskelser. I denne artikkelen vil vi prøve å avklare noen av deres særheter, forstå årsaken til deres merkelige oppførsel og forstå hvordan man ikke får problemer i hverdagen. For å fortelle sannheten, har jeg satt sammen denne artikkelen også som en slags oppslagsbok for meg selv, en oppslagsbok som lett kunne refereres til i kontroversielle saker. Derfor vil den fylles på etter hvert som nye overraskelser fra mistenkelige typer oppdages. Så la oss gå, oh utrettelige databasesporere!

Dossier nummer én. ekte/dobbel presisjon/numerisk/penger

Det ser ut til at numeriske typer er de minst problematiske når det gjelder overraskelser i oppførsel. Men uansett hvordan det er. Så la oss begynne med dem. Så…

Glemte hvordan man teller

SELECT 0.1::real = 0.1

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

Hva er i veien? Problemet er at PostgreSQL konverterer den utypede konstanten 0.1 til dobbel presisjon og prøver å sammenligne den med 0.1 av ekte type. Og dette er helt andre betydninger! Tanken er å representere reelle tall i maskinminnet. Siden 0.1 ikke kan representeres som en endelig binær brøk (det ville vært 0.0(0011) i binær), vil tall med forskjellige bitdybder være forskjellige, derav resultatet at de ikke er like. Generelt sett er dette et emne for en egen artikkel; jeg skal ikke skrive mer detaljert her.

Hvor kommer feilen fra?

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

Mange vet at PostgreSQL tillater funksjonell notasjon for typecasting. Det vil si at du kan skrive ikke bare 1::int, men også int(1), som vil være ekvivalent. Men ikke for typer hvis navn består av flere ord! Derfor, hvis du ønsker å kaste en numerisk verdi til dobbel presisjonstype i funksjonell form, bruk aliaset til denne typen float8, det vil si SELECT float8(1).

Hva er større enn uendelighet?

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

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

Se hvordan det er! Det viser seg at det er noe større enn uendelig, og det er NaN! Samtidig ser PostgreSQL-dokumentasjonen på oss med ærlige øyne og hevder at NaN åpenbart er større enn noe annet tall, og derfor uendelig. Det motsatte gjelder også for -NaN. Hei, matteelskere! Men vi må huske at alt dette fungerer i sammenheng med reelle tall.

Øyeavrunding

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

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

Nok en uventet hilsen fra basen. Igjen, husk at dobbel presisjon og numeriske typer har forskjellige avrundingseffekter. For numerisk - den vanlige, når 0,5 rundes opp, og for dobbel presisjon - avrundes 0,5 mot nærmeste partall.

Penger er noe spesielt

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

I følge PostgreSQL er ikke penger et reelt tall. I følge noen individer også. Vi må huske at casting av pengetypen bare er mulig til den numeriske typen, akkurat som bare den numeriske typen kan castes til pengetypen. Men nå kan du leke med det som hjertet ditt vil. Men det blir ikke de samme pengene.

Smallint og sekvensgenerering

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 liker ikke å kaste bort tid på bagateller. Hva er disse sekvensene basert på smallint? int, ikke mindre! Derfor, når du prøver å utføre spørringen ovenfor, prøver databasen å kaste smallint til en annen heltallstype, og ser at det kan være flere slike kast. Hvilken rollebesetning å velge? Dette kan hun ikke bestemme, og krasjer derfor med en feil.

Fil nummer to. "char"/char/varchar/tekst

En rekke særheter er også til stede i karaktertyper. La oss bli kjent med dem også.

Hva slags triks er dette?

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

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

Hva slags «røye» er dette, hva slags klovn er dette? Vi trenger ikke de... Fordi den later til å være en vanlig røye, selv om den står i anførselstegn. Og det skiller seg fra et vanlig tegn, som er uten anførselstegn, ved at det bare gir ut den første byten i strengrepresentasjonen, mens et vanlig tegn gir ut det første tegnet. I vårt tilfelle er det første tegnet bokstaven P, som i unicode-representasjonen tar opp 2 byte, som bevist ved å konvertere resultatet til bytea-typen. Og "char"-typen tar bare den første byten av denne unicode-representasjonen. Så hvorfor er denne typen nødvendig? PostgreSQL-dokumentasjonen sier at dette er en spesiell type som brukes for spesielle behov. Så vi trenger det neppe. Men se inn i øynene hans og du vil ikke ta feil når du møter ham med hans spesielle oppførsel.

Ekstra plass. Ute av syne, ute av sinn

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

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

Ta en titt på eksemplet gitt. Jeg konverterte spesielt alle resultatene til bytea-typen, slik at det var godt synlig hva som var der. Hvor er de etterfølgende mellomrommene etter støping til varchar(6)? Dokumentasjonen sier kortfattet: "Når verdien av karakter overføres til en annen karaktertype, forkastes etterfølgende mellomrom." Denne motviljen må huskes. Og legg merke til at hvis en angitt strengkonstant støpes direkte til typen varchar(6), blir de etterfølgende mellomrommene bevart. Slik er miraklene.

Fil nummer tre. json/jsonb

JSON er en egen struktur som lever sitt eget liv. Derfor er enhetene og de til PostgreSQL litt forskjellige. Her er eksempler.

Johnson og Johnson. Føl forskjellen

SELECT 'null'::jsonb IS NULL

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

Saken er at JSON har sin egen null-enhet, som ikke er analogen til NULL i PostgreSQL. Samtidig kan selve JSON-objektet godt ha verdien NULL, så uttrykket SELECT null::jsonb ER NULL (merk fraværet av enkle anførselstegn) vil returnere sant denne gangen.

En bokstav forandrer alt

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

Saken er at json og jsonb er helt forskjellige strukturer. I json lagres objektet som det er, og i jsonb er det allerede lagret i form av en analysert, indeksert struktur. Det er derfor i det andre tilfellet, verdien av objektet med nøkkel 1 ble erstattet fra [1, 2, 3] til [7, 8, 9], som kom inn i strukturen helt til slutt med samme nøkkel.

Ikke drikk vann fra ansiktet ditt

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

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

PostgreSQL i sin JSONB-implementering endrer formateringen av reelle tall, og bringer dem til den klassiske formen. Dette skjer ikke for JSON-typen. Litt rart, men han har rett.

Fil nummer fire. dato/klokkeslett/tidsstempel

Det er også noen rariteter med dato/klokkesletttyper. La oss se på dem. La meg ta en reservasjon med en gang at noen av atferdstrekkene blir tydelige hvis du forstår essensen av å jobbe med tidssoner. Men dette er også et tema for en egen artikkel.

Mine dine forstår ikke

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

Det ser ut til at hva er uforståelig her? Men databasen forstår fortsatt ikke hva vi setter på første plass her – året eller dagen? Og hun bestemmer seg for at det er 99. januar 2008, noe som slår henne i hodet. Generelt sett, når du overfører datoer i tekstformat, må du veldig nøye sjekke hvor riktig databasen gjenkjente dem (spesielt analyser datestyle-parameteren med SHOW datestyle-kommandoen), siden tvetydigheter i denne saken kan være svært kostbare.

Hvor fikk du dette fra?

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

Hvorfor kan ikke databasen forstå den eksplisitt angitte tiden? Fordi tidssonen ikke har en forkortelse, men et fullt navn, noe som gir mening bare i sammenheng med en dato, siden den tar hensyn til historien om tidssoneendringer, og den fungerer ikke uten en dato. Og selve formuleringen av tidslinjen reiser spørsmål – hva mente programmereren egentlig? Derfor er alt logisk her, hvis du ser på det.

Hva er galt med ham?

Forestill deg situasjonen. Du har et felt i tabellen med typen timestamptz. Du vil indeksere den. Men du forstår at det ikke alltid er berettiget å bygge en indeks på dette feltet på grunn av dens høye selektivitet (nesten alle verdier av denne typen vil være unike). Så du bestemmer deg for å redusere selektiviteten til indeksen ved å sette typen til en dato. Og du får en overraskelse:

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

Hva er i veien? Faktum er at for å caste en timestamptz-type til en datotype, brukes verdien av TimeZone-systemparameteren, som gjør typekonverteringsfunksjonen avhengig av en tilpasset parameter, dvs. flyktige. Slike funksjoner er ikke tillatt i indeksen. I dette tilfellet må du eksplisitt angi i hvilken tidssone typekastet utføres.

Når nå er ikke engang nå i det hele tatt

Vi er vant til nå() å returnere gjeldende dato/klokkeslett, tatt i betraktning tidssonen. Men se på følgende spørsmål:

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;

Datoen/klokkeslettet returneres likt uansett hvor lang tid som har gått siden forrige forespørsel! Hva er i veien? Faktum er at now() ikke er gjeldende tidspunkt, men starttidspunkt for gjeldende transaksjon. Derfor endres det ikke i transaksjonen. Enhver spørring som lanseres utenfor omfanget av en transaksjon er pakket inn i en transaksjon implisitt, og det er grunnen til at vi ikke legger merke til at tiden returneres av en enkel SELECT now(); faktisk ikke den nåværende... Hvis du ønsker å få en ærlig gjeldende tid, må du bruke funksjonen clock_timestamp().

Fil nummer fem. bit

Litt merkelig

SELECT '111'::bit(4)

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

Hvilken side skal bitene legges til ved typeutvidelse? Det ser ut til å være til venstre. Men bare basen har en annen oppfatning om denne saken. Vær forsiktig: Hvis antall sifre ikke stemmer overens når du kaster en type, får du ikke det du ønsket. Dette gjelder både å legge til bits til høyre og trimme bits. Også til høyre...

Fil nummer seks. Matriser

Selv NULL skjøt ikke

SELECT ARRAY[1, 2] || NULL

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

Som vanlige mennesker oppdratt på SQL, forventer vi at resultatet av dette uttrykket er NULL. Men det var ikke der. En matrise returneres. Hvorfor? For i dette tilfellet kaster basen NULL til en heltallsmatrise og kaller implisitt funksjonen array_cat. Men det er fortsatt uklart hvorfor denne "matrisekatten" ikke tilbakestiller matrisen. Denne oppførselen må også bare huskes.

Oppsummer. Det er mange merkelige ting. De fleste av dem er selvfølgelig ikke så kritiske at de snakker om åpenbart upassende oppførsel. Og andre er forklart med brukervennlighet eller hyppigheten av deres anvendelighet i visse situasjoner. Men samtidig er det mange overraskelser. Derfor må du vite om dem. Hvis du finner noe annet merkelig eller uvanlig i oppførselen av noen typer, skriv i kommentarene, jeg vil gjerne legge til dokumentene som er tilgjengelige for dem.

Kilde: www.habr.com

Mistenkelige typer