Deres ydre fremtræden vækker ikke mistanke. Tværtimod virker de endda velkendte og nærtstående. Men det er kun indtil du tjekker dem. Her afslører de deres snedige væsen og opfører sig slet ikke, som du havde forventet. Nogle gange gør de noget, der får håret til at rejse sig - for eksempel, de mister de betroede hemmelige oplysninger. Når du konfronterer dem ansigt til ansigt, hævder de, at de ikke kender hinanden, selvom de flittigt arbejder sammen i det skjulte. Det er tid til at bringe dem frem i lyset. Lad os undersøge disse mistænkelige personer.
Datatyperne i PostgreSQL, på trods af deres logik, giver faktisk nogle gange meget mærkelige overraskelser. I denne artikel vil vi forsøge at afklare nogle af deres finurligheder, finde ud af årsagen til deres mærkelige adfærd og forstå, hvordan man undgår problemer i dagligdagen. Ærligt talt har jeg også skrevet denne artikel som en slags opslagsværk for mig selv, et opslagsværk, som man nemt kan henvise til i tvivlssituationer. Derfor vil det blive opdateret, efterhånden som jeg opdager nye overraskelser fra de mistænkelige typer. Så lad os komme i gang, uermodige databaseforskere!
Dossier nummer ét. real/double precision/numeric/money
Det ville synes, at de numeriske typer er de mindst problematiske i forhold til overraskelser i adfærd. Men sådan er det ikke. Så lad os starte her. Så…
Har glemt at tælle
SELECT 0.1::real = 0.1
?kolonne?
boolean
---------
fHvad er problemet? Problemet er, at PostgreSQL konverterer den unummererede konstant 0.1 til typen double precision og forsøger at sammenligne den med 0.1 af typen real. Og det er helt forskellige værdier! Problemet ligger i repræsentationen af reelle tal i maskinens hukommelse. Da 0.1 ikke kan repræsenteres som en endelig binær brøk (det vil være 0.0(0011) i binær form), vil tal med forskellig præcision være forskellige, og derfor er resultatet, at de ikke er lig med hinanden. Generelt set er dette et emne for en separat artikel, så jeg vil ikke uddybe det her.
Hvor kommer fejlen fra?
SELECT double precision(1)
ERROR: syntaksfejl tæt på "("
LINE 1: SELECT double precision(1)
^
********** Fejl **********
ERROR: syntaksfejl tæt på "("
SQL-tilstand: 42601
Symbol: 24Mange ved, at PostgreSQL tillader funktionel typekonvertering. Det vil sige, at man kan skrive ikke kun 1::int, men også int(1), hvilket vil være lig med hinanden. Men kun ikke for typer, hvis navne består af flere ord! Så hvis du vil konvertere en numerisk værdi til typet double precision i funktionel form, så brug aliaset for denne type float8, altså SELECT float8(1).
Hvad er større end uendelighed?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tSe nu engang! Det viser sig, at der er noget mere end uendelighed, og det er NaN! Dokumentationen for PostgreSQL kigger os ærligt i øjnene og påstår, at NaN utvivlsomt er større end ethvert andet tal og dermed også større end uendelighed. Det samme gælder omvendt for -NaN. Hej, elskere af matematik! Men det er vigtigt at huske, at alt dette gælder i konteksten af reelle tal.
Runding af øjne
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Endnu en uventet hilsen fra databasen. Og igen skal vi huske, at der gælder forskellige rundinger for typerne double precision og numeric. For numeric er det almindelig runding, hvor 0,5 rundes op, mens for double precision sker runding af 0,5 mod nærmeste lige heltal.
Penge er noget særligt
SELECT '10'::money::float8
ERROR: kan ikke caste type money til double precision
LINE 1: SELECT '10'::money::float8
^
********** Fejl **********
ERROR: kan ikke caste type money til double precision
SQL-tilstand: 42846
Symbol: 19Ifølge PostgreSQL er penge ikke et reelt tal. Nogle individer mener det samme. Vi skal huske, at typekonvertering fra money kun er muligt til typen numeric, ligesom money kun kan konverteres til typen numeric. Men med den kan man lege, som man ønsker. Men det vil ikke længere være de samme penge.
Smallint og sekvensgenerering
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERROR: funktionen generate_series(smallint, smallint, smallint) er ikke unik
LINE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
HINT: Kunne ikke vælge en bedste kandidatfunktion. Du skal muligvis tilføje eksplicitte typecasting.
********** Fejl **********
ERROR: funktionen generate_series(smallint, smallint, smallint) er ikke unik
SQL-tilstand: 42725
Tip: Kunne ikke vælge en bedste kandidatfunktion. Du skal muligvis tilføje eksplicitte typecasting.
Symbol: 18PostgreSQL går ikke på kompromis. Hvilke sekvenser baseres der på smallint? int, mindst! Derfor, når man forsøger at udføre den ovenfor nævnte forespørgsel, forsøger databasen at konvertere smallint til en anden heltalstype og ser, at der kan være flere konverteringer. Hvilken konvertering skal vælges? Det kan den ikke beslutte, og derfor fejler den med en fejlmelding.
Sagsnummer to. "char"/char/varchar/text
Der er også nogle mærkelige ting ved de bogstavtyper. Lad os også blive bekendt med dem.
Hvad er det for magi?
SELECT 'PETYA'::"char"
, 'PETYA'::"char"::bytea
, 'PETYA'::char
, 'PETYA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | P | xd09fHvad er denne type "char"? Hvad er det for en klovn? Vi har ikke brug for sådanne… Fordi den opfører sig som en almindelig char, blot fordi den er i citationstegn. Og den adskiller sig fra den almindelige char, der ikke er i citationstegn, ved at den kun udskriver det første byte af den stringrepræsentation, mens den normale char udskriver det første tegn. I vores tilfælde er det første tegn bogstavet P, som i unicode-repræsentation optager 2 bytes, hvilket fremgår af konverteringen af resultatet til typen bytea. Og typen "char" tager kun det første byte af denne unicode-repræsentation. Så hvorfor er denne type nødvendig? PostgreSQL-dokumentationen siger, at det er en speciel type, der anvendes til særlige formål. Så den vil næppe være nyttig for os. Men kig den i øjnene og lad dig ikke narre, når du møder den med sin specielle opførsel.
Unødvendige mellemrum. Ude af syne, 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 | x616263202020Se på det givne eksempel. Jeg har bevidst konverteret alle resultater til typen bytea, så det er tydeligt, hvad der ligger der. Hvor er de trailing mellemrum efter konverteringen til typen varchar(6)? Dokumentationen siger kort og godt: “Når en værdi af karakter konverteres til en anden bogstavtype, kasseres de fyldende mellemrum.” Denne uvilje skal huskes. Og bemærk, at hvis den stringkonstant i citationstegn straks konverteres til typen varchar(6), bevares de afsluttende mellemrum. Sådan nogle vidundere.
Sagsnummer tre. json/jsonb
JSON er en separat struktur, der lever sit eget liv. Derfor adskiller dens entiteter sig lidt fra PostgreSQL's entiteter. Her er nogle eksempler.
Johnson & Johnson. Oplev forskellen
SELECT 'null'::jsonb IS NULL
?kolonne?
boolean
---------
fPointen er, at JSON har sin egen null-entitet, der ikke svarer til NULL i PostgreSQL. Samtidig kan JSON-objektet godt have værdien NULL, hvilket betyder, at udtrykket SELECT null::jsonb IS NULL (bemærk fraværet af enkelt citationstegn) denne gang vil returnere true.
Et enkelt 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]}Pointen er, at json og jsonb er helt forskellige strukturer. I json gemmes objektet som det er, mens det i jsonb opbevares i en nedbrudt indekseret struktur. Derfor blev værdien af objektet ved nøgle 1 ændret fra [1, 2, 3] til [7, 8, 9], som kom ind i strukturen til sidst med den samme nøgle.
Man kan ikke drikke vand fra ansigtet
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 flydende tal, så de fremstår i klassisk format. Dette sker ikke for JSON-typen. Det er lidt underligt, men det er deres ret.
Dossier nummer fire. dato/tid/tidsstempel
Der er også nogle mærkelige forhold med datatyperne. Lad os se på dem. Jeg vil straks sige, at nogle af adfærdsfunktionerne bliver klare, hvis man forstår essensen af arbejdet med tidszoner. Men det er også et emne for en separat artikel.
Jeg forstår ikke, hvad du siger
SELECT '08-Jan-99'::date
ERROR: dato/tid feltværdi er uden for rækkevidde: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Måske skal du bruge en anden "datestyle" indstilling.
********** Fejl **********
ERROR: dato/tid feltværdi er uden for rækkevidde: "08-Jan-99"
SQL-tilstand: 22008
Hint: Måske skal du bruge en anden "datestyle" indstilling.
Symbol: 8Det synes da underligt, hvad er der ikke at forstå? Men databasen kan ikke regne ud, hvad vi har sat først - år eller dag? Og den beslutter, at det er 99. januar 2008, hvilket sprænger dens hjerne. Generelt set bør man være meget opmærksom ved overførsel af datoer i tekstformat på, hvor korrekt databasen genkender dem (især ved at analysere datastylen med SHOW datestyle), da tvetydigheder i dette spørgsmål kan koste dyrt.
Hvor stammer du fra?
SELECT '04:05 Europa/København'::time
FEJL: ugyldig inputsyntaks for type time: "04:05 Europa/København"
LINE 1: SELECT '04:05 Europa/København'::time
^
********** Fejl **********
FEJL: ugyldig inputsyntaks for type time: "04:05 Europa/København"
SQL-status: 22007
Symbol: 8Hvorfor kan databasen ikke forstå det angivne tidspunkt? Fordi tidszonen er angivet med det fulde navn og ikke blot en forkortelse, hvilket kun giver mening i konteksten af en dato, da det tager højde for historiske ændringer i tidszoner, og det fungerer ikke uden en dato. Samtidig rejser også formuleringen af tidsstrengen spørgsmål — hvad mente programmøren reelt? Så alt dette giver mening, hvis man ser nærmere på det.
Hvad er galt med det?
Forestil dig en situation. Du har et felt i din tabel med typen timestamptz. Du vil indeksere det. Men du indser, at det ikke altid giver mening at opbygge et indeks baseret på dette felt på grund af dets høje selektivitet (næsten alle værdier af denne type vil være unikke). Derfor beslutter du at reducere indeksets selektivitet ved at konvertere denne type til en dato. Og så får du en overraskelse:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
FEJL: funktioner i indeksudtrykket skal markeres som IMMUTABLE
********** Fejl **********
FEJL: funktioner i indeksudtrykket skal markeres som IMMUTABLE
SQL-status: 42P17Hvad er problemet? At konverteringen fra typen timestamptz til typen date anvender værdien af systemparameteren TimeZone, hvilket gør konverteringsfunktionen afhængig af en konfigurerbar parameter, altså foranderlig (volatile). Sådanne funktioner er ikke tilladte i indekser. I dette tilfælde skal man eksplicit angive, hvilken tidszone konverteringen udføres i.
Når now slet ikke er now
Vi er vant til, at now() returnerer den aktuelle dato/tid under hensyntagen til tidszonen. Men se på følgende forespørgsler:
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;Dato/tid returneres ens uanset hvor lang tid der er gået siden den forrige anmodning! Hvad er grunden? Det er fordi now() ikke er den aktuelle tid, men tiden for starten af den nuværende transaktion. Derfor ændres det ikke inden for transaktionen. Enhver anmodning, der startes uden for transaktionen, indkapsles implicit i en transaktion, så vi bemærker ikke, at tiden, der gives af en simpel SELECT now(); faktisk ikke er aktuel... Hvis du vil have den ægte aktuelle tid, skal du bruge funktionen clock_timestamp().
Dossier nummer fem. bit
Struktur er lidt mærkelig
SELECT '111'::bit(4)
bit
bit(4)
------
1110Fra hvilken side skal man tilføje bits i tilfælde af udvidelsen af typen? Det virker som om, det er til venstre. Men databasen har en anden mening om dette. Vær forsigtig: hvis antallet af bits ikke stemmer overens ved typekonvertering, får du ikke hvad du ønskede. Dette gælder både for tilføjelse af bits til højre og for trimming af bits. Også til højre...
Dossier nummer seks. Arrays
Selv NULL skød ikke
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Som normale mennesker opdraget på SQL, forventer vi at resultatet af dette udtryk vil være NULL. Men ikke her. Der returneres et array. Hvorfor? Fordi databasen i dette tilfælde konverterer NULL til et heltalsarray og implicit kalder funktionen array_cat. Men det er stadig uklart, hvorfor denne "array kat" ikke nulstiller arrayet. Sådan adfærd skal også bare huskes.
Lad os opsummere. Der er mange underligheder. De fleste af dem er naturligvis ikke så kritiske, at man kan tale om åbenlyst uhensigtsmæssig adfærd. Andre forklares af brugervenlighed eller hyppigheden af deres anvendelse i forskellige situationer. Men samtidig er der mange overraskelser. Derfor er det vigtigt at vide om dem. Hvis du finder noget endnu mere underligt eller usædvanligt i adfærden af nogen typer, så skriv i kommentarerne, så vil jeg med glæde opdatere de eksisterende dossiere.
Kilde: habr.com
