Daar is niks verdag aan hul voorkoms nie. Boonop lyk hulle selfs vir jou goed en vir 'n lang tyd bekend. Maar dit is net totdat jy dit nagaan. Dit is waar hulle hul verraderlike aard wys, wat heeltemal anders werk as wat jy verwag het. En soms doen hulle iets wat jou hare laat rys – hulle verloor byvoorbeeld geheime data wat aan hulle toevertrou is. Wanneer jy hulle konfronteer, beweer hulle dat hulle mekaar nie ken nie, hoewel hulle in die skaduwees hard onder dieselfde kap werk. Dit is tyd om hulle uiteindelik na skoon water te bring. Kom ons hanteer ook hierdie verdagte tipes.
Datatik in PostgreSQL, vir al sy logika, bied soms baie vreemde verrassings. In hierdie artikel sal ons probeer om sommige van hul eienaardighede op te klaar, die rede vir hul vreemde gedrag te verstaan en te verstaan hoe om nie probleme in die alledaagse praktyk te ondervind nie. Om die waarheid te sê, het ek hierdie artikel ook saamgestel as 'n soort naslaanboek vir myself, 'n naslaanboek waarna maklik in kontroversiële gevalle verwys kon word. Daarom sal dit aangevul word soos nuwe verrassings van verdagte tipes ontdek word. So, laat ons gaan, o onvermoeide databasisspoorsnyers!
Dossier nommer een. werklike/dubbele akkuraatheid/numeries/geld
Dit wil voorkom asof numeriese tipes die minste problematies is in terme van verrassings in gedrag. Maar maak nie saak hoe dit is nie. So kom ons begin met hulle. So…
Het vergeet hoe om te tel
SELECT 0.1::real = 0.1
?column?
boolean
---------
fWats fout? Die probleem is dat PostgreSQL die ongetikte konstante 0.1 omskakel na dubbele presisie en probeer om dit te vergelyk met 0.1 van werklike tipe. En dit is heeltemal verskillende betekenisse! Die idee is om reële getalle in masjiengeheue voor te stel. Aangesien 0.1 nie as 'n eindige binêre breuk voorgestel kan word nie (dit sal 0.0(0011) in binêre wees), sal getalle met verskillende bisdieptes verskil, vandaar die gevolg dat hulle nie gelyk is nie. Oor die algemeen is dit 'n onderwerp vir 'n aparte artikel, ek sal nie hier in meer besonderhede skryf nie.
Waar kom die fout vandaan?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Baie mense weet dat PostgreSQL funksionele notasie vir tipe casting toelaat. Dit wil sê, jy kan nie net 1::int skryf nie, maar ook int(1), wat ekwivalent sal wees. Maar nie vir tipes wie se name uit verskeie woorde bestaan nie! Daarom, as jy 'n numeriese waarde wil gooi na dubbele presisietipe in funksionele vorm, gebruik die alias van hierdie tipe float8, dit wil sê, SELECT float8(1).
Wat is groter as oneindigheid?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tKyk hoe is dit! Dit blyk daar is iets groter as oneindigheid, en dit is NaN! Terselfdertyd kyk die PostgreSQL-dokumentasie met eerlike oë na ons en beweer dat NaN natuurlik groter is as enige ander getal, en dus oneindig. Die teenoorgestelde is ook waar vir -NaN. Hallo, wiskunde liefhebbers! Maar ons moet onthou dat dit alles in die konteks van reële getalle werk.
Oogronding
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Nog 'n onverwagse groet vanaf die basis. Weereens, onthou dat dubbele presisie en numeriese tipes verskillende afrondingseffekte het. Vir numeries - die gewone een, wanneer 0,5 na bo afgerond word, en vir dubbele akkuraatheid - word 0,5 afgerond na die naaste ewe heelgetal.
Geld is iets besonders
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
Символ: 19Volgens PostgreSQL is geld nie 'n regte getal nie. Volgens sommige individue ook. Ons moet onthou dat om die geldtipe slegs na die numeriese tipe te gooi, net soos slegs die numeriese tipe na die geldtipe gegiet kan word. Maar nou kan jy daarmee speel soos jou hart begeer. Maar dit sal nie dieselfde geld wees nie.
Smallint en volgorde generering
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 hou nie daarvan om tyd op kleinighede te mors nie. Wat is hierdie rye gebaseer op smallint? int, nie minder nie! Daarom, wanneer probeer om die bogenoemde navraag uit te voer, probeer die databasis om smallint na 'n ander heelgetal tipe te gooi, en sien dat daar verskeie sulke casts kan wees. Watter rolverdeling om te kies? Sy kan dit nie besluit nie, en crash daarom met 'n fout.
Lêer nommer twee. "char"/char/varchar/text
'n Aantal eienaardighede kom ook in karaktertipes voor. Kom ons leer hulle ook ken.
Watter soort truuks is dit?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fWatter tipe "char" is dit, watter soort nar is dit? Ons het dit nie nodig nie ... Omdat dit voorgee dat dit 'n gewone char is, al is dit tussen aanhalingstekens. En dit verskil van 'n gewone char, wat sonder aanhalingstekens is, deurdat dit slegs die eerste greep van die stringvoorstelling uitstuur, terwyl 'n normale char die eerste karakter uitvoer. In ons geval is die eerste karakter die letter P, wat in die unicode-voorstelling 2 grepe opneem, soos blyk uit die omskakeling van die resultaat na die grepe-tipe. En die tipe "char" neem slegs die eerste greep van hierdie unicode-voorstelling. Hoekom is hierdie tipe dan nodig? Die PostgreSQL-dokumentasie sê dat dit 'n spesiale tipe is wat vir spesiale behoeftes gebruik word. Dit is dus onwaarskynlik dat ons dit nodig sal hê. Maar kyk in sy oë en jy sal jou nie vergis wanneer jy hom met sy spesiale gedrag ontmoet nie.
Ekstra spasies. Uit die oog, uit die verstand
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Kyk na die voorbeeld wat gegee word. Ek het spesiaal al die resultate na die bytea-tipe omgeskakel sodat dit duidelik sigbaar was wat daar was. Waar is die agterste spasies nadat dit na varchar(6) gegiet is? Die dokumentasie sê bondig: "Wanneer die waarde van karakter na 'n ander karaktertipe oorgedra word, word agterliggende spasie weggegooi." Hierdie afkeer moet onthou word. En let daarop dat as 'n aangehaalde stringkonstante direk na tipe varchar(6) gegiet word, die agterste spasies behoue bly. So is die wonderwerke.
Lêer nommer drie. json/jsonb
JSON is 'n aparte struktuur wat sy eie lewe lei. Daarom verskil sy entiteite en dié van PostgreSQL effens. Hier is voorbeelde.
Johnson en Johnson. voel die verskil
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fDie ding is dat JSON sy eie nul-entiteit het, wat nie die analoog van NULL in PostgreSQL is nie. Terselfdertyd kan die JSON-voorwerp self die waarde NULL hê, so die uitdrukking SELECT null::jsonb IS NULL (let op die afwesigheid van enkele aanhalingstekens) sal hierdie keer waar terugkeer.
Een letter verander alles
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]}Die ding is dat json en jsonb heeltemal verskillende strukture is. In json word die voorwerp gestoor soos dit is, en in jsonb is dit reeds in die vorm van 'n ontleed, geïndekseerde struktuur gestoor. Daarom is in die tweede geval die waarde van die voorwerp deur sleutel 1 vervang van [1, 2, 3] na [7, 8, 9], wat heel aan die einde met dieselfde sleutel in die struktuur gekom het.
Moenie water uit jou gesig drink nie
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL in sy JSONB-implementering verander die formatering van reële getalle, wat hulle na die klassieke vorm bring. Dit gebeur nie vir die JSON-tipe nie. Bietjie vreemd, maar hy is reg.
Lêer nommer vier. datum/tyd/tydstempel
Daar is ook 'n paar eienaardighede met datum/tydtipes. Kom ons kyk na hulle. Laat ek dadelik 'n voorbehoud maak dat sommige van die gedragskenmerke duidelik word as jy die essensie van werk met tydsones goed verstaan. Maar dit is ook 'n onderwerp vir 'n aparte artikel.
My joune verstaan nie
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.
Символ: 8Dit wil voorkom asof wat hier onverstaanbaar is? Maar die databasis verstaan steeds nie wat ons hier in die eerste plek plaas nie - die jaar of die dag? En sy besluit dat dit 99 Januarie 2008 is, wat haar kop blaas. Oor die algemeen, wanneer u datums in teksformaat oordra, moet u baie noukeurig kyk hoe korrek die databasis dit herken het (ontleed veral die datumstylparameter met die SHOW datestyle-opdrag), aangesien onduidelikhede in hierdie saak baie duur kan wees.
Waar het jy dit vandaan gekry?
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
Символ: 8Waarom kan die databasis nie die eksplisiet gespesifiseerde tyd verstaan nie? Omdat die tydsone nie 'n afkorting het nie, maar 'n volle naam, wat slegs in die konteks van 'n datum sin maak, aangesien dit die geskiedenis van tydsoneveranderings in ag neem, en dit werk nie sonder 'n datum nie. En die bewoording van die tydlyn laat vrae ontstaan – wat het die programmeerder werklik bedoel? Daarom is alles hier logies, as jy daarna kyk.
Wat is fout met hom?
Stel jou die situasie voor. Jy het 'n veld in jou tabel met tipe timestamptz. Jy wil dit indekseer. Maar jy verstaan dat die bou van 'n indeks op hierdie veld nie altyd geregverdig is nie as gevolg van die hoë selektiwiteit daarvan (byna alle waardes van hierdie tipe sal uniek wees). U besluit dus om die selektiwiteit van die indeks te verminder deur die tipe na 'n datum te stel. En jy kry 'n verrassing:
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-состояние: 42P17Wats fout? Die feit is dat om 'n timestamptz-tipe na 'n datumtipe te gooi, word die waarde van die TimeZone-stelselparameter gebruik, wat die tipe-omskakelingsfunksie afhanklik maak van 'n pasgemaakte parameter, d.w.s. wisselvallig. Sulke funksies word nie in die indeks toegelaat nie. In hierdie geval moet jy uitdruklik aandui in watter tydsone die tipe cast uitgevoer word.
Wanneer nou glad nie eers nou is nie
Ons is gewoond daaraan om nou() die huidige datum/tyd terug te gee, met inagneming van die tydsone. Maar kyk na die volgende navrae:
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;Die datum/tyd word dieselfde teruggestuur maak nie saak hoeveel tyd verloop het sedert die vorige versoek nie! Wats fout? Die feit is dat now() nie die huidige tyd is nie, maar die begintyd van die huidige transaksie. Daarom verander dit nie binne die transaksie nie. Enige navraag wat buite die bestek van 'n transaksie geloods word, word implisiet in 'n transaksie toegedraai, en daarom merk ons nie op dat die tyd teruggestuur word deur 'n eenvoudige SELECT now(); trouens, nie die huidige een nie... As jy 'n eerlike huidige tyd wil kry, moet jy die clock_timestamp() funksie gebruik.
Lêer nommer vyf. bietjie
Bietjie vreemd
SELECT '111'::bit(4)
bit
bit(4)
------
1110Watter kant moet die stukkies bygevoeg word in geval van tipe uitbreiding? Dit blyk aan die linkerkant te wees. Maar net die basis het 'n ander mening oor hierdie saak. Wees versigtig: as die aantal syfers nie ooreenstem wanneer 'n tipe gegooi word nie, sal jy nie kry wat jy wou hê nie. Dit is van toepassing op beide die byvoeging van stukkies aan die regterkant en snypunte. Ook aan die regterkant...
Lêer nommer ses. Skikkings
Selfs NULL het nie gevuur nie
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}As normale mense wat op SQL grootgemaak is, verwag ons dat die resultaat van hierdie uitdrukking NULL sal wees. Maar dit was nie daar nie. 'n Skikking word teruggestuur. Hoekom? Want in hierdie geval gooi die basis NULL na 'n heelgetalskikking en roep implisiet die array_cat-funksie. Maar dit bly steeds onduidelik waarom hierdie "skikkingskat" nie die skikking terugstel nie. Hierdie gedrag moet ook net onthou word.
Som op. Daar is genoeg vreemde dinge. Die meeste van hulle is natuurlik nie so krities om oor blatante onvanpaste gedrag te praat nie. En ander word verklaar deur gemak van gebruik of die frekwensie van hul toepaslikheid in sekere situasies. Maar terselfdertyd is daar baie verrassings. Daarom moet jy van hulle weet. As jy enigiets anders vreemd of ongewoon in die gedrag van enige tipe vind, skryf in die kommentaar, ek sal met graagte by die dossiere wat daarop beskikbaar is, byvoeg.
Bron: will.com