🥇Iintlobo ezikrokrisayo

Akukho nto ikrokrisayo ngenkangeleko yabo. Ngaphezu koko, babonakala beqhelekile kuwe kwaye ixesha elide. Kodwa loo nto kuphela de uzihlole. Kulapho babonisa khona ubume babo obufihlakeleyo, besebenza ngokwahlukileyo ngokupheleleyo kunokuba ubulindele. Kwaye ngamanye amaxesha benza into eyenza iinwele zakho zime esiphelweni - umzekelo, balahlekelwa idatha eyimfihlo ephathiswe kubo. Xa ujongene nabo, bathi abazani, nangona emthunzini basebenza nzima phantsi kwehood enye. Lixesha lokuba ekugqibeleni ubazise kumanzi acocekileyo. Kwakhona masijongane nezi ntlobo zikrokrisayo.

Ukuchwetheza idatha kwi-PostgreSQL, kuyo yonke ingqiqo yayo, ngamanye amaxesha ibonisa izinto ezimangalisayo ezingaqhelekanga. Kweli nqaku siza kuzama ukucacisa ezinye ze-quirks zabo, siqonde isizathu sokuziphatha kwabo okungaqhelekanga kwaye siqonde indlela yokungaqhubeki kwiingxaki ekusebenzeni kwansuku zonke. Ukuthetha inyani, ndiqulunqe eli nqaku kwakhona njengohlobo lwencwadi yesalathiso kum, incwadi yereferensi enokubhekiswa kuyo ngokulula kwiimeko eziphikisanayo. Ke, iya kwenziwa kwakhona njengoko izinto ezintsha ezimangalisayo ezivela kwiintlobo ezikrokrelekayo zifunyanwa. Ke, masihambe, oh bangadinwa abalandeleli bedatabase!

Inombolo enye. real/double precision/numeric/imali

Kungabonakala ngathi iindidi zamanani zezona zineengxaki ezincinci malunga nezinto ezimangalisayo ekuziphatheni. Kodwa nokuba kunjani na. Ngoko masiqale ngabo. Ngoko...

Ulibele ukubala

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

Yintoni ingxaki? Ingxaki kukuba i-PostgreSQL iguqula i-0.1 engabhalwanga ukuba ichaneke kabini kwaye izama ukuyithelekisa kunye ne-0.1 yohlobo lokwenyani. Kwaye ezi ziintsingiselo ezahlukeneyo ngokupheleleyo! Umbono kukumela amanani okwenene kwimemori yomatshini. Kuba u-0.1 akanakubonakaliswa njengeqhezu lokubini elinomda (iyakuba ngu-0.0(0011) kubini), amanani anobunzulu obuhlukeneyo ayakuhluka, kungoko isiphumo sokuba angalingani. Ngokubanzi, esi sisihloko senqaku elahlukileyo; Andizukubhala ngeenkcukacha ezithe vetshe apha.

Ivela phi impazamo?

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

Abantu abaninzi bayazi ukuba iPostgreSQL ivumela ubhalo olusebenzayo lohlobo lokuphosa. Oko kukuthi, awukwazi ukubhala kuphela i-1::int, kodwa kunye ne-int(1), eya kufana. Kodwa hayi kwiintlobo ezinamagama aliqela! Ngoko ke, ukuba ufuna ukuphosa ixabiso lamanani kudidi oluchanekileyo oluphindwe kabini kwifomu esebenzayo, sebenzisa isibizo solu hlobo lwe-float8, oko kukuthi, KHETHA ukudada8(1).

Yintoni enkulu kune-infinity?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

Jonga ukuba injani! Kuyavela ukuba kukho into enkulu kune-infinity, kwaye yi-NaN! Kwangaxeshanye, amaxwebhu e-PostgreSQL ajonge kuthi ngamehlo anyanisekileyo kwaye amabango ukuba i-NaN ngokucacileyo inkulu kunalo naliphi na elinye inani, kwaye, ke, elingenasiphelo. Okuchaseneyo kuyinyaniso nakwi -NaN. Molweni, bathandi bezibalo! Kodwa kufuneka sikhumbule ukuba konke oku kusebenza kumxholo wamanani okwenene.

Ukujikeleza kwamehlo

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

Omnye umbuliso ongalindelekanga ovela kwisiseko. Kwakhona, khumbula ukuba ukuchaneka okuphindwe kabini kunye neendidi zamanani zineempembelelo ezahlukeneyo zokurhangqa. Kwinani - eliqhelekileyo, xa u-0,5 esondezwe, kwaye ngokuchaneka kabini - u-0,5 usondezwa kwinani elikufutshane nelipheleleyo.

Imali yinto ekhethekileyo

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

Ngokutsho kwePostgreSQL, imali ayilona nani lokwenyani. Ngokutsho kwabanye abantu, kwakhona. Kufuneka sikhumbule ukuba ukuphosa uhlobo lwemali kunokwenzeka kuphela kudidi lwamanani, kanye njengokuba luhlobo lwamanani kuphela olunokuphoswa kuhlobo lwemali. Kodwa ngoku ungadlala ngayo njengoko inqwenela intliziyo yakho. Kodwa ayiyi kuba yimali efanayo.

Isizukulwana esincinci kunye nokulandelelana

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

I-PostgreSQL ayikuthandi ukuchitha ixesha kwizinto ezincinci. Luluphi olu landelelwano lusekwe kwintwana encinane? int, hayi ngaphantsi! Ngoko ke, xa uzama ukuphumeza lo mbuzo ungentla, idatabase izama ukuphosa intwana kolunye uhlobo olupheleleyo, kwaye ibona ukuba kusenokubakho iicasts ezininzi ezinjalo. Yeyiphi icast onokuyikhetha? Akanakuthatha isigqibo malunga noku, kwaye ngenxa yoko ungqubana nempazamo.

Inombolo yefayile yesibini. "char"/char/varchar/text

Iqela lezinto ezingaqhelekanga zikwakhona kwiindidi zabalinganiswa. Masibazi nabo.

Ngamaqhinga anjani la?

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

Loluphi uhlobo lwe "char" olu, luhlobo luni lomhlekisi? Asiyidingi ezo ... Kuba izenza i-char eqhelekileyo, nangona isicatshulwa. Kwaye iyahluka kwi-char eqhelekileyo, engenazo izicaphulo, kuba ivelisa kuphela i-byte yokuqala yokumelwa komtya, ngelixa i-char eqhelekileyo ivelisa uphawu lokuqala. Kwimeko yethu, umlingiswa wokuqala unobumba P, ekubonakalisweni kwe-unicode kuthatha i-2 bytes, njengoko kubonakaliswa ngokuguqula umphumo kuhlobo lwe-bytea. Kwaye uhlobo lwe "char" luthatha kuphela i-byte yokuqala yalo mboniso we-unicode. Ngoko kutheni olu hlobo lufuneka? Amaxwebhu ePostgreSQL athi olu luhlobo olulodwa olusetyenziselwa iimfuno ezizodwa. Ngoko akunakwenzeka ukuba siyifune. Kodwa jonga emehlweni akhe kwaye awuyi kuphosisa xa udibana naye ngokuziphatha kwakhe okukhethekileyo.

Izithuba ezongezelelweyo. Xa ungamboni akekho sengqondweni

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

Jonga kumzekelo onikiweyo. Ndiguqule ngokukhethekileyo zonke iziphumo kuhlobo lwe-bytea, ukuze ibonakale ngokucacileyo into ekhoyo. Ziphi izithuba zokulandela emva kokuphosa kwi-varchar(6)? Amaxwebhu achaza ngokucacileyo: "Xa uphosa ixabiso lomlinganiswa kolunye uhlobo lomlinganiswa, isithuba esimhlophe esilandelayo siyalahlwa." Oku kungathandi kufuneka kukhunjulwe. Kwaye qaphela ukuba ukuba umtya ocatshulweyo ongatshintshiyo uphoswe ngokuthe ngqo ukuchwetheza i-varchar(6), izithuba zokulandela ziyagcinwa. Injalo ke imimangaliso.

Inombolo yefayile yesithathu. json/jsonb

I-JSON sisakhiwo esahlukileyo esiphila ubomi baso. Ke ngoko, amaziko ayo kunye nalawo ePostgreSQL ahluke kancinci. Nantsi imizekelo.

Johnson kunye noJohnson. yiva umahluko

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

Into kukuba i-JSON ine-null entity yayo, engeyiyo i-analogue ye-NULL kwi-PostgreSQL. Kwangaxeshanye, into ye-JSON ngokwayo inokuba nexabiso NULL, ngoko ke intetho ethi KHETHA null::jsonb IS NULL (qaphela ukungabikho kocaphulo olunye) izakubuyela eyinyani ngeli xesha.

Unobumba omnye utshintsha yonke into

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]}

Into kukuba i-json kunye ne-jsonb zizakhiwo ezahlukeneyo ngokupheleleyo. Kwi-json, into igcinwa njengoko injalo, kwaye kwi-jsonb sele igcinwe kwimo yolwahlulo olucazululiweyo, olunesalathisi. Kungenxa yoko kwimeko yesibini, ixabiso lento ngesitshixo se-1 latshintshwa ukusuka kwi- [1, 2, 3] ukuya ku- [7, 8, 9], eyangena kwisakhiwo ekupheleni kanye nesitshixo esifanayo.

Musa ukusela manzi ebusweni bakho

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

I-PostgreSQL ekuphunyezweni kwayo kwe-JSONB itshintsha ukufomathwa kwamanani okwenene, ukuwazisa kwifom yeklasiki. Oku akwenzeki kuhlobo lwe-JSON. Into engaqhelekanga, kodwa unyanisile.

Inombolo yefayile yesine. umhla/ixesha/isitampu sexesha

Kukwakho nezinye izinto ezingaqhelekanga ngeentlobo zomhla/zexesha. Masizijonge. Mandenze ugcino ngoko nangoko ukuba ezinye zeempawu zokuziphatha zicaca ukuba uyayiqonda kakuhle isiseko sokusebenza kunye nemimandla yexesha. Kodwa oku kukwangumxholo wenqaku elahlukileyo.

Eyam eyakho ayiqondi

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

Kubonakala ngathi yintoni engaqondakaliyo apha? Kodwa i-database ayikaqondi into esiyibeke kwindawo yokuqala apha-unyaka okanye usuku? Kwaye uthatha isigqibo sokuba nguJanuwari 99, 2008, into ephazamisa ingqondo yakhe. Ngokubanzi, xa uhambisa imihla kwifomati yokubhaliweyo, kufuneka ujonge ngononophelo olukhulu ukuba idatabase iyazinaka njani (ingakumbi, hlalutya iparameter yedatestyle ngomyalelo we-SHOW datestyle), kuba iintsingiselo-mbini kulo mba zinokubiza kakhulu.

Uyithathaphi lento?

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

Kutheni uvimba weenkcukacha ungaliqondi ixesha elichazwe ngokucacileyo? Ngenxa yokuba indawo yexesha ayinalo isifinyezo, kodwa igama elipheleleyo, elinengqiqo kuphela kumxholo womhla, ekubeni ithathela ingqalelo imbali yokutshintsha kwendawo yexesha, kwaye ayisebenzi ngaphandle komhla. Kwaye kwanamagama abhalwe kumgca wexesha aphakamisa imibuzo-ubethetha ukuthini ngokwenene umdwelisi wenkqubo? Ngoko ke, yonke into isengqiqweni apha, ukuba uyayijonga.

Kwenzeka ntoni kuye?

Khawube nomfanekiso-ngqondweni wemeko. Unebala kwitafile yakho enohlobo lwetimestamptz. Ufuna ukuyichaza. Kodwa uyaqonda ukuba ukwakha isalathisi kule ntsimi akusoloko kufanelekile ngenxa yokukhethwa kwayo okuphezulu (phantse onke amaxabiso alolu hlobo aya kuba yedwa). Ngoko uthatha isigqibo sokunciphisa ukukhethwa kwesalathisi ngokuphosa uhlobo kumhla. Kwaye ufumana into emangalisayo:

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

Yintoni ingxaki? Inyani kukuba ukuphosa uhlobo lwe-timestamptz kuhlobo lomhla, ixabiso le-TimeZone system parameter lisetyenziswa, elenza ukuba uhlobo lokuguqulwa komsebenzi luxhomekeke kwipharamitha yesiko, okt. eguquguqukayo. Imisebenzi enjalo ayivumelekanga kwisalathiso. Kule meko, kufuneka ubonise ngokucacileyo ukuba yeyiphi indawo yexesha apho uhlobo lwenziwa khona.

Xa ngoku hayi nangoku kwaphela

Siqhele uku() ukubuyisela umhla/ixesha langoku, sithathela ingqalelo indawo yexesha. Kodwa jonga le mibuzo ilandelayo:

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;

Umhla/ixesha libuyiswa ngokufanayo nokuba lingakanani ixesha eligqithileyo ukususela kwisicelo sangaphambili! Yintoni ingxaki? Inyani kukuba ngoku () ayiloxesha langoku, kodwa lixesha lokuqala lentengiselwano yangoku. Ngoko ke, ayitshintshi ngaphakathi kwentengiselwano. Nawuphi na umbuzo oqaliswe ngaphandle komda wentengiselwano usongelwe kwintengiselwano ngokungafihlisiyo, yiyo loo nto singaqapheli ukuba ixesha elibuyiswe ngokulula KHETHA ngoku (); eneneni, hayi ekhoyo... Ukuba ufuna ukufumana ixesha langoku elinyanisekileyo, kufuneka usebenzise i-clock_timestamp() umsebenzi.

Inombolo yefayile yesihlanu. kancinci

Umnqa kancinci

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

Leliphi icala ekufuneka longezwe amasuntswana kwimeko yokwandiswa kohlobo? Kubonakala ngathi ingasekhohlo. Kodwa isiseko kuphela esinoluvo olwahlukileyo kulo mbandela. Lumka: ukuba inani lamadijithi alihambelani xa usenza uhlobo, awuzukufumana le ubukufuna. Oku kusebenza kuzo zombini ukongeza amasuntswana ngasekunene kunye namasuntswana okunciphisa. Kwakhona ngasekunene...

Inombolo yefayile yesithandathu. Uluhlu

Nkqu uNULL akazange adubule

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

Njengabantu abaqhelekileyo abaphakanyiswe kwi-SQL, silindele ukuba umphumo wale ntetho ube NULL. Kodwa yayingekho. Kubuyiswe uluhlu. Ngoba? Kuba kulo mzekelo isiseko sakha NULL kuluhlu olupheleleyo kwaye ngokufihlakeleyo lubiza umsebenzi we-array_cat. Kodwa akukacaci ukuba kutheni le "kati yekati" ingaluseti ngokutsha uluhlu. Oku kuziphatha nako kufuna nje ukukhunjulwa.

Shwankathela. Zininzi izinto ezingaqhelekanga. Uninzi lwazo, ngokuqinisekileyo, alubalulekanga kangako ukuba luthethe ngokuziphatha okungafanelekanga ngokuphandle. Kwaye ezinye zicaciswa ngokulula kokusetyenziswa okanye ukuphindaphinda kokusetyenziswa kwazo kwiimeko ezithile. Kodwa kwangaxeshanye, kukho izinto ezininzi ezimangalisayo. Ngoko ke, kufuneka ukwazi ngabo. Ukuba ufumana enye into engaqhelekanga okanye engaqhelekanga ekuziphatheni kwazo naziphi na iindidi, bhala kwiinkcazo, ndiya kuvuya ukongeza kwiidosi ezikhoyo kuzo.

umthombo: www.habr.com

Iintlobo ezikrokrisayo