Misstänkta typer

I deras yttre väcker ingenting misstankar. Mer än så, de verkar till och med bekanta för dig. Men det är bara så länge du inte kontrollerar dem. Just här visar de sin illvilliga natur och fungerar inte alls som du förväntat dig. Ibland gör de något så skrämmande att håret reser sig på huvudet – till exempel, de förlorar de hemliga uppgifter som anförtrotts dem. När du konfronterar dem hävdar de att de inte känner varandra, fastän de flitigt arbetar i skuggan under samma tak. Det är dags att avslöja dem. Låt oss också reda ut dessa misstänkta typer.

Datatyper i PostgreSQL, trots sin logik, ger ibland väldigt konstiga överraskningar. I denna artikel ska vi försöka klargöra några av deras egenheter, förstå orsaken till deras underliga beteenden och lära oss hur man undviker problem i vardaglig praktik. För att vara ärlig har jag skrivit denna artikel även som en slags referens för mig själv, en referens som man enkelt kan konsultera vid tveksamma fall. Därför kommer den att uppdateras i takt med att nya överraskningar från misstänkta typer upptäckts. Så, låt oss börja, o orubbliga databassökare!

Dossier nummer ett. real/double precision/numeric/money

Det skulle kunna verka som att numeriska typer är de minst problematiska när det gäller överraskningar i beteendet. Men så är det inte. Så vi börjar med dem. Så…

Vi har glömt hur man räknar

SELECT 0.1::real = 0.1

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

Vad är problemet? Jo, PostgreSQL konverterar den otypade konstanten 0.1 till typen double precision och försöker jämföra den med 0.1 av typen real. Detta är helt olika värden! Problemet ligger i representationen av flyttal i maskinens minne. Eftersom 0.1 inte kan representeras som ett ändligt binärt bråk (det blir 0.0(0011) i binär form), kommer tal med olika precision att skilja sig åt, därav resultatet att de inte är lika. Över lag är detta ämnet för en separat artikel, så jag ska inte gå in på det mer här.

Varifrån kommer felet?

SELECT double precision(1)

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

Många vet att PostgreSQL tillåter funktionell typkonvertering. Det betyder att man kan skriva inte bara 1::int, utan också int(1), vilket är likvärdigt. Men inte för typer vars namn består av flera ord! Så om du vill konvertera ett numeriskt värde till typen double precision i funktionell form, använd aliaset för denna typ float8, det vill säga SELECT float8(1).

Vad är större än oändlighet?

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

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

Titta där! Det visar sig att det finns något som är större än oändlighet, och det är NaN! Samtidigt ser PostgreSQL-dokumentationen på oss med ärliga ögon och hävdar att NaN är av naturliga skäl större än vilket annat tal som helst, och därmed oändligheten. Det omvända gäller också för -NaN. Hej, kalkylatorälskare! Men kom ihåg att allt detta gäller inom ramen för reella tal.

Ombalansering av ögon

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

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

Ännu en oväntad hälsning från databasen. Och återigen måste man komma ihåg att olika ombalanseringar gäller för typerna double precision och numeric. För numeric — den vanliga, där 0,5 rundas uppåt, och för double precision — sker rundningen av 0,5 mot närmaste jämna heltal.

Pengar är något speciellt

SELECT '10'::money::float8

ERROR:  kan inte konvertera typ money till double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Fel **********
ERROR: kan inte konvertera typ money till double precision
SQL-status: 42846
Symbol: 19

Enligt PostgreSQL är pengar inte ett reellt tal. Enligt vissa individer är det också så. Men vi måste komma ihåg att typkonvertering av money endast är möjlig till typen numeric, precis som money endast kan konverteras från typ numeric. Men med den kan man leka som man vill. Men det kommer inte att vara de samma pengarna.

Smallint och sekvensgenerering

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

ERROR:  funktionen generate_series(smallint, smallint, smallint) är inte unik
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
HINT:  Kunde inte välja den bästa kandidatfunktionen. Du kan behöva lägga till explicita typkonverteringar.
********** Fel **********
ERROR: funktionen generate_series(smallint, smallint, smallint) är inte unik
SQL-status: 42725
Tips: Kunde inte välja den bästa kandidatfunktionen. Du kan behöva lägga till explicita typkonverteringar.
Symbol: 18

PostgreSQL är inte för den som vill snåla. Vilka sekvenser baserade på smallint skulle det vara? int, minst! Därför, när försöker att köra den nämnda frågan, försöker databasen att konvertera smallint till någon annan heltalstyp och ser att sådana konverteringar kan finnas flera av. Vilken konvertering ska väljas? Det kan den inte avgöra, och därför kraschar den med ett felmeddelande.

Dossier nummer två. "char"/char/varchar/text

Det finns en del konstigheter även bland teckentyperna. Låt oss också bekanta oss med dem.

Vad är det här för trick?

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

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

Vad är den här typen "char", vad är det för clown? Vi behöver inte sådana… För att den låtsas vara en vanlig char, bara för att den är inom citat. Den skiljer sig från en vanlig char utan citat, genom att den bara returnerar den första byten av den strängrepresentationen, medan den normala char returnerar den första tecknet. I vårt fall är det första tecknet bokstaven П, som i Unicode-representation tar 2 byte, vilket indikeras av konverteringen av resultatet till typen bytea. Typen "char" tar bara den första byten av denna Unicode-representation. Så varför är denna typ nödvändig? PostgreSQL-dokumentationen säger att detta är en speciell typ, som används för speciella behov. Så det kommer förmodligen inte att behövas. Men titta den i ögonen och gör inte misstag, när du möter den med sitt speciella beteende.

Överskottsutrymmen. Borta från ögonen, borta från hjärtat.

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

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

Titta på exemplet ovan. Jag har medvetet konverterat alla resultat till typ bytea för att tydligt kunna se vad som finns där. Var finns de avslutande utrymmena efter konvertering till typ varchar(6)? Dokumentationen påstår lakoniskt: "Vid konvertering av värdet character till en annan teckentyp slängs utfyllnadsutrymmen bort." Denna ogillande bör man komma ihåg. Och observera att om en strängkonstant inom citat konverteras direkt till typ varchar(6), så bevaras de avslutande utrymmena. Sådana mirakel.

Dossier nummer tre. json/jsonb

JSON är en separat struktur som har sitt eget liv. Därför skiljer sig dess entiteter något från PostgreSQL:s entiteter. Här är exempel.

Johnson & Johnson. Känn skillnaden

VÄLJ 'null'::jsonb ÄR NULL

?kolumn?
booleskt
---------
f

Det handlar om att JSON har sin egen null-enhet, som inte är en liknelse till NULL i PostgreSQL. Samtidigt kan själva JSON-objektet mycket väl ha värdet NULL, så uttrycket SELECT null::jsonb ÄR NULL (notera avsaknaden av enkla citat) returnerar denna gång true.

En bokstav ändrar allt

VÄLJ '{"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]}

---

VÄLJ '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb

             jsonb
             jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}

Det handlar om att json och jsonb är helt olika strukturer. I json lagras objektet som det är, medan i jsonb lagras det som en parsead och indexerad struktur. Det är därför värdet för objektet under nyckel 1 byttes från [1, 2, 3] till [7, 8, 9], vilket kom in i strukturen sist med samma nyckel.

Man kan inte dricka vatten från ansiktet

VÄLJ '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

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

PostgreSQL i sin implementation av JSONB ändrar formateringen av flyttal till sitt klassiska utseende. För JSON-typen sker detta inte. Lite konstigt, men det är dess rätt.

Dossier nummer fyra. datum/tid/timestamp

Det finns också vissa underligheter med datatyper för datum/tid. Låt oss titta närmare på dem. Jag vill genast påpeka att vissa av beteendets egenskaper blir förståeliga om man har god insikt i hur man arbetar med tidszoner. Men det är också ett ämne för en separat artikel.

Min din inte förstå

VÄLJ '08-Jan-99'::datum

FEL: datum/tid fältvärde utanför intervall: "08-Jan-99"
RAD 1: VÄLJ '08-Jan-99'::datum
               ^
HINT: Kanske behöver du en annan "datastil" inställning.
********** Fel **********
FEL: datum/tid fältvärde utanför intervall: "08-Jan-99"
SQL-tillstånd: 22008
Hint: Kanske behöver du en annan "datastil" inställning.
Symbol: 8

Det verkar som att det inte finns något som är oklart här? Men ändå förstår inte databasen vad vi ställde först — året eller dagen? Och den beslutar att det är den 99 januari 2008, vilket får den att förlora förståndet. Allmänt sett, vid överlämning av datum i textformat, måste man vara mycket noga med hur korrekt databasen känner igen dem (specifikt analysera parametern datastil med kommandot SHOW datastil), eftersom tvetydigheter i den frågan kan kosta mycket.

Varifrån kom du?

SELECT '04:05 Europe/Moscow'::time

ERROR: ogiltig inmatningssyntax för tids typ: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** Fel **********
ERROR: ogiltig inmatningssyntax för tids typ: "04:05 Europe/Moscow"
SQL-status: 22007
Symbol: 8

Varför kan databasen inte förstå den uttryckligen angivna tiden? För att tidszonen anges med ett fullständigt namn istället för en förkortning, vilket bara har mening i samband med ett datum, eftersom det tar hänsyn till historien om förändringar av tidszoner, och den fungerar inte utan ett datum. Och själva formuleringen av tidssträngen väcker frågor — vad menade programören egentligen? Så allt detta är logiskt, om man gräver lite djupare.

Vad är fel med honom?

Föreställ dig en situation. Du har ett fält i tabellen med typen timestamptz. Du vill indexera det. Men du inser att det inte alltid är motiverat att bygga ett index för detta fält med hänsyn till dess höga selektivitet (nästan alla värden av denna typ kommer att vara unika). Därför bestämmer du dig för att minska selektiviteten av indexet genom att omvandla den här typen till datum. Och får en överraskning:

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

ERROR: funktioner i index uttryck måste vara märkta IMMUTABLE
********** Fel **********
ERROR: funktioner i index uttryck måste vara märkta IMMUTABLE
SQL-status: 42P17

Vad är problemet? Det är så att för att omvandla typen timestamptz till typ date används värdet av systemparametern TimeZone, vilket gör omvandlingsfunktionen beroende av en konfigurerbar parameter, dvs. förändringsbar (volatile). Sådana funktioner är inte tillåtna i ett index. I det här fallet måste man uttryckligen ange i vilken tidszon omvandlingen av typen görs.

När now inte är really now

Vi är vana vid att now() returnerar aktuellt datum/tid med hänsyn till tidszonen. Men titta på följande förfrågningar:

START TRANSACTION;
SELECT now();

            now
  timestamp med tidszon
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp med tidszon
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp med tidszon
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

Datum/tid returneras alltid samma oavsett hur länge som har gått sedan senaste begäran! Vad är problemet? Jo, now() är inte den aktuella tiden, utan tiden för början av den aktuella transaktionen. Därför ändras den inte inom ramen för transaktionen. Alla begärningar som utförs utanför transaktionen kapslas in en transaktion implicit, så vi märker inte att tiden som ges av en enkel begäran SELECT now(); faktiskt inte är aktuell... Om du vill få den verkliga aktuella tiden, måste du använda funktionen clock_timestamp().

Dossier nummer fem. bit

Lite konstigt

SELECT '111'::bit(4)

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

Från vilken sida ska man lägga till bitar vid typförlängning? Det verkar som om det ska vara från vänster. Men databasen har en annan åsikt om det. Var försiktig: vid typkonvertering med olika bitlängder får du något helt annat än vad du ville ha. Detta gäller såväl tillägg av bitar till höger som beskärning av bitar. Också till höger...

Dossier nummer sex. Arrayer

Inte ens NULL slog till

SELECT ARRAY[1, 2] || NULL

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

Som normala människor uppfostrade på SQL förväntar vi oss att resultatet av detta uttryck ska vara NULL. Men icke! En array returneras. Varför? För i det här fallet konverterar databasen NULL till en heltalsarray och anropar implicit funktionen array_cat. Men det förblir oklart varför denna "array-katt" inte nollställer arrayen. Detta beteende måste man också bara memorera.

Sammanfattningsvis. Det finns många konstigheter. De flesta av dem är naturligtvis inte så kritiska att det handlar om uppenbart ologiskt beteende. Andra förklaras av användarvänlighet eller hur ofta de tillämpas i vissa situationer. Men samtidigt finns det många överraskningar. Därför måste man känna till dem. Om du hittar något annat konstigt eller ovanligt i beteendet hos någon typ, skriv gärna i kommentarerna så kompletterar jag med nöje de befintliga dossierna.

Källa: habr.com