ProHoster > Π‘Π»ΠΎΠ³ > Maamulka > Noocyada laga shakiyo

Noocyada laga shakiyo

Ma jiro wax laga shakiyo oo ku saabsan muuqaalkooda. Waxaa intaa dheer, waxay xitaa u muuqdaan kuwo si fiican kuu yaqaan iyo wakhti dheer. Laakiin taasi waa kaliya ilaa aad ka hubiso. Tani waa halka ay ku muujinayaan dabeecadooda khiyaanada ah, iyagoo si buuxda u shaqeynaya si ka duwan sidii aad filaysay. Mararka qaarkoodna waxay sameeyaan wax timahaaga ka dhigaya inay istaagaan - tusaale ahaan, waxay lumiyaan xog sir ah oo iyaga lagu aaminay. Marka aad ka hortimaado waxa ay ku andacoonayaan in aanay is garanayn, in kasta oo hadhkooda ay si adag u shaqeeyaan. Waa markii ugu dambeyntii la keeni lahaa biyo nadiif ah. Aynu sidoo kale wax ka qabanno noocyadan shakiga leh.

Ku-qorista xogta ee PostgreSQL, dhammaan caqli-galkeeda, waxay mararka qaarkood soo bandhigtaa yaabab yaab leh. Maqaalkani waxa aanu isku dayi doonaa in aan caddeeyo qaar ka mid ah qallafsanaantooda, fahamno sababta dhaqankooda qariibka ah iyo fahamka sida aan loola kulmin dhibaatooyinka dhaqanka maalinlaha ah. Si aan runta u sheego, waxa aan maqaalkan u soo ururiyey sidoo kale nooc ka mid ah buug tixraac ah oo nafteyda ah, buug tixraac ah oo si fudud loo tixraaci karo kiisaska muranka dhaliyay. Sidaa darteed, waa la buuxin doonaa marka la ogaado waxyaabo cusub oo la yaab leh oo ka yimid noocyo shaki leh. Haddaba, aynu tagno, hoog raadiyayaasha xogta aan daalin!

Diiwaanka lambarka koowaad. dhabta ah/laba jeer saxsanaanta/tiro/lacag

Waxay u ekaan kartaa in noocyada tirooyinka ay yihiin kuwa ugu dhibka yar marka loo eego la yaabka dhaqanka. Laakiin si kastaba ha ahaatee. Haddaba aan ku bilowno iyaga. Markaa…

Sida loo tiriyo ilaabay

SELECT 0.1::real = 0.1

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

Maxaa jira? Dhibaatadu waxay tahay in PostgreSQL ay u beddesho 0.1 joogta ah oo aan la qorin si ay u labanlaabto saxnaanta waxayna isku daydaa inay barbar dhigto 0.1 ee nooca dhabta ah. Oo kuwanu waa macnayaal gebi ahaanba kala duwan! Fikradda ayaa ah in lagu matalo tirooyinka dhabta ah ee xusuusta mashiinka. Maaddaama 0.1 aan loo matali karin sida jajab binary oo dhameystiran (waxay noqon doontaa 0.0 (0011) binary), tirooyinka leh qoto dheer oo kala duwan way ka duwanaan doonaan, markaa natiijada inaysan sinnayn. Guud ahaan, kani waa mowduuc loogu talagalay maqaal gaar ah, halkan kuma qori doono si faahfaahsan.

Halkee buu khaladku ka yimid?

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

Dad badan ayaa og in PostgreSQL ay u ogolaato calaamad muujinaysa nooca wax shubista. Taasi waa, ma qori kartid kaliya 1 :: int, laakiin sidoo kale int(1), taas oo u dhiganta. Laakin ma aha noocyo magacyadoodu ka kooban yihiin dhawr eray! Sidaa darteed, haddii aad rabto inaad ku tuurto qiime tiro ah si aad u labanlaabato nooca saxda ah ee qaab shaqaynaysa, isticmaal magaca loo yaqaan 'float8', oo ah, SELECT float8(1).

Maxaa ka weyn infinity?

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

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

Bal eeg sida ay tahay! Waxaa soo baxday in uu jiro wax ka weyn infinity, waana NaN! Isla mar ahaantaana, dukumeentiyada PostgreSQL waxay nagu eegayaan indho daacad ah waxayna sheeganayaan in NaN uu si cad uga weyn yahay tiro kasta oo kale, iyo, sidaas darteed, aan xadidnayn. Ka soo horjeeda sidoo kale waa run -NaN. Hello, xisaabta jecel! Laakiin waa in aan xasuusannaa in waxaas oo dhan ay ku shaqeeyaan macnaha tirooyinka dhabta ah.

Indho wareegsan

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

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

Salaan kale oo lama filaan ah oo saldhiga ka timid. Mar labaad, xasuusnoow in saxnaanta labanlaaban iyo noocyada lambaradu ay leeyihiin saameyn wareeg oo kala duwan. Marka loo eego tirooyinka - kan caadiga ah, marka 0,5 la soo koobo, iyo labanlaab sax ah - 0,5 ayaa loo soo koobay xitaa halbeegga ugu dhow.

Lacagtu waa shay gaar ah

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

Marka loo eego PostgreSQL, lacagtu maaha tiro dhab ah. Sida laga soo xigtay shakhsiyaadka qaarkood, sidoo kale. Waxaan u baahannahay inaan xasuusanno in tuurista nooca lacagtu ay suurtogal tahay oo keliya nooca nambarada, sida kaliya nooca tirada lagu tuuri karo nooca lacagta. Laakin hadda waxaad ugu ciyaari kartaa sida uu qalbigaagu rabo. Laakin ma noqon doonto lacag isku mid ah.

Jiilka yaryar iyo taxanaha

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 ma jecla inay wakhti ku lumiso waxyaabaha yaryar. Maxay yihiin taxanahan ku salaysan wax yar? int, aan ka yarayn! Sidaa darteed, marka la isku dayayo in la fuliyo su'aasha sare, xog ururintu waxa ay isku daydaa in ay ku riddo tiro yar oo nooc kale ah, oo ay aragto in ay jiri karaan dhawr kabka sida. Kabka la dooranayo? Ma go'aamin karto tan, oo sidaas darteed waxay ku dhacdaa qalad.

Faylka lambarka labaad. "char"/char/varchar/qoraal

Tiro khayaali ah ayaa waliba ku jira noocyada jilayaasha. Aynu iyagana is baranno.

Waa maxay xeeladahani?

SELECT 'ΠŸΠ•Π’Π―'::"char"
     , 'ΠŸΠ•Π’Π―'::"char"::bytea
     , 'ΠŸΠ•Π’Π―'::char
     , 'ΠŸΠ•Π’Π―'::char::bytea

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

Waa maxay nooca "char" kani, waa maxay nooca clown? Uma baahnno kuwa ... Sababtoo ah waxay u muuqataa inay tahay char caadi ah, inkastoo ay ku jirto xigashooyin. Oo waxay ka duwan tahay char caadiga ah, taas oo aan lahayn xigashooyin, taas oo soo saarta kaliya byte ugu horreeya ee matalaadda xargaha, halka char caadiga ah uu soo saaro dabeecadda koowaad. Xaaladeena, xarafka ugu horreeya waa xarafka P, kaas oo ku jira unicode matalaa wuxuu qaataa 2 bytes, taas oo caddaynaysa in natiijada loo beddelayo nooca bytea. Nooca "char" wuxuu qaataa kaliya byte-ka ugu horreeya ee matalaadda Unicode. Haddaba maxaa noocaan loogu baahan yahay? Dukumentiga PostgreSQL ayaa sheegaya in kani yahay nooc gaar ah oo loo isticmaalo baahiyaha gaarka ah. Markaa lagama yaabo inaan u baahanahay. Laakin fiiri indhihiisa oo kuma qaldami doontid marka aad la kulanto asaga oo leh dhaqankiisa gaarka ah.

Meelo dheeraad ah Aragga ka baxay, maankana ka baxay

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

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

Bal u fiirso tusaalaha la bixiyay. Waxaan si gaar ah ugu beddelay dhammaan natiijooyinka nooca bytea, si ay si cad u muuqato waxa meesha ku jira. Aaway meelaha banaan ee ka dambeeya tuurista varchar(6)? Dukumeentigu wuxuu si kooban u dhigayaa: "Marka la tuurayo qiimaha jilaha nooc kale, raad-raac meel cad ayaa la tuuray." Nacaybkan waa in la xusuustaa. Oo ogow in haddii xadhig joogto ah oo la soo xigtay si toos ah loogu tuuro nooca varchar(6), meelaha bannaan waa la ilaalinayaa. Kuwaasi waa mucjisooyinka.

Gal lambarka saddexaad. json/jsonb

JSON waa dhisme gooni ah oo ku nool nolosheeda. Sidaa darteed, hay'addeeda iyo kuwa PostgreSQL way kala duwan yihiin. Waa kuwan tusaalayaal.

Johnson iyo Johnson. dareemo faraqa

SELECT 'null'::jsonb IS NULL

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

Shayga ayaa ah in JSON ay leedahay wax aan waxba ka jirin, oo aan ahayn analoogga NULL ee PostgreSQL. Isla mar ahaantaana, shayga JSON laftiisa ayaa laga yaabaa inuu si fiican u lahaado qiimaha NULL, sidaa darteed odhaahda SELECT null :: jsonb IS NULL (xusuusnow maqnaanshaha xigashooyinka) ayaa soo noqon doona run markan.

Hal xaraf ayaa wax walba beddela

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

Shayga ayaa ah in json iyo jsonb ay yihiin qaabab gebi ahaanba kala duwan. Gudaha json, shayga waxa loo kaydiyaa sidiisii, jsonbna waxa uu horeba ugu kaydsan yahay qaab qaabaysan oo la kala saaray. Taasi waa sababta kiiska labaad, qiimaha shayga ee furaha 1 lagu beddelay [1, 2, 3] ilaa [7, 8, 9], kaas oo la soo galay qaab-dhismeedka dhammaadka isla furaha.

Ha ka cabbin biyaha wejigaaga

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

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

PostgreSQL ee hirgalinteeda JSONB waxay bedeshaa qaabaynta tirooyinka dhabta ah, iyaga oo keenaya qaabka caadiga ah. Tani kuma dhacayso nooca JSON. In yar oo la yaab leh, laakiin isagu waa sax.

Gal lambarka afraad. taariikhda/waqtiga/waqtiga shaambada

Waxa kale oo jira waxyaabo aan caadi ahayn oo leh noocyada taariikhda/waqtiga. Aynu eegno iyaga. Aan isla markaaba meel sii dhigo in qaar ka mid ah sifooyinka habdhaqanku ay caddaato haddii aad si fiican u fahanto nuxurka ka shaqaynta aagagga wakhtiga. Laakiin tani sidoo kale waa mowduuc loogu talagalay maqaal gaar ah.

Kayga ma fahmin

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

Waxay u egtahay in waxa aan la fahmi karin halkan? Laakin xog-ururinta weli ma fahmin waxa aan dhignay meesha ugu horeysa halkan-sanadka ama maalinta? Waxayna go'aansatey in ay tahay Janaayo 99, 2008, taas oo maskaxdeeda ku dhufatay. Guud ahaan, markaad u gudbinayso taariikhaha qaab qoraal ah, waxaad u baahan tahay inaad si taxadar leh u hubiso sida saxda ah ee xogta xogta loo aqoonsaday (gaar ahaan, la falanqeyso qiyaasta datestyle ee amarka qaabka taariikhda SHOW), maadaama madmadowga arrintani uu noqon karo mid aad qaali u ah.

Xageed ka keentay tan?

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

Maxay xogtu u fahmi la'dahay wakhtiga si cad loo cayimay? Sababtoo ah aagga wakhtigu ma laha soo gaabinta, laakiin waa magac buuxa, taas oo macno samaynaysa oo kaliya marka la eego macnaha taariikhda, maadaama ay tixgelinayso taariikhda aagagga wakhtiga isbeddelka, oo aan shaqaynayn taariikh la'aan. Ereyada wakhtiga ee wakhtiga ayaa keenaya su'aalo - muxuu runtii ula jeeday barnaamij-sameeyaha? Sidaa darteed, wax walba waa macquul halkan, haddii aad eegto.

Maxaa isaga ka qaldan?

Bal qiyaas xaaladda. Waxaad miiskaaga ku haysaa beer leh nooca timestamptz. Waxaad doonaysaa in aad index. Laakiin waxaad fahamsan tahay in dhismaha tusmaynta goobtan aan had iyo jeer la caddayn sababtoo ah xulashada sare (ku dhawaad ​​dhammaan qiyamka noocaan ah ayaa noqon doona mid gaar ah). Markaa waxaad go'aansatey inaad hoos u dhigto xulashada tusaha adiga oo nooca taariikhda ku dhejinaya. Oo waxaad helaysaa yaab:

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

Maxaa jira? Xaqiiqdu waxay tahay in lagu tuuro nooca timestamptz nooca taariikhda, qiimaha nidaamka nidaamka TimeZone ayaa la isticmaalaa, taas oo ka dhigaysa nooca beddelka shaqada inay ku tiirsan tahay cabbirka caadada ah, i.e. kacsan. Hawlaha noocan oo kale ah lagama oggola tusmada. Xaaladdan oo kale, waa inaad si cad u muujisaa aagga wakhtiga nooca kabka lagu sameeyay.

Marka hadda xataa hadda ma aha

Waxaan u barannay inaan hadda () soo celinno taariikhda/waqtiga hadda, anagoo tixgelinayna aagga waqtiga. Laakin fiiri su'aalaha soo socda:

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;

Taariikhda/wakhtiga si isku mid ah ayaa loo soo celiyaa iyadoon loo eegin inta wakhti ee ka soo wareegtay codsigii hore! Maxaa jira? Xaqiiqdu waxay tahay in hadda () aysan ahayn wakhtiga hadda, laakiin wakhtiga bilawga ah ee macaamilka hadda jira. Sidaa darteed, waxba iskama beddelin wax kala iibsiga. Weydiimo kasta oo la bilaabo si ka baxsan baaxadda wax kala iibsiga waxa lagu duuduubay macaamil ganacsi si daahsoon, taas oo ah sababta aynaan u dareemin in wakhtiga uu soo celiyay Xulasho fudud hadda(); dhab ahaantii, ma aha kan hadda... Haddii aad rabto in aad hesho wakhti daacad ah, waxaad u baahan tahay inaad isticmaasho clock_timestamp() function.

Galka shanaad xoogaa

Cajiib in yar

SELECT '111'::bit(4)

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

Dhinacgee ayay tahay in qashinka lagu daro haddii ay dhacdo kordhin nooca? Waxay u muuqataa in ay tahay bidix. Laakiin kaliya saldhigga ayaa fikrad ka duwan arrintan ku leh. Ka digtoonow: haddii tirada lambaradu iswaafaqin waydo marka aad nooc tuurayso, ma heli doontid waxaad rabtay. Tani waxay khusaysaa labada ku-darka qaybinta midigta iyo gooynta. Sidoo kale dhanka midig...

Gal lambarka lixaad. Habab

Xitaa NULL ma ridin

SELECT ARRAY[1, 2] || NULL

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

Sida dadka caadiga ah lagu soo qaaday SQL, waxaan filaynaa in natiijada odhaahdani ay noqoto NULL. Laakiin halkaas may ahayn. Array ayaa la soo celiyay. Waa maxay sababtu? Sababtoo ah kiiskan salku wuxuu NULL u tuuraa array isku dhafan wuxuuna si maldahan ugu yeeraa shaqada array_cat. Laakin wali ma cadda sababta " bisad diyaarsan " aysan dib ugu habeynin shaxanka. Habdhaqankan sidoo kale waxa uu u baahan yahay in la xasuusto.

Soo koob. Waxaa jira waxyaabo badan oo qariib ah. Badankooda, dabcan, maaha kuwo aad u dhaliila in laga hadlo dabeecad aan habboonayn oo cad. Iyo kuwa kale waxaa lagu sharaxay si fudud isticmaalka ama inta jeer ee ay ku dabaqi karaan xaaladaha qaarkood. Laakiin isla markaa, waxaa jira waxyaabo badan oo la yaab leh. Sidaa darteed, waxaad u baahan tahay inaad ka ogaato iyaga. Haddii aad aragto wax kale oo qariib ah ama aan caadi ahayn habdhaqanka nooc kasta, ku qor faallooyinka, waxaan ku farxi doonaa inaan ku daro dossier-yada laga heli karo iyaga.

Source: www.habr.com

Add a comment