Suspekta typer

Det finns inget misstänkt med deras utseende. Dessutom verkar de till och med vara bekanta för dig väl och under lång tid. Men det är bara tills du kontrollerar dem. Det är här de visar sin lömska natur och fungerar helt annorlunda än du förväntade dig. Och ibland gör de något som får ditt hår att resa sig - till exempel förlorar de hemlig data som anförtrotts dem. När man konfronterar dem hävdar de att de inte känner varandra, även om de i skymundan jobbar hårt under samma huva. Det är dags att äntligen föra dem till rent vatten. Låt oss också ta itu med dessa misstänkta typer.

Datainmatning i PostgreSQL, trots all dess logik, ger ibland väldigt konstiga överraskningar. I den här artikeln kommer vi att försöka klargöra några av deras egenheter, förstå orsaken till deras konstiga beteende och förstå hur man inte stöter på problem i vardagen. Ärligt talat sammanställde jag den här artikeln också som en slags uppslagsbok för mig själv, en uppslagsbok som lätt kunde hänvisas till i kontroversiella fall. Därför kommer den att fyllas på när nya överraskningar från misstänkta typer upptäcks. Så, låt oss gå, o outtröttliga databasspårare!

Dokumentation nummer ett. verklig/dubbel precision/numerisk/pengar

Det verkar som om numeriska typer är minst problematiska när det gäller överraskningar i beteende. Men hur det än är. Så låt oss börja med dem. Så…

Glömde hur man räknar

SELECT 0.1::real = 0.1

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

Vad är problemet? Problemet är att PostgreSQL konverterar den otypade konstanten 0.1 till dubbel precision och försöker jämföra den med 0.1 av verklig typ. Och det är helt olika betydelser! Tanken är att representera reella tal i maskinminnet. Eftersom 0.1 inte kan representeras som en finit binär bråkdel (det skulle vara 0.0(0011) i binärt), kommer tal med olika bitdjup att vara olika, därav resultatet att de inte är lika. Generellt sett är detta ett ämne för en separat artikel, jag kommer inte att skriva mer i detalj här.

Var kommer felet ifrån?

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

Många vet att PostgreSQL tillåter funktionell notation för typgjutning. Det vill säga, du kan inte bara skriva 1::int, utan även int(1), vilket kommer att vara likvärdigt. Men inte för typer vars namn består av flera ord! Därför, om du vill gjuta ett numeriskt värde till dubbel precisionstyp i funktionell form, använd aliaset för denna typ float8, det vill säga SELECT float8(1).

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

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

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

Titta hur det är! Det visar sig att det finns något större än oändligheten, och det är NaN! Samtidigt ser PostgreSQL-dokumentationen på oss med ärliga ögon och hävdar att NaN uppenbarligen är större än något annat tal, och därför oändligt. Motsatsen gäller också för -NaN. Hej matteälskare! Men vi måste komma ihåg att allt detta fungerar i samband med reella tal.

Ögonrundning

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 basen. Återigen, kom ihåg att dubbel precision och numeriska typer har olika avrundningseffekter. För numerisk - den vanliga, när 0,5 avrundas uppåt, och för dubbel precision - avrundas 0,5 mot närmaste jämna heltal.

Pengar är något speciellt

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

Enligt PostgreSQL är pengar inte ett verkligt tal. Enligt vissa individer också. Vi måste komma ihåg att det bara är möjligt att kasta pengatypen till den numeriska typen, precis som bara den numeriska typen kan kastas till penningtypen. Men nu kan du leka med det som ditt hjärta vill. Men det blir inte samma pengar.

Smallint och 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 gillar inte att slösa tid på småsaker. Vilka är dessa sekvenser baserade på smallint? int, inte mindre! Därför, när man försöker köra ovanstående fråga, försöker databasen casta smallint till någon annan heltalstyp och ser att det kan finnas flera sådana casts. Vilken cast ska man välja? Hon kan inte avgöra detta, och kraschar därför med ett fel.

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

Ett antal konstigheter finns också i karaktärstyper. Låt oss också lära känna dem.

Vad är det för knep?

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

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

Vilken typ av "röding" är det här, vad är det här för clown? De behöver vi inte... För att den låtsas vara en vanlig röding, trots att den står inom citattecken. Och det skiljer sig från ett vanligt tecken, som är utan citattecken, genom att det bara matar ut den första byten i strängrepresentationen, medan ett normalt tecken matar ut det första tecknet. I vårt fall är det första tecknet bokstaven P, som i unicode-representationen tar upp 2 byte, vilket framgår av att konvertera resultatet till bytea-typen. Och "char"-typen tar bara den första byten av denna unicode-representation. Varför behövs då denna typ? PostgreSQL-dokumentationen säger att detta är en speciell typ som används för särskilda behov. Så det är osannolikt att vi behöver det. Men titta in i hans ögon och du kommer inte att misstas när du möter honom med hans speciella beteende.

Extra utrymmen. Utom synhåll, utom sinne

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å exemplet som ges. Jag konverterade speciellt alla resultat till bytea-typen, så att det var tydligt synligt vad som fanns där. Var är de efterföljande utrymmena efter gjutning till varchar(6)? Dokumentationen säger kortfattat: "När värdet av tecken överförs till en annan teckentyp, kasseras efterföljande blanksteg." Denna motvilja måste komma ihåg. Och observera att om en strängkonstant i citationstecken gjuts direkt till typen varchar(6), så bevaras de efterföljande mellanrummen. Sådana är miraklen.

Fil nummer tre. json/jsonb

JSON är en separat struktur som lever sitt eget liv. Därför är dess enheter och de för PostgreSQL något annorlunda. Här är exempel.

Johnson och Johnson. Känn skillnaden

SELECT 'null'::jsonb IS NULL

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

Saken är att JSON har sin egen null-enhet, som inte är analogen till NULL i PostgreSQL. Samtidigt kan själva JSON-objektet mycket väl ha värdet NULL, så uttrycket SELECT null::jsonb ÄR NULL (observera frånvaron av enstaka citattecken) kommer att returnera sant den här gången.

En bokstav förändrar allt

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 är att json och jsonb är helt olika strukturer. I json lagras objektet som det är, och i jsonb är det redan lagrat i form av en analyserad, indexerad struktur. Det är därför i det andra fallet, objektets värde med nyckel 1 ersattes från [1, 2, 3] till [7, 8, 9], som kom in i strukturen i slutet med samma nyckel.

Drick inte vatten från ditt ansikte

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 ändrar formateringen av reella tal och för dem till den klassiska formen. Detta händer inte för JSON-typen. Lite konstigt, men han har rätt.

Fil nummer fyra. datum/tid/tidsstämpel

Det finns också några konstigheter med datum/tidstyper. Låt oss titta på dem. Låt mig omedelbart göra en reservation för att några av beteendeegenskaperna blir tydliga om du väl förstår essensen av att arbeta med tidszoner. Men detta är också ett ämne för en separat artikel.

Jag förstår inte ditt

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 verkar som att vad är obegripligt här? Men databasen förstår fortfarande inte vad vi sätter på första plats här - året eller dagen? Och hon bestämmer sig för att det är den 99 januari 2008, vilket gör henne förbannad. Generellt sett, när du sänder datum i textformat, måste du mycket noggrant kontrollera hur korrekt databasen kände igen dem (i synnerhet analysera datestyle-parametern med kommandot SHOW datestyle), eftersom oklarheter i denna fråga kan vara mycket dyra.

Var kom du ifrån?

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

Varför kan inte databasen förstå den explicit angivna tiden? Eftersom tidszonen inte har en förkortning, utan ett fullständigt namn, vilket bara är vettigt i samband med ett datum, eftersom det tar hänsyn till historiken för tidszonsändringar, och det fungerar inte utan ett datum. Och själva formuleringen av tidslinjen väcker frågor – vad menade programmeraren egentligen? Därför är allt logiskt här, om man tittar på det.

Vad är fel med honom?

Föreställ dig situationen. Du har ett fält i din tabell med typen timestamptz. Du vill indexera det. Men du förstår att det inte alltid är motiverat att bygga ett index på detta fält på grund av dess höga selektivitet (nästan alla värden av denna typ kommer att vara unika). Så du bestämmer dig för att minska indexets selektivitet genom att sätta typen till ett datum. Och du får en överraskning:

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

Vad är problemet? Faktum är att för att casta en timestamptz-typ till en datumtyp, används värdet på TimeZone-systemparametern, vilket gör typkonverteringsfunktionen beroende av en anpassad parameter, d.v.s. flyktig. Sådana funktioner är inte tillåtna i indexet. I det här fallet måste du uttryckligen ange i vilken tidszon typgjutningen utförs.

När nu är inte ens nu alls

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

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/tid returneras samma oavsett hur lång tid som har gått sedan föregående begäran! Vad är problemet? Faktum är att now() inte är den aktuella tiden, utan starttiden för den aktuella transaktionen. Därför ändras det inte inom transaktionen. Varje fråga som lanseras utanför omfattningen av en transaktion är implicit inlindad i en transaktion, vilket är anledningen till att vi inte märker att tiden returneras av en enkel SELECT now(); faktiskt inte den nuvarande... Om du vill få en ärlig aktuell tid måste du använda funktionen clock_timestamp() .

Fil nummer fem. bit

Lite konstigt

SELECT '111'::bit(4)

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

Vilken sida ska bitarna läggas till vid typförlängning? Det verkar vara till vänster. Men bara basen har en annan uppfattning i denna fråga. Var försiktig: om antalet siffror inte stämmer överens när du gjuter en typ kommer du inte att få det du ville ha. Detta gäller både att lägga till bitar till höger och trimma bitar. Även till höger...

Fil nummer sex. Matriser

Inte ens NULL avfyrade

SELECT ARRAY[1, 2] || NULL

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

Som normala personer uppvuxna med SQL förväntar vi oss att resultatet av detta uttryck är NULL. Men det fanns inte där. En array returneras. Varför? Eftersom basen i det här fallet castar NULL till en heltalsmatris och anropar implicit funktionen array_cat. Men det är fortfarande oklart varför denna "array cat" inte återställer arrayen. Detta beteende måste också bara komma ihåg.

Sammanfatta. Det finns många konstiga saker. De flesta av dem är naturligtvis inte så kritiska att de talar om uppenbart olämpligt beteende. Och andra förklaras av användarvänlighet eller hur ofta de är tillämpliga i vissa situationer. Men samtidigt finns det många överraskningar. Därför måste du känna till dem. Om du hittar något annat konstigt eller ovanligt i beteendet av någon typ, skriv i kommentarerna, jag kommer gärna att lägga till de underlag som finns tillgängliga på dem.

Källa: will.com