ViÅu izskatÄ nav nekÄ aizdomÄ«ga. TurklÄt tie jums pat Ŕķiet labi un ilgi pazÄ«stami. Bet tas ir tikai lÄ«dz brÄ«dim, kad tos pÄrbaudÄ«sit. Å eit viÅi parÄda savu mÄnÄ«go raksturu, strÄdÄjot pilnÄ«gi savÄdÄk, nekÄ jÅ«s gaidÄ«jÄt. Un dažreiz viÅi dara kaut ko tÄdu, kas liek jums mati stÄvÄt stÄvus - piemÄram, viÅi pazaudÄ viÅiem uzticÄtos slepenos datus. Kad jÅ«s saskaraties ar viÅiem, viÅi apgalvo, ka viÅi viens otru nepazÄ«st, lai gan ÄnÄ viÅi smagi strÄdÄ zem viena pÄrsega. Ir pienÄcis laiks beidzot nogÄdÄt tos tÄ«rÄ Å«denÄ«. Ä»aujiet mums arÄ« tikt galÄ ar Å”iem aizdomÄ«gajiem tipiem.
Datu ierakstÄ«Å”ana programmÄ PostgreSQL, neskatoties uz visu tÄs loÄ£iku, dažreiz sagÄdÄ Ä¼oti dÄ«vainus pÄrsteigumus. Å ajÄ rakstÄ mÄs centÄ«simies noskaidrot dažas viÅu dÄ«vainÄ«bas, izprast viÅu dÄ«vainÄs uzvedÄ«bas iemeslus un saprast, kÄ ikdienas praksÄ nesastapties ar problÄmÄm. TaisnÄ«bu sakot, es Å”o rakstu sastÄdÄ«ju arÄ« kÄ sava veida uzziÅu grÄmatu sev, uzziÅu grÄmatu, uz kuru var viegli atsaukties strÄ«dÄ«gos gadÄ«jumos. TÄpÄc tas tiks papildinÄts, jo tiks atklÄti jauni pÄrsteigumi no aizdomÄ«giem tipiem. TÄtad, ejam, ak, nenogurstoÅ”ie datu bÄzes izsekotÄji!
DokumentÄcija numur viens. reÄla/dubultÄ precizitÄte/ciparu/nauda
Å Ä·iet, ka skaitļu tipi ir vismazÄk problemÄtiski attiecÄ«bÄ uz pÄrsteigumiem uzvedÄ«bÄ. Bet neatkarÄ«gi no tÄ, kÄ tas ir. TÄpÄc sÄksim ar viÅiem. TÄtadā¦
Aizmirsu, kÄ skaitÄ«t
SELECT 0.1::real = 0.1
?column?
boolean
---------
f
Kas noticis? ProblÄma ir tÄda, ka PostgreSQL pÄrveido neierakstÄ«to konstanti 0.1 ar dubultu precizitÄti un mÄÄ£ina to salÄ«dzinÄt ar reÄlÄ tipa 0.1. Un tÄs ir pilnÄ«gi atŔķirÄ«gas nozÄ«mes! Ideja ir attÄlot reÄlus skaitļus maŔīnas atmiÅÄ. TÄ kÄ 0.1 nevar attÄlot kÄ galÄ«gu binÄru daļu (binÄrÄ tÄ bÅ«tu 0.0(0011), skaitļi ar dažÄdiem cipariem bÅ«s atŔķirÄ«gi, tÄpÄc tie nav vienÄdi. VispÄrÄ«gi runÄjot, Ŕī ir atseviŔķa raksta tÄma, es Å”eit nerakstÄ«Å”u sÄ«kÄk.
No kurienes rodas kļūda?
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
Daudzi cilvÄki zina, ka PostgreSQL pieļauj funkcionÄlu apzÄ«mÄjumu tipu lieÅ”anai. Tas ir, jÅ«s varat rakstÄ«t ne tikai 1::int, bet arÄ« int(1), kas bÅ«s lÄ«dzvÄrtÄ«gs. Bet ne tipiem, kuru nosaukumi sastÄv no vairÄkiem vÄrdiem! TÄpÄc, ja vÄlaties funkcionÄlÄ formÄ nodot skaitlisko vÄrtÄ«bu dubultÄs precizitÄtes tipam, izmantojiet Ŕī tipa aizstÄjvÄrdu float8, tas ir, SELECT float8(1).
Kas ir lielÄks par bezgalÄ«bu?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
t
Paskaties, kÄ tas ir! IzrÄdÄs, ka ir kaut kas lielÄks par bezgalÄ«bu, un tas ir NaN! TajÄ paÅ”Ä laikÄ PostgreSQL dokumentÄcija skatÄs uz mums ar godÄ«gÄm acÄ«m un apgalvo, ka NaN ir acÄ«mredzami lielÄks par jebkuru citu skaitli un lÄ«dz ar to bezgalÄ«bu. PretÄji ir arÄ« -NaN. Sveiki, matemÄtikas cienÄ«tÄji! Bet mums jÄatceras, ka tas viss darbojas reÄlo skaitļu kontekstÄ.
Acu noapaļoŔana
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3
VÄl viens negaidÄ«ts sveiciens no bÄzes. Atkal atcerieties, ka dubultÄs precizitÄtes un skaitļu veidiem ir dažÄdi noapaļoÅ”anas efekti. Skaitļiem - parastais veids, kad 0,5 tiek noapaļots uz augÅ”u, un dubultai precizitÄtei - 0,5 tiek noapaļots uz tuvÄko pÄra veselo skaitli.
Nauda ir kaut kas īpaŔs
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
SaskaÅÄ ar PostgreSQL, nauda nav reÄls skaitlis. PÄc dažu cilvÄku domÄm, arÄ«. JÄatceras, ka naudas veidu var nodot tikai skaitļu veidam, tÄpat kÄ naudas veidu var nodot tikai ciparu tipam. Bet tagad ar to var spÄlÄties, kÄ sirds kÄro. Bet tÄ nebÅ«s tÄ pati nauda.
Smallint un secÄ«bu Ä£enerÄÅ”ana
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 nepatÄ«k tÄrÄt laiku sÄ«kumiem. KÄdas ir Ŕīs secÄ«bas, kuru pamatÄ ir Smallint? int, ne mazÄk! TÄpÄc, mÄÄ£inot izpildÄ«t iepriekÅ” minÄto vaicÄjumu, datu bÄze mÄÄ£ina nodot smallint uz kÄdu citu veselu skaitļu tipu un redz, ka var bÅ«t vairÄki Å”Ädi skaitļi. Kuru cast izvÄlÄties? ViÅa nevar to izlemt, un tÄpÄc avarÄ kļūdas dÄļ.
Faila numurs divi. "char"/char/varchar/text
RakstzÄ«mju tipos ir arÄ« vairÄkas dÄ«vainÄ«bas. IepazÄ«sim arÄ« viÅus.
KÄdi ir Å”ie triki?
SELECT 'ŠŠŠ¢ŠÆ'::"char"
, 'ŠŠŠ¢ŠÆ'::"char"::bytea
, 'ŠŠŠ¢ŠÆ'::char
, 'ŠŠŠ¢ŠÆ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
āØ | xd0 | Š | xd09f
Kas tas par "Äakaru", kas tas par klaunu? Mums tie nav vajadzÄ«gi... Jo tas izliekas par parastu zÄ«muli, lai gan tas ir pÄdiÅÄs. Un tas atŔķiras no parastÄs rakstzÄ«mes, kas ir bez pÄdiÅÄm, ar to, ka tÄ izvada tikai virknes attÄlojuma pirmo baitu, bet parasta rakstzÄ«me izvada pirmo rakstzÄ«mi. MÅ«su gadÄ«jumÄ pirmÄ rakstzÄ«me ir burts P, kas unikoda attÄlojumÄ aizÅem 2 baitus, par ko liecina rezultÄta pÄrvÄrÅ”ana par baitu tipu. Un ācharā tips aizÅem tikai pirmo Ŕī unikoda attÄlojuma baitu. Tad kÄpÄc Å”is tips ir vajadzÄ«gs? PostgreSQL dokumentÄcijÄ teikts, ka Å”is ir Ä«paÅ”s veids, ko izmanto Ä«paÅ”Äm vajadzÄ«bÄm. TÄpÄc diez vai mums tas bÅ«s vajadzÄ«gs. Bet paskaties viÅam acÄ«s, un tu nekļūdÄ«sies, kad satiksi viÅu ar viÅa Ä«paÅ”o uzvedÄ«bu.
Papildu vietas. No redzesloka, no prÄta
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020
Apskatiet sniegto piemÄru. Es speciÄli konvertÄju visus rezultÄtus uz baitu tipu, lai bÅ«tu skaidri redzams, kas tur ir. Kur ir beigu atstarpes pÄc atlaiÅ”anas varchar(6)? DokumentÄcijÄ Ä«si teikts: "Izmantojot rakstzÄ«mes vÄrtÄ«bu citam rakstzÄ«mju veidam, beigu atstarpes tiek izmestas." Å Ä« nepatika ir jÄatceras. Un Åemiet vÄrÄ, ka, ja pÄdiÅÄs norÄdÄ«tÄ virknes konstante tiek nodota tieÅ”i tipam varchar (6), beigu atstarpes tiek saglabÄtas. TÄdi ir brÄ«numi.
Faila numurs trīs. json/jsonb
JSON ir atseviŔķa struktÅ«ra, kas dzÄ«vo savu dzÄ«vi. TÄpÄc tÄs entÄ«tijas un PostgreSQL entÄ«tijas nedaudz atŔķiras. Å eit ir piemÄri.
Džonsons un Džonsons. sajust atŔķirību
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
f
Lieta ir tÄda, ka JSON ir sava nulles entÄ«tija, kas nav NULL analogs programmÄ PostgreSQL. TajÄ paÅ”Ä laikÄ paÅ”am JSON objektam var bÅ«t vÄrtÄ«ba NULL, tÄpÄc izteiksme SELECT null::jsonb IS NULL (Åemiet vÄrÄ, ka nav vienpÄdiÅu) Å”oreiz atgriezÄ«sies patiesa.
Viens burts maina visu
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]}
Lieta ir tÄda, ka json un jsonb ir pilnÄ«gi atŔķirÄ«gas struktÅ«ras. Json versijÄ objekts tiek saglabÄts tÄds, kÄds tas ir, un jsonb tas jau ir saglabÄts parsÄtas, indeksÄtas struktÅ«ras veidÄ. TÄpÄc otrajÄ gadÄ«jumÄ objekta vÄrtÄ«ba ar atslÄgu 1 tika aizstÄta no [1, 2, 3] uz [7, 8, 9], kas struktÅ«rÄ nonÄca paÅ”Äs beigÄs ar to paÅ”u atslÄgu.
Nedzeriet Å«deni no sejas
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}
PostgreSQL savÄ JSONB ievieÅ”anÄ maina reÄlo skaitļu formatÄjumu, ievieÅ”ot tos klasiskajÄ formÄ. Tas nenotiek JSON tipam. Nedaudz dÄ«vaini, bet viÅam ir taisnÄ«ba.
Faila numurs Äetri. datums/laiks/laika zÄ«mogs
Ir arÄ« dažas dÄ«vainÄ«bas ar datuma/laika veidiem. ApskatÄ«sim tos. Ä»aujiet man uzreiz izdarÄ«t atrunu, ka dažas uzvedÄ«bas pazÄ«mes kļūst skaidras, ja labi saprotat darba ar laika joslÄm bÅ«tÄ«bu. Bet Ŕī ir arÄ« atseviŔķa raksta tÄma.
ManÄjais nesaprot
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
Å Ä·iet, kas gan te nesaprotams? Bet datubÄze joprojÄm nesaprot, ko mÄs Å”eit ieliekam pirmajÄ vietÄ ā gadu vai dienu? Un viÅa nolemj, ka ir 99. gada 2008. janvÄris, kas viÅai satriec prÄtu. VispÄrÄ«gi runÄjot, pÄrsÅ«tot datumus teksta formÄtÄ, jums ļoti rÅ«pÄ«gi jÄpÄrbauda, āācik pareizi datu bÄze tos atpazina (jo Ä«paÅ”i analizÄjiet parametru datestyle ar komandu SHOW datestyle), jo neskaidrÄ«bas Å”ajÄ jautÄjumÄ var bÅ«t ļoti dÄrgas.
No kurienes tu to dabūji?
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
KÄpÄc datu bÄze nevar saprast skaidri norÄdÄ«to laiku? Jo laika joslai ir nevis saÄ«sinÄjums, bet pilns nosaukums, kam ir jÄga tikai datuma kontekstÄ, jo tiek Åemta vÄrÄ laika joslu izmaiÅu vÄsture un bez datuma tas nedarbojas. Un jau pats laika lÄ«nijas formulÄjums rada jautÄjumus ā ko Ä«sti programmÄtÄjs domÄja? TÄpÄc Å”eit viss ir loÄ£iski, ja paskatÄs.
Kas viÅam kaiÅ”?
IedomÄjieties situÄciju. TabulÄ ir lauks ar veidu timestampz. JÅ«s vÄlaties to indeksÄt. Bet jÅ«s saprotat, ka indeksa veidoÅ”ana Å”ajÄ laukÄ ne vienmÄr ir pamatota tÄ augstÄs selektivitÄtes dÄļ (gandrÄ«z visas Å”Äda veida vÄrtÄ«bas bÅ«s unikÄlas). TÄpÄc jÅ«s nolemjat samazinÄt indeksa selektivitÄti, atlasot veidu uz datumu. Un jÅ«s saÅemat pÄrsteigumu:
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
Kas noticis? Fakts ir tÄds, ka, lai datuma tipam nodotu laika zÄ«moga veidu, tiek izmantota TimeZone sistÄmas parametra vÄrtÄ«ba, kas padara tipa konvertÄÅ”anas funkciju atkarÄ«gu no pielÄgota parametra, t.i. nepastÄvÄ«gs. Å Ädas funkcijas indeksÄ nav atļautas. Å ajÄ gadÄ«jumÄ jums ir skaidri jÄnorÄda, kurÄ laika joslÄ tiek veikta tipa apraide.
Kad tagad nemaz nav pat tagad
MÄs esam pieraduÅ”i tagad() atgriezt paÅ”reizÄjo datumu/laiku, Åemot vÄrÄ laika joslu. Bet apskatiet Å”Ädus jautÄjumus:
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;
Datums/laiks tiek atgriezts vienÄds neatkarÄ«gi no tÄ, cik daudz laika ir pagÄjis kopÅ” iepriekÅ”ÄjÄ pieprasÄ«juma! Kas noticis? Fakts ir tÄds, ka tagad() nav paÅ”reizÄjais laiks, bet gan paÅ”reizÄjÄ darÄ«juma sÄkuma laiks. LÄ«dz ar to darÄ«juma ietvaros tas nemainÄs. JebkurÅ” vaicÄjums, kas tiek palaists Ärpus darÄ«juma darbÄ«bas jomas, tiek iekļauts transakcijÄ netieÅ”i, tÄpÄc mÄs nepamanÄm, ka laiks tiek atgriezts ar vienkÄrÅ”u SELECT now(); patiesÄ«bÄ, nevis paÅ”reizÄjo... Ja vÄlaties iegÅ«t godÄ«gu paÅ”reizÄjo laiku, jums ir jÄizmanto funkcija clock_timestamp().
Faila numurs pieci. mazliet
Nedaudz dīvaini
SELECT '111'::bit(4)
bit
bit(4)
------
1110
KurÄ pusÄ jÄpievieno biti tipa paplaÅ”inÄjuma gadÄ«jumÄ? Å Ä·iet, ka tas ir kreisajÄ pusÄ. Bet tikai bÄzei Å”ajÄ jautÄjumÄ ir atŔķirÄ«gs viedoklis. Esiet piesardzÄ«gs: ja ciparu skaits nesakrÄ«t, nododot veidu, jÅ«s nesaÅemsit to, ko gribÄjÄt. Tas attiecas gan uz bitu pievienoÅ”anu labajÄ pusÄ, gan uz bitu apgrieÅ”anu. ArÄ« pa labi...
Faila numurs seŔi. Masīvi
Pat NULL neizÅ”Äva
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}
KÄ parasti cilvÄki, kuri izmanto SQL, mÄs sagaidÄm, ka Ŕīs izteiksmes rezultÄts bÅ«s NULL. Bet tÄ tur nebija. Tiek atgriezts masÄ«vs. KÄpÄc? TÄ kÄ Å”ajÄ gadÄ«jumÄ bÄze izdala NULL uz veselu skaitļu masÄ«vu un netieÅ”i izsauc funkciju array_cat. TaÄu joprojÄm nav skaidrs, kÄpÄc Å”is āmasÄ«va kaÄ·isā neatiestata masÄ«vu. ArÄ« Ŕī uzvedÄ«ba ir vienkÄrÅ”i jÄatceras.
Apkopojiet. Ir daudz dÄ«vainu lietu. LielÄkÄ daļa no viÅiem, protams, nav tik kritiski, lai runÄtu par klaji nepiedienÄ«gu uzvedÄ«bu. Un citi ir izskaidrojami ar lietoÅ”anas vienkÄrŔību vai to pielietoÅ”anas biežumu noteiktÄs situÄcijÄs. Bet tajÄ paÅ”Ä laikÄ ir daudz pÄrsteigumu. TÄpÄc jums par tiem jÄzina. Ja kÄda veida uzvedÄ«bÄ atrodat ko citu dÄ«vainu vai neparastu, rakstiet komentÄros, es ar prieku papildinÄÅ”u par tiem pieejamo dokumentÄciju.
Avots: www.habr.com