🥇Izinhlobo ezisolisayo

Akukho okusolisayo ngokubukeka kwabo. Ngaphezu kwalokho, babonakala bejwayelene nawe futhi isikhathi eside. Kodwa lokho kuphela uze uwahlole. Yilapho bekhombisa khona imvelo yabo ekhohlisayo, besebenza ngendlela ehluke ngokuphelele kunalokho obukulindele. Futhi ngezinye izikhathi benza okuthile okwenza izinwele zakho ziqine - isibonelo, balahlekelwa idatha eyimfihlo ephathiswe bona. Uma ubhekana nabo bathi abazani, nakuba ethunzini basebenza kanzima ngaphansi kwesigqoko esifanayo. Sekuyisikhathi sokuba ekugcineni uwalethe emanzini ahlanzekile. Masiphinde sibhekane nalezi zinhlobo ezisolisayo.

Ukuthayipha kwedatha ku-PostgreSQL, kuwo wonke ama-logic ayo, ngezinye izikhathi kuveza izimanga eziyinqaba kakhulu. Kulesi sihloko sizozama ukucacisa amanye ama-quirks abo, siqonde isizathu sokuziphatha kwabo okungavamile futhi siqonde ukuthi ungabhekana kanjani nezinkinga ekusebenzeni kwansuku zonke. Ukukhuluma iqiniso, ngihlanganise lesi sihloko futhi njengohlobo lwencwadi engiyisethenjwa yami, incwadi eyireferensi okungabhekiselwa kuyo kalula ezimeni eziyizingxabano. Ngakho-ke, izokwenziwa kabusha njengoba kutholwa izimanga ezintsha ezivela ezinhlotsheni ezisolisayo. Ngakho-ke, ake sihambe, oh abalandeleli besizindalwazi abangakhathali!

Inombolo yokuqala. ukunemba kwangempela/okukabili/inombolo/imali

Kungabonakala sengathi izinhlobo zezinombolo ziyizona eziyinkinga kakhulu mayelana nezimanga ekuziphatheni. Kodwa noma ngabe kunjani. Ngakho ake siqale ngabo. Ngakho...

Ukhohlwe ukubala

SELECT 0.1::real = 0.1

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

Kwenzenjani? Inkinga ukuthi i-PostgreSQL iguqula i-0.1 engabhalwanga ukuze inembe kabili futhi izame ukuyiqhathanisa no-0.1 wohlobo lwangempela. Futhi lezi izincazelo ezihluke ngokuphelele! Umqondo uwukumela izinombolo zangempela kumemori yomshini. Njengoba u-0.1 engakwazi ukumelwa njengengxenye kanambambili enomkhawulo (kungaba ngu-0.0(0011) ku-binary), izinombolo ezinobunzulu obuhlukene zizohluka, yingakho umphumela wokuthi azilingani. Ngokuvamile, lesi yisihloko sendatshana ehlukile; ngeke ngibhale imininingwane eyengeziwe lapha.

Iphutha livelaphi?

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 abaningi bayazi ukuthi i-PostgreSQL ivumela ukuphawula okusebenzayo kohlobo lokusakaza. Okusho ukuthi, awukwazi ukubhala kuphela i-1::int, kodwa futhi int(1), ezolingana. Kodwa hhayi ezinhlotsheni amagama azo aqukethe amagama amaningana! Ngakho-ke, uma ufuna ukusakaza inani lezinombolo ukuze uthole uhlobo olunembayo oluphindwe kabili ngendlela yokusebenza, sebenzisa isiteketiso salolu hlobo lwe-float8, okungukuthi, KHETHA iflothi8(1).

Yini enkulu kune-infinity?

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

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

Bheka ukuthi kunjani! Kuvele ukuthi kukhona okukhulu kunokungapheli, futhi yi-NaN! Ngesikhathi esifanayo, imibhalo ye-PostgreSQL isibheka ngamehlo athembekile futhi ithi i-NaN ngokusobala inkulu kunanoma iyiphi enye inombolo, ngakho-ke, ingapheli. Okuphambene nakho kuyiqiniso ku--NaN. Sawubona, bathandi bezibalo! Kodwa kufanele sikhumbule ukuthi konke lokhu kusebenza kumongo wezinombolo zangempela.

Ukuzungeza iso

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

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

Okunye ukubingelela okungalindelekile okuvela kusisekelo. Futhi, khumbula ukuthi ukunemba okukabili kanye nezinhlobo zezinombolo zinemiphumela ehlukene yokuzungeza. Ezinombolo - indlela evamile, lapho u-0,5 efinyezwa, futhi ngokunemba okuphindwe kabili - u-0,5 usongelwa kunombolo eseduze.

Imali yinto ekhethekile

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

Ngokusho kwe-PostgreSQL, imali akuyona inombolo yangempela. Ngokusho kwabanye abantu, futhi. Kudingeka sikhumbule ukuthi ukusakaza uhlobo lwemali kungenzeka kuphela ohlotsheni lwezinombolo, njengoba nje uhlobo lwezinombolo lungaphonswa ohlotsheni lwemali. Kodwa manje ungadlala ngayo njengoba inhliziyo yakho ifisa. Kodwa ngeke kube yimali efanayo.

Isizukulwane esincane futhi esilandelanayo

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 ayithandi ukuchitha isikhathi ezintweni ezincane. Kuyini lokhu kulandelana okusekelwe ku-smallint? int, hhayi kancane! Ngakho-ke, lapho uzama ukusebenzisa lo mbuzo ongenhla, isizindalwazi sizama ukwenza okuncanyana kolunye uhlobo oluphelele, futhi ibona ukuthi kungase kube khona abalingisi abambalwa abanjalo. Yimuphi umlingisi ongakhetha? Akakwazi ukunquma lokhu, ngakho-ke uphahlazeka ngephutha.

Inombolo yefayela yesibili. "char"/char/varchar/text

Inani lokungajwayelekile likhona futhi ezinhlotsheni zezinhlamvu. Asibazane nabo.

Anjani amaqhinga lawa?

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

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

Hlobo luni lwe-"char" lolu, hlobo luni lomhlekisi lolu? Asizidingi lezo... Ngoba izenza i-char evamile, nakuba isemicaphunweni. Futhi ihlukile ku-char evamile, engenazo izingcaphuno, ngoba ikhipha ibhayithi yokuqala kuphela yokumelela iyunithi yezinhlamvu, kuyilapho i-char evamile ikhipha uhlamvu lokuqala. Esimweni sethu, uhlamvu lokuqala luwuhlamvu P, okuyinto ekumelelweni kwe-unicode kuthatha amabhayithi angu-2, njengoba kufakazelwa ukuguqula umphumela kuhlobo lwe-bytea. Futhi uhlobo lwe-"char" luthatha kuphela ibhayithi yokuqala yalokhu kumelwa kwe-unicode. Pho kungani lolu hlobo ludingeka? Imibhalo ye-PostgreSQL ithi lolu wuhlobo olukhethekile olusetshenziselwa izidingo ezikhethekile. Ngakho mancane amathuba okuthi singayidinga. Kodwa bheka emehlweni akhe futhi ngeke wenze iphutha lapho uhlangana naye ngokuziphatha kwakhe okukhethekile.

Izikhala ezengeziwe. Phela emehlweni phela emqondweni

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

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

Bheka isibonelo esinikeziwe. Ngiguqule yonke imiphumela ngokukhethekile ohlotsheni lwe-bytea, ukuze kubonakale ngokucacile ukuthi yini ekhona. Ziphi izikhala ezilandelanayo ngemva kokusakaza ku-varchar(6)? Amadokhumenti asho ngamafuphi: "Lapho uphonsa inani lohlamvu kolunye uhlobo lohlamvu, indawo emhlophe elandelanayo iyalahlwa." Lokhu kungathandi kumele kukhunjulwe. Futhi qaphela ukuthi uma iyunithi yezinhlamvu engaguquki iphonswa ngokuqondile ukuze uthayiphe i-varchar(6), izikhala ezilandelanayo ziyagcinwa. Zinjalo izimanga.

Inombolo yefayela yesithathu. json/jsonb

I-JSON iyisakhiwo esihlukile esiphila impilo yaso. Ngakho-ke, izinhlangano zayo kanye nalezo ze-PostgreSQL zihluke kancane. Nazi izibonelo.

Johnson kanye no Johnson. ukuzwa umehluko

SELECT 'null'::jsonb IS NULL

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

Into ukuthi i-JSON inebhizinisi layo elingenalutho, okungeyona i-analogue ye-NULL ku-PostgreSQL. Ngesikhathi esifanayo, into ye-JSON ngokwayo ingase ibe nenani elithi NULL, ngakho inkulumo ethi KHETHA null::jsonb IS NULL (qaphela ukungabikho kwezingcaphuno ezizodwa) izobuya ibe yiqiniso kulokhu.

Incwadi eyodwa ishintsha 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 ukuthi i-json ne-jsonb yizakhiwo ezihluke ngokuphelele. Ku-json, into igcinwa njengoba injalo, futhi ku-jsonb isivele igcinwe ngendlela yesakhiwo esihlukanisiwe, esinenkomba. Kungakho esimweni sesibili, inani lento ngokhiye 1 lashintshwa lisuka ku- [1, 2, 3] kuya ku- [7, 8, 9], elangena esakhiweni ekugcineni kanye nokhiye ofanayo.

Ungaphuzi amanzi 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 ekusetshenzisweni kwayo kwe-JSONB ishintsha ukufometha kwezinombolo zangempela, izilethe efomini yakudala. Lokhu akwenzeki ohlotsheni lwe-JSON. Okuxakayo kancane, kodwa uqinisile.

Inombolo yefayela yesine. usuku/isikhathi/isitembu sesikhathi

Kukhona futhi okungavamile ngezinhlobo zedethi/isikhathi. Ake sibheke kubo. Ake ngenze ukubhuka ngokushesha ukuthi ezinye izici zokuziphatha zicace uma uqonda kahle ingqikithi yokusebenza ngamazoni esikhathi. Kodwa lesi futhi isihloko se-athikili ehlukile.

Ezami aziqondi

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

Kubukeka sengathi yini engaqondakali lapha? Kodwa isizindalwazi asikaqondi ukuthi yini esiyibeka kuqala lapha—unyaka noma usuku? Futhi unquma ukuthi uJanuwari 99, 2008, okuyinto eshaya ingqondo yakhe. Ngokuvamile, lapho udlulisa izinsuku ngefomethi yombhalo, udinga ukuhlola ngokucophelela ukuthi isizindalwazi sizibone kahle kanjani (ikakhulukazi, hlaziya ipharamitha ye-datestyle ngomyalo we-SHOW datestyle), njengoba ukungaqondakali kulolu daba kungabiza kakhulu.

Ukuthathephi lokhu?

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

Kungani isizindalwazi singakwazi ukuqonda isikhathi esishiwo ngokucacile? Ngenxa yokuthi indawo yesikhathi ayinaso isifinyezo, kodwa igama eligcwele, elinengqondo kuphela kumongo wosuku, ngoba kucabangela umlando wezinguquko zesikhathi, futhi akusebenzi ngaphandle kosuku. Futhi wona kanye amazwi omugqa wesikhathi aphakamisa imibuzo - wayeqonde ukuthini umklami? Ngakho-ke, konke kunengqondo lapha, uma ubheka.

Что ему не так?

Cabanga ngesimo. Unenkambu etafuleni lakho enohlobo lwe-timestamptz. Ufuna ukukukhomba. Kodwa uyaqonda ukuthi ukwakha inkomba kulo mkhakha akulungile ngaso sonke isikhathi ngenxa yokukhetha kwayo okuphezulu (cishe wonke amanani alolu hlobo azohluka). Ngakho-ke unquma ukunciphisa ukukhetha kwenkomba ngokuphonsa uhlobo kudethi. Futhi uthola isimanga:

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

Kwenzenjani? Iqiniso liwukuthi ukusakaza uhlobo lwesitembu sesikhathi ohlotsheni lwedethi, kusetshenziswa inani lepharamitha yesistimu ye-TimeZone, okwenza umsebenzi wokuguqula uhlobo uncike kupharamitha yangokwezifiso, i.e. eziguquguqukayo. Imisebenzi enjalo ayivunyelwe kunkomba. Kulesi simo, kufanele ubonise ngokucacile ukuthi iyiphi indawo yesikhathi uhlobo lwe-cast cast.

Lapho manje akukho ngisho namanje nhlobo

Sesijwayele ukuthi manje() ukubuyisela idethi/isikhathi samanje, kucatshangelwa indawo yesikhathi. Kodwa bheka le mibuzo elandelayo:

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;

Idethi/isikhathi sibuyiselwa ngokufana kungakhathaliseki ukuthi singakanani isikhathi esidlule kusukela kusicelo sangaphambilini! Kwenzenjani? Iqiniso liwukuthi manje() akusona isikhathi samanje, kodwa isikhathi sokuqala kokwenziwayo kwamanje. Ngakho-ke, akushintshi ngaphakathi kokuthengiselana. Noma yimuphi umbuzo owethulwe ngaphandle kobubanzi bomsebenzi usongwe ekwenziweni ngokungagunci, yingakho singaqapheli ukuthi isikhathi esibuyiswe ngokuthi KHETHA manje(); empeleni, hhayi esamanje... Uma ufuna ukuthola isikhathi samanje esithembekile, udinga ukusebenzisa umsebenzi we-clock_timestamp().

Inombolo yefayela yesihlanu. kancane

Okuxakayo kancane

SELECT '111'::bit(4)

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

Iluphi uhlangothi okufanele lwengezwe amabhithi uma kwenzeka kunwetshwa uhlobo? Kubonakala sengathi ingakwesobunxele. Kodwa isisekelo kuphela esinombono ohlukile ngalolu daba. Qaphela: uma inombolo yamadijithi ingafani lapho usakaza uhlobo, ngeke ukuthole obukufuna. Lokhu kusebenza kukho kokubili ukungeza izingcezu kwesokudla kanye nezingcezu zokuphungula. Futhi kwesokudla...

Inombolo yefayela yesithupha. Izinhlu

Ngisho noNULL akazange adubule

SELECT ARRAY[1, 2] || NULL

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

Njengabantu abajwayelekile abakhuliswe ku-SQL, silindele ukuthi umphumela wale nkulumo ube NULL. Kodwa yayingekho. Kubuyiswe amalungu afanayo. Kungani? Ngoba kulokhu isisekelo senza okuthi NULL kuhlelo oluphelele futhi sibize ngokungagunci umsebenzi we-array_cat. Kodwa namanje akukacaci ukuthi kungani leli “kati eliwuhlu” lingalusethi kabusha uhlu. Lokhu kuziphatha nakho kudinga nje ukukhunjulwa.

Fingqa. Ziningi izinto eziyinqaba. Iningi labo, vele, aligxeka kakhulu kangangokuthi likhuluma ngokuziphatha okungalungile ngokusobala. Futhi ezinye zichazwa kalula ukusetshenziswa noma imvamisa ukusetshenziswa kwazo ezimeni ezithile. Kodwa ngesikhathi esifanayo, kunezimanga eziningi. Ngakho-ke, udinga ukwazi ngabo. Uma uthola noma yini enye engavamile noma engavamile ekuziphatheni kwanoma yiziphi izinhlobo, bhala kumazwana, ngizojabula ukungeza kumadokhumenti atholakalayo kuwo.

Source: www.habr.com

Izinhlobo ezisolisayo