Në pamjen e jashtme, asgjë nuk ngjall dyshime. Për më tepër, ato madje duket se të janë shumë të njohura. Por kjo është vetëm deri sa ti t'i kontrollosh. Atëherë ato do ta tregojnë natyrën e tyre të keqe, duke vepruar krejt ndryshe nga sa e prisje. Disa herë, ato hedhin diçka që të lë pa fjalë — si për shembull, humbasin të dhëna sekrete që iu besuan. Kur iu bën presion ballë për ballë, ato thonë se nuk e njohin njëra-tjetrën, ndonëse në hije po punojnë ngushtë. Është koha të nxjerrim në dritë këta figura të dyshimta. Le të merremi me ta.
Tipizimi i të dhënave në PostgreSQL, megjithëse është logjik, herë pas here ofron surpriza shumë të çuditshme. Në këtë artikull do të përpiqemi të sqarojmë disa nga kapricot e tyre, të kuptojmë shkakun e sjelljes së tyre të çuditshme dhe të mësojmë se si të shmangim problemet në praktikën e përditshme. Të drejtën e them, këtë artikull e kam përgatitur gjithashtu si një lloj udhëzuesi për veten time, një udhëzues që mund të konsultohem lehtësisht në raste kontestues. Prandaj ai do të plotësohet ndërsa zbulojmë surpriza të reja nga këto figura të dyshimta. Pra, le të fillojmë, o ndjekës të palodhur të bazave të të dhënave!
Dosja numër një. real/double precision/numeric/money
Duket se llojet numerike janë më pak problematike në aspektin e surprizave në sjellje. Por sigurisht që jo. Prandaj, ne do ta fillojmë nga ato. Pra...
Kemi harruar të numërojmë
SELECT 0.1::real = 0.1
?column?
boolean
---------
fÇfarë ndodhi? Procesi është se PostgreSQL e konverton konstantën e papërcaktuar 0.1 në tipin double precision dhe përpiqet ta krahasojë atë me 0.1 në tipin real. Çfarë janë absolutisht vlera të ndryshme! Problemi qëndron në përfaqësimin e numrave të vërtetë në kujtesën kompjuterike. Duke qenë se 0.1 nuk mund të përfaqësohet si një fraksion binar përfundimtar (do të jetë 0.0(0011) në formatin binar), numrat me saktësi të ndryshme do të ndryshojnë, dhe kjo është e vetmja arsye pse ata nuk janë të barabartë. Në përgjithësi, ky është një temë për një artikull të veçantë, nuk do të shkruaj më shumë këtu.
Nga vjen gabimi?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Gabim **********
ERROR: syntax error at or near "("
SQL-state: 42601
Simbol: 24Shumë njerëz e dinë se PostgreSQL lejon shkrimin funksional të konvertimeve të tipit. Pra, mund të shkruani jo vetëm 1::int, por edhe int(1), që do të jetë ekuivalente. Por jo për tipet, emri i të cilave përbëhet nga disa fjalë! Prandaj, nëse dëshironi të konvertoni një vlerë numerike në tipin double precision në formën funksionale, përdorni alias-in e këtij tipi float8, që do të thotë SELECT float8(1).
Çfarë është më shumë se pafundësia?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tJa ku e kemi! Duket se ekziston diçka më e madhe se pafundësia, dhe kjo është NaN! Ndërkohë, dokumentacioni i PostgreSQL na shikon me sy të sinqertë dhe pretendon se NaN është gjithmonë më i madh se çdo numër tjetër, dhe përpasojë, edhe se pafundësia. Kjo është e vërtetë edhe për -NaN. Përshëndetje, dashamirës të analizës matematikore! Por duhet të mbani mend se gjithçka kjo vlen në kontekstin e numrave realë.
Rrethimi i syve
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Një përshëndetje tjetër befasuese nga baza. Dhe sërish duhet të mbani mend se për tipet double precision dhe numeric zbatojnë rrethime të ndryshme. Për numeric — rrethim i zakonshëm, kur 0,5 rrethohen në qendër, ndërsa për double precision — rrethimi i 0,5 ndodh në drejtimin e numrit më të afërt çift.
Paratë janë diçka e veçantë
SELECT '10'::money::float8
ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
^
********** Gabim **********
ERROR: cannot cast type money to double precision
SQL-shteti: 42846
Simbol: 19Sipas PostgreSQL, paratë nuk janë numra realë. Sipas disa individëve, ashtu është. Ne duhet të mbajmë mend se konvertimi i tipit money është i mundur vetëm në tipin numeric, ashtu si dhe tipit money mund t'i konvertohet vetëm tipit numeric. Tani, me këtë mund të luani si të dëshironi. Por ato nuk do të jenë më ato para.
Smallint dhe gjenerimi i sekuencave
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.
********** Gabim **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-shteti: 42725
Sugjerim: Could not choose a best candidate function. You might need to add explicit type casts.
Simbol: 18PostgreSQL nuk do të thotë të jetë i vogël. Cilat janë ato sekuenca bazuar në smallint? int, jo më pak! Prandaj, kur provohet të ekzekutohet kërkesa e mësipërme, baza përpiqet ta konvertojë smallint në një tip tjetër të numrave të plotë, dhe sheh që mund të ketë disa rregulla konvertimi. Cilin rregull konvertimi të zgjedhë? Kjo nuk mund ta vendosë, dhe prandaj bie me një gabim.
Dosja numër dy. «char»/char/varchar/text
Disa çuditëri janë të pranishme edhe te tipet simbolike. Le të njohim edhe ato.
Çfarë janë këto mashtrime?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | karakter(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fÇfarë tipi është «char», çfarë klouni është ky? Nuk na nevojitet një i tillë… Sepse ai pretendon të jetë një char normal, përveç se është në thonjëza. Ai ndryshon nga char-i normal, që është pa thonjëza, sepse shfaq vetëm bajtin e parë të përfaqësimit string, ndërsa char-i normal shfaq simbolin e parë. Në rastin tonë, simboli i parë është shkronja П, e cila në përfaqësimin unicode merr 2 bajta, çka dëshmohet nga konvertimi i rezultatit në tipin bytea. Ndërsa tipi «char» merr vetëm bajtin e parë të këtij përfaqësimi unicode. Pse na nevojitet ky tip? Dokumentacioni i PostgreSQL thotë se ky është një tip special, përdorur për nevoja të veçanta. Kështu që është e pamundur që ne ta kemi nevojë. Por shikoni në sytë e tij dhe mos gaboni kur e takoni atë me sjelljen e tij të veçantë.
Hapësirat e tepërta. Nga syri, në zemër.
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Shikoni shembullin e dhënë. Unë qëllimisht kam sjellë të gjitha rezultatet në tipin bytea që të ishte qartë se çfarë ka brenda. Ku janë hapësirat e fundit pas konvertimit në tipin varchar(6)? Dokumentacioni thotë shkurt: «Kur vlera e karakterit konvertohet në një tip tjetër simbolik, hapësirat e mbetjeve hidhën». Duhet ta mbani mend këtë. Dhe vini re se nëse konstanta string është në thonjëza dhe konvertohet menjëherë në tipin varchar(6), hapësirat e fundit ruhen. Të tilla janë mrekullitë.
Dosja numër tre. json/jsonb
JSON — një strukturë e veçantë, që jeton jetën e saj. Prandaj entitetet e saj dhe entitetet e PostgreSQL duken paksa ndryshe. Ja disa shembuj.
Johnson & Johnson. Ndihmoni ndryshimin
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fÇështja është se JSON ka entitetin e tij null, i cili nuk është ekuivalenti i NULL në PostgreSQL. Në të njëjtën kohë, objekti JSON mund të ketë një vlerë NULL, prandaj shprehja SELECT null::jsonb IS NULL (këndoni vëmendjen për mungesën e thonjëzave) këtë radhë do të kthejë true.
Një shkronjë e ndryshon gjithçka
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]}Çështja është se json dhe jsonb janë struktura krejtësisht të ndryshme. Në json objekti ruhet ashtu siç është, ndërsa në jsonb ruhet në një strukturë të shënuar dhe të indekzuar. Pikërisht për këtë arsye, në rastin e dytë, vlera e objektit me çelësin 1 u zëvendësua nga [1, 2, 3] në [7, 8, 9], që arriti në strukturë në fund me të njëjtin çelës.
S'ka pirë ujë nga fytyra
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL në përmirësimin e JSONB ndryshon formatimin e numrave dhjetorë, duke i sjellë ato në një formë klasike. Për tipin JSON, kjo nuk ndodh. Pak e çuditshme, por është e drejtë e tij.
Dosja numër katër. data/koha/timestamp
Me llojet e datave/koheve ka disa çuditshmëri. Le të shikojmë në to. Në fillim do të përmend se disa nga veçoritë e sjelljes bëhen të qarta nëse kuptoni mirë thelbin e punës me zona kohore. Por kjo gjithashtu është një temë për një artikull të veçantë.
Nuk kuptohem
SELECT '08-Jan-99'::date
ERROR: vlera e fushës së datës/kohais jashtë range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Ndoshta ju nevojitet një caktim tjetër "datestyle".
********** Gabim **********
ERROR: vlera e fushës së datës/kohais jashtë range: "08-Jan-99"
SQL-statusi: 22008
Këshillë: Ndoshta ju nevojitet një caktim tjetër "datestyle".
Simboli: 8Duket se çfarë ka që s'kuptohet? Por megjithatë, baza nuk kupton se çfarë kemi vendosur në vend të parë — vitin apo ditën? Dhe vendos se është 99 janar 2008, që i shkatërron mendjen. Duke folur, kur jepni data në format tekstual, duhet të kontrolloni me shumë kujdes se sa mirë e ka njohur baza (veçanërisht, analizoni parametrin datestyle me komandën SHOW datestyle), pasi paqartësitë në këtë çështje mund të kostojnë shumë.
Nga je një?
SELECT '04:05 Europa/Moscow'::time
ERROR: sintaks i pavëndosur i pavlefshëm për tipin kohë: "04:05 Europa/Moscow"
LINE 1: SELECT '04:05 Europa/Moscow'::time
^
********** Gabim **********
ERROR: sintaks i pavëndosur i pavlefshëm për tipin kohë: "04:05 Europa/Moscow"
SQL-gjendja: 22007
Simboli: 8Pse baza nuk mund ta kuptojë kohën e caktuar qartë? Sepse për zonën kohore është caktuar jo një shkronjë, por emri i plotë, i cili ka kuptim vetëm në kontekstin e datës, pasi merr parasysh historinë e ndryshimeve të zonave kohore, dhe kjo pa datë nuk funksionon. Po ashtu, formulimi i vetë vargut të kohës ngre pyetje — çfarë ka dashur vërtet të thotë programuesi? Prandaj, këtu gjithçka është logjike, nëse e kupton.
Çfarë s'është në rregull me të?
Imagjinoni një situatë. Në tabelën tuaj ka një fushë me tipin timestamptz. Ju doni ta indeksoni atë. Por kuptoni se ndërtimi i një indeksi mbi këtë fushë nuk është gjithmonë i arsyeshëm për shkak të selektivitetit të tij të lartë (më shumë se gjysma e vlerave të këtij tipi do të jenë unike). Prandaj, ju vendosni të uleni selektivitetin e indeksit, duke e sjellë këtë tip në një datë. Dhe merrni një surprizë:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
ERROR: funksionet në shprehjen e indeksit duhet të jenë të shënuara si IMMUTABLE
********** Gabim **********
ERROR: funksionet në shprehjen e indeksit duhet të jenë të shënuara si IMMUTABLE
SQL-gjendja: 42P17Cila është çështja? Se për t'i kthyer tipin timestamptz në tipin date përdoret vlera e parametrave sistemikë TimeZone, që e bën funksionin e kthimit të tipit të varur nga një parametër të konfiguruar, dmth të ndryshueshëm (volatile). Funksionet e tilla në indeks nuk janë të pranueshme. Në këtë rast, duhet të shprehni qartë se në cilën zonë kohore bëhet kthimi i tipit.
Kur tani nuk është vërtet tani
Ne jemi mësuar se now() kthen datën/kohën aktuale duke marrë parasysh zonën kohore. Por shikoni këto kërkesa:
START TRANSACTION;
SELECT now();
tani
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
tani
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
tani
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
COMMIT;Data/koha kthehen të njëjta pavarësisht nga sa kohë ka kaluar nga kërkesa e mëparshme! Çfarë po ndodh? Epo, now() nuk është koha aktuale, por koha e fillimit të tranzaksionit aktual. Prandaj, brenda tranzaksionit, ajo nuk ndryshon. Çdo kërkesë që ekzekutohet jashtë kornizave të tranzaksionit, përfshihet në një tranzaksion në mënyrë të heshtur, kështu që ne nuk e vërejmë që koha, që jepet nga një kërkesë e thjeshtë SELECT now(); në të vërtetë nuk është aktuale… Nëse dëshironi të merrni kohën aktuale të saktë, duhet të përdorni funksionin clock_timestamp().
Dosja numër pesë. bit
Pak e çuditshme
SELECT '111'::bit(4)
bit
bit(4)
------
1110Nga cilat anë duhet të shtohen bitët në rast të zgjerimit të llojit? Duket se nga e majta. Por vetëm se baza ka një mendim të ndryshëm për këtë çështje. Kini kujdes: në rast të mospërputhjes së numrit të bitëve gjatë konvertimit të llojit, do të merrni diçka krejt tjetër nga sa prisnit. Kjo i referohet si shtimit të bitëve nga e djathta, ashtu edhe shkurtimit të bitëve. Po ashtu nga e djathta…
Dosja numër gjashtë. Arrays
As NULL nuk u shfaq
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Si njerëz normalë, të edukuar në SQL, ne presim që rezultati i këtij shprehjeje të jetë NULL. Por nuk ndodhi kështu. Kthehet një array. Pse? Sepse në këtë rast baza e konverton NULL në një array çelësash dhe thirr një funksion array_cat në mënyrë të heshtur. Por megjithatë mbetet e paqartë pse ky "macë array" nuk e nulifikon array-in. Ky sjellje gjithashtu duhet thjesht të mbajmë mend.
Të bëjmë një përmbledhje. Ka mjaft çudira. Shumica prej tyre, sigurisht, nuk janë aq kritike sa të flasim për sjellje skandaloze. Disa të tjera shpjegohen nga lehtësia e përdorimit ose frekuenca e përdorimit të tyre në situata të caktuara. Por megjithatë ka shumë befasi. Prandaj, duhet t'i dini ato. Nëse gjeni diçka tjetër të çuditshme ose të pazakontë në sjelljen e llojeve të caktuar, shkruani në komentet, me kënaqësi do ta kompletoj listën ekzistuese.
Burimi: habr.com
