Der er intet mistænkeligt ved deres udseende. Desuden virker de endda kendte for dig godt og længe. Men det er kun indtil du tjekker dem. Det er her, de viser deres lumske natur og arbejder helt anderledes, end du havde forventet. Og nogle gange gør de noget, der får dit hår til at rejse sig – for eksempel mister de hemmelige data, der er betroet dem. Når du konfronterer dem, hævder de, at de ikke kender hinanden, selvom de i skyggen arbejder hårdt under samme hætte. Det er tid til endelig at bringe dem til rent vand. Lad os også beskæftige os med disse mistænkelige typer.
Dataindtastning i PostgreSQL giver trods al dens logik nogle gange meget mærkelige overraskelser. I denne artikel vil vi forsøge at afklare nogle af deres særheder, forstå årsagen til deres mærkelige adfærd og forstå, hvordan man ikke støder ind i problemer i hverdagens praksis. For at sige sandheden har jeg også samlet denne artikel som en slags opslagsbog for mig selv, en opslagsbog, der let kunne henvises til i kontroversielle sager. Derfor vil den blive genopfyldt, efterhånden som nye overraskelser fra mistænkelige typer opdages. Så lad os gå, åh utrættelige database trackere!
Dossier nummer et. reel/dobbelt præcision/numerisk/penge
Det ser ud til, at numeriske typer er de mindst problematiske med hensyn til overraskelser i adfærd. Men uanset hvordan det er. Så lad os starte med dem. Så…
Glemte hvordan man tæller
SELECT 0.1::real = 0.1
?column?
boolean
---------
fHvad er der galt? Problemet er, at PostgreSQL konverterer den utyperede konstant 0.1 til dobbelt præcision og forsøger at sammenligne den med 0.1 af reel type. Og det er helt andre betydninger! Ideen er at repræsentere reelle tal i maskinens hukommelse. Da 0.1 ikke kan repræsenteres som en endelig binær brøk (det ville være 0.0(0011) i binær), vil tal med forskellige bitdybder være forskellige, deraf resultatet, at de ikke er ens. Generelt er dette et emne for en separat artikel; jeg vil ikke skrive mere detaljeret her.
Hvor kommer fejlen 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
Символ: 24Mange mennesker ved, at PostgreSQL tillader funktionel notation til typecasting. Det vil sige, at du ikke kun kan skrive 1::int, men også int(1), som vil være ækvivalent. Men ikke for typer, hvis navne består af flere ord! Derfor, hvis du ønsker at caste en numerisk værdi til dobbelt præcisionstype i funktionel form, skal du bruge aliaset af denne type float8, det vil sige SELECT float8(1).
Hvad er større end uendelighed?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tSe hvordan det er! Det viser sig, at der er noget større end uendelighed, og det er NaN! Samtidig ser PostgreSQL-dokumentationen på os med ærlige øjne og hævder, at NaN åbenlyst er større end noget andet tal, og derfor uendeligt. Det modsatte gælder også for -NaN. Hej, matematikelskere! Men vi skal huske, at alt dette fungerer i sammenhæng med reelle tal.
Øjenafrunding
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Endnu en uventet hilsen fra basen. Husk igen, at dobbelt præcision og numeriske typer har forskellige afrundingseffekter. For numerisk - den sædvanlige måde, når 0,5 rundes op, og for dobbelt præcision - afrundes 0,5 mod det nærmeste lige heltal.
Penge er noget særligt
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
Символ: 19Ifølge PostgreSQL er penge ikke et rigtigt tal. Ifølge nogle personer også. Vi skal huske, at casting af pengetypen kun er mulig til den numeriske type, ligesom kun den numeriske type kan castes til pengetypen. Men nu kan du lege med det, som dit hjerte ønsker. Men det bliver ikke de samme penge.
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.
Символ: 18PostgreSQL kan ikke lide at spilde tid på småting. Hvad er disse sekvenser baseret på smallint? int, intet mindre! Derfor, når du prøver at udføre ovenstående forespørgsel, forsøger databasen at caste smallint til en anden heltalstype og ser, at der kan være flere sådanne casts. Hvilket cast skal man vælge? Det kan hun ikke tage stilling til, og går derfor ned med en fejl.
Fil nummer to. "char"/char/varchar/tekst
En række mærkværdigheder er også til stede i karaktertyper. Lad os også lære dem at kende.
Hvad er det for nogle tricks?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fHvilken type "char" er dette, hvad er det for en klovn? Dem har vi ikke brug for... For den giver sig ud for at være en almindelig char, selvom den står i anførselstegn. Og det adskiller sig fra et almindeligt tegn, som er uden anførselstegn, ved at det kun udsender den første byte af strengrepræsentationen, mens et normalt tegn udsender det første tegn. I vores tilfælde er det første tegn bogstavet P, som i unicode-repræsentationen fylder 2 bytes, hvilket fremgår af konvertering af resultatet til bytea-typen. Og "char"-typen tager kun den første byte af denne unicode-repræsentation. Hvorfor er denne type så nødvendig? PostgreSQL-dokumentationen siger, at dette er en speciel type, der bruges til særlige behov. Så det får vi næppe brug for. Men kig ind i hans øjne, og du vil ikke tage fejl, når du møder ham med hans særlige opførsel.
Ekstra pladser. Ude af øje ude af sind
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Tag et kig på eksemplet. Jeg konverterede specielt alle resultaterne til bytea-typen, så det var tydeligt at se, hvad der var der. Hvor er de efterfølgende mellemrum efter støbning til varchar(6)? Dokumentationen siger kortfattet: "Når værdien af karakter castes til en anden karaktertype, kasseres efterste mellemrum." Denne modvilje skal huskes. Og bemærk, at hvis en citeret strengkonstant støbes direkte til typen varchar(6), bevares de efterfølgende mellemrum. Sådan er miraklerne.
Fil nummer tre. json/jsonb
JSON er en separat struktur, der lever sit eget liv. Derfor er dets enheder og PostgreSQL's enheder lidt forskellige. Her er eksempler.
Johnson og Johnson. mærke forskellen
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fSagen er, at JSON har sin egen null-entitet, som ikke er analogen til NULL i PostgreSQL. Samtidig kan selve JSON-objektet godt have værdien NULL, så udtrykket SELECT null::jsonb ER NULL (bemærk fraværet af enkelte anførselstegn) vil returnere sandt denne gang.
Et bogstav ændrer 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]}Sagen er, at json og jsonb er helt forskellige strukturer. I json er objektet gemt som det er, og i jsonb er det allerede gemt i form af en parset, indekseret struktur. Derfor blev værdien af objektet i det andet tilfælde erstattet af tast 1 fra [1, 2, 3] til [7, 8, 9], som kom ind i strukturen til allersidst med den samme nøgle.
Drik ikke vand fra dit ansigt
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 ændrer formateringen af reelle tal, hvilket bringer dem til den klassiske form. Dette sker ikke for JSON-typen. Lidt mærkeligt, men han har ret.
Fil nummer fire. dato/tid/tidsstempel
Der er også nogle mærkværdigheder med dato/klokkeslæt typer. Lad os se på dem. Lad mig tage et forbehold med det samme, at nogle af adfærdsegenskaberne bliver tydelige, hvis du godt forstår essensen af at arbejde med tidszoner. Men dette er også et emne for en separat artikel.
Mine dine forstår det 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.
Символ: 8Det ser ud til, at hvad er uforståeligt her? Men databasen forstår stadig ikke, hvad vi sætter på førstepladsen her - året eller dagen? Og hun beslutter sig for, at det er den 99. januar 2008, der blæser hende. Generelt set, når du sender datoer i tekstformat, skal du meget omhyggeligt kontrollere, hvor korrekt databasen genkendte dem (analyser især datestyle-parameteren med SHOW datestyle-kommandoen), da uklarheder i denne sag kan være meget dyre.
Hvor har du fået 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
Символ: 8Hvorfor kan databasen ikke forstå den eksplicit angivne tid? Fordi tidszonen ikke har en forkortelse, men et fulde navn, hvilket kun giver mening i forbindelse med en dato, da det tager højde for historikken for tidszoneændringer, og det fungerer ikke uden en dato. Og selve formuleringen af tidslinjen rejser spørgsmål – hvad mente programmøren egentlig? Derfor er alt logisk her, hvis man ser på det.
Hvad der er galt med ham?
Forestil dig situationen. Du har et felt i din tabel med typen timestamptz. Du vil indeksere det. Men du forstår, at det ikke altid er berettiget at bygge et indeks på dette felt på grund af dets høje selektivitet (næsten alle værdier af denne type vil være unikke). Så du beslutter dig for at reducere indeksets selektivitet ved at caste 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-состояние: 42P17Hvad er der galt? Faktum er, at for at caste en timestamptz-type til en datotype, bruges værdien af TimeZone-systemparameteren, hvilket gør typekonverteringsfunktionen afhængig af en brugerdefineret parameter, dvs. flygtige. Sådanne funktioner er ikke tilladt i indekset. I dette tilfælde skal du udtrykkeligt angive, i hvilken tidszone typecastet udføres.
Når nu slet ikke er nu
Vi er vant til nu() at returnere den aktuelle dato/tid, idet tidszonen tages i betragtning. Men se på følgende spørgsmå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/klokkeslættet returneres det samme, uanset hvor lang tid der er gået siden forrige anmodning! Hvad er der galt? Faktum er, at now() ikke er det aktuelle tidspunkt, men starttidspunktet for den aktuelle transaktion. Derfor ændres det ikke inden for transaktionen. Enhver forespørgsel, der lanceres uden for en transaktions omfang, er implicit pakket ind i en transaktion, hvilket er grunden til, at vi ikke bemærker, at tiden returneres af en simpel SELECT now(); faktisk ikke den nuværende... Hvis du vil have et ærligt aktuelt klokkeslæt, skal du bruge funktionen clock_timestamp() .
Fil nummer fem. lidt
En lille smule mærkeligt
SELECT '111'::bit(4)
bit
bit(4)
------
1110Hvilken side skal bits tilføjes i tilfælde af typeudvidelse? Det ser ud til at være til venstre. Men kun basen har en anden mening om denne sag. Vær forsigtig: Hvis antallet af cifre ikke stemmer overens, når du kaster en type, får du ikke, hvad du ønskede. Dette gælder både tilføjelse af bits til højre og trimning af bits. Også til højre...
Fil nummer seks. Arrays
Selv NULL brændte ikke
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Som normale mennesker opvokset på SQL, forventer vi, at resultatet af dette udtryk er NULL. Men det var der ikke. Et array returneres. Hvorfor? For i dette tilfælde kaster basen NULL til et heltalsarray og kalder implicit funktionen array_cat. Men det er stadig uklart, hvorfor denne "array kat" ikke nulstiller arrayet. Denne adfærd skal også bare huskes.
Sammenfatte. Der er masser af mærkelige ting. De fleste af dem er selvfølgelig ikke så kritiske, at de taler om åbenlyst upassende adfærd. Og andre forklares med brugervenlighed eller hyppigheden af deres anvendelighed i visse situationer. Men samtidig er der mange overraskelser. Derfor skal du kende til dem. Hvis du finder noget andet mærkeligt eller usædvanligt i adfærden af nogen art, så skriv i kommentarerne, jeg vil med glæde tilføje til de tilgængelige dossierer på dem.
Kilde: www.habr.com