🥇 Mitundu yokayikitsa | Pulogalamu ya ProHoster

Palibe chokayikitsa pa maonekedwe awo. Komanso, amawoneka odziwika bwino kwa inu komanso kwa nthawi yayitali. Koma ndi mpaka mutawafufuza. Apa ndipamene amawonetsa chikhalidwe chawo chobisika, akugwira ntchito mosiyana kwambiri ndi momwe mumayembekezera. Ndipo nthawi zina amachita zomwe zimapangitsa tsitsi lanu kuima - mwachitsanzo, amataya deta yachinsinsi yomwe adapatsidwa. Mukakumana nawo, amanena kuti sakudziwana, ngakhale mumithunzi amagwira ntchito molimbika pansi pa chovala chomwecho. Yakwana nthawi yoti muwabweretse kumadzi oyera. Tiyeni tithanenso ndi mitundu yokayikitsa imeneyi.

Kulemba kwa data mu PostgreSQL, pamalingaliro ake onse, nthawi zina kumapereka zodabwitsa kwambiri. M'nkhaniyi tiyesera kufotokozera zina mwazolakwika zawo, kumvetsetsa chifukwa cha khalidwe lawo lachilendo ndikumvetsetsa momwe sayenera kukumana ndi mavuto pazochitika za tsiku ndi tsiku. Kunena zowona, ndinapanganso nkhaniyi ngati mtundu wa buku lofotokozera ndekha, buku lofotokozera lomwe lingatchulidwe mosavuta pamikangano. Chifukwa chake, idzawonjezeredwanso pamene zodabwitsa zatsopano kuchokera ku mitundu yokayikitsa zipezeka. Chifukwa chake, tiyeni tipite, osakasaka osatopa!

Dossier nambala wani. zenizeni/kuwirikiza kawiri/nambala/ndalama

Zitha kuwoneka kuti mitundu ya manambala ndiyomwe ilibe vuto potengera zodabwitsa zamakhalidwe. Koma ziribe kanthu momwe izo ziri. Choncho tiyeni tiyambe nawo. Ndiye…

Kuyiwala kuwerengera

SELECT 0.1::real = 0.1

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

Vuto ndi chiyani? Vuto ndiloti PostgreSQL imasintha 0.1 yosasinthika kuti ikhale yolondola kawiri ndikuyesa kuifanizira ndi 0.1 yamtundu weniweni. Ndipo awa ndi matanthauzo osiyana kotheratu! Lingaliro ndikuyimira manambala enieni mu kukumbukira makina. Popeza 0.1 sangayimilidwe ngati kagawo kakang'ono komaliza (kungakhale 0.0(0011) mu binary), manambala okhala ndi manambala osiyanasiyana adzakhala osiyana, chifukwa chake sizofanana. Nthawi zambiri, uwu ndi mutu wankhani ina; sindilemba mwatsatanetsatane apa.

Kodi cholakwikacho chimachokera kuti?

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

Anthu ambiri amadziwa kuti PostgreSQL imalola zolemba zamtundu wamtundu. Ndiko kuti, simungathe kulemba 1 ::int, komanso int(1), yomwe idzakhala yofanana. Koma osati mitundu yomwe mayina awo amakhala ndi mawu angapo! Chifukwa chake, ngati mukufuna kuyika manambala kuti mutsimikizire kuwirikiza kawiri mu mawonekedwe ogwirira ntchito, gwiritsani ntchito dzina lamtundu uwu float8, ndiye kuti, SELECT float8(1).

Chachikulu kuposa infinity ndi chiyani?

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

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

Taonani momwe izo ziliri! Zikuoneka kuti pali china chachikulu kuposa chopanda malire, ndipo ndi NaN! Panthawi imodzimodziyo, zolemba za PostgreSQL zimatiyang'ana ndi maso oona mtima ndipo zimati NaN mwachiwonekere ndi yaikulu kuposa nambala ina iliyonse, motero, yopanda malire. Zosiyana nazo ndizowonanso -NaN. Moni, okonda masamu! Koma tiyenera kukumbukira kuti zonsezi zimagwira ntchito paziwerengero zenizeni.

Kuzungulira maso

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

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

Moni wina wosayembekezeka kuchokera ku base. Apanso, kumbukirani kuti kulondola kawiri ndi mitundu ya manambala imakhala ndi zotsatira zosiyana zozungulira. Kwa manambala - njira yanthawi zonse, pamene 0,5 ikuzunguliridwa, ndi kulondola kawiri - 0,5 imazunguliridwa kufupi kwambiri.

Ndalama ndi chinthu chapadera

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

Malinga ndi PostgreSQL, ndalama si nambala yeniyeni. Malinga ndi anthu ena, nawonso. Tiyenera kukumbukira kuti kuponyera mtundu wa ndalama ndi kotheka ku mtundu wa manambala, monganso mtundu wa manambala ukhoza kuponyedwa ku mtundu wa ndalama. Koma tsopano mutha kusewera nawo momwe mtima wanu ukufunira. Koma sizikhala ndalama zomwezo.

Smallint ndi mndandanda m'badwo

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 sikonda kuwononga nthawi pazinthu zazing'ono. Kodi zotsatizanazi zozikidwa pazing'onozing'ono ndi ziti? int, ayi! Chifukwa chake, poyesa kuyankha funso lomwe lili pamwambapa, nkhokweyo imayesa kuponya kakang'ono ku mtundu wina wamtundu uliwonse, ndipo imawona kuti pangakhale zingapo zotere. Osankha otani? Sangathe kusankha izi, motero amakumana ndi cholakwika.

Fayilo nambala yachiwiri. "char"/char/varchar/text

Pali mitundu ingapo yosamvetseka nayonso m'mitundu ya anthu. Tiyenso tiwadziwe.

Kodi awa ndi machenjerero otani?

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

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

Kodi uyu ndi "char" wamtundu wanji, ndi woseketsa wamtundu wanji uyu? Sitikufuna izo ... Chifukwa zimadziyesa ngati char wamba, ngakhale zili m'mawu. Ndipo imasiyana ndi char wamba, yomwe ilibe mawu, chifukwa imangotulutsa ma byte oyamba a chingwe, pomwe char wamba imatulutsa munthu woyamba. Kwa ife, khalidwe loyamba ndi chilembo P, chomwe mu chithunzithunzi cha unicode chimatenga ma byte 2, monga umboni wa kutembenuza zotsatira kukhala mtundu wa bytea. Ndipo mtundu wa "char" umangotenga ma byte oyamba a yunicode iyi. Ndiye n’chifukwa chiyani mtundu umenewu uli wofunika? Zolemba za PostgreSQL zimati uwu ndi mtundu wapadera womwe umagwiritsidwa ntchito pazosowa zapadera. Choncho n’zokayikitsa kuti tingazifune. Koma yang'anani m'maso mwake ndipo simudzalakwitsa mukakumana naye ndi khalidwe lake lapadera.

Mipata yowonjezera. Osawoneka, osokonezeka

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

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

Taonani chitsanzo choperekedwa. Ndinasintha mwapadera zotsatira zonse kukhala mtundu wa bytea, kuti ziwonekere bwino zomwe zinalipo. Kodi mipata yotsatira ili kuti mutatha kuponya ku varchar(6)? Zolembazo zimanena momveka bwino kuti: "Poyika mtengo wamtundu ku mtundu wina, malo oyera amatayidwa." Kusakonda uku kuyenera kukumbukiridwa. Ndipo zindikirani kuti ngati chingwe chokhazikika chikuponyedwa mwachindunji ku mtundu wa varchar(6), mipata yotsata imasungidwa. Izi ndi zozizwa.

Fayilo nambala XNUMX. json/jsonb

JSON ndi gawo losiyana lomwe limakhala moyo wake. Chifukwa chake, mabungwe ake ndi a PostgreSQL ndi osiyana pang'ono. Nazi zitsanzo.

Johnson ndi Johnson. kumva kusiyana

SELECT 'null'::jsonb IS NULL

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

Chowonadi ndi chakuti JSON ili ndi gawo lake lopanda pake, lomwe silofanana ndi NULL mu PostgreSQL. Nthawi yomweyo, chinthu cha JSON chokha chingakhale ndi mtengo wa NULL, kotero mawu akuti SELECT null ::jsonb IS NULL (zindikirani kusakhalapo kwa mawu amodzi) abwereranso nthawi ino.

Chilembo chimodzi chimasintha chirichonse

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

Chowonadi ndi chakuti json ndi jsonb ndizosiyana kotheratu. Mu json, chinthucho chimasungidwa momwe chilili, ndipo mu jsonb chimasungidwa kale m'mawonekedwe osankhidwa, olembedwa. Ndicho chifukwa chachiwiri, mtengo wa chinthu ndi fungulo 1 unasinthidwa kuchokera ku [1, 2, 3] mpaka [7, 8, 9], yomwe inalowa mu dongosolo kumapeto kwenikweni ndi fungulo lomwelo.

Osamwa madzi a pankhope pako

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

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

PostgreSQL mu kukhazikitsa kwake kwa JSONB imasintha masanjidwe a manambala enieni, kuwabweretsa ku mawonekedwe akale. Izi sizichitika kwa mtundu wa JSON. Chodabwitsa pang'ono, koma iye akulondola.

Fayilo nambala XNUMX. tsiku/nthawi/chidindo chanthawi

Palinso zosamvetseka ndi mitundu ya tsiku/nthawi. Tiyeni tiyang'ane pa iwo. Ndiloleni ndisungitse nthawi yomweyo kuti zina mwamakhalidwe zimamveka bwino ngati mukumvetsetsa tanthauzo lakugwira ntchito ndi magawo anthawi. Koma uwunso ndi mutu wa nkhani ina.

Zanga sizikumvetsa

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

Zikuwoneka kuti ndi chiyani chosamvetsetseka apa? Koma nkhokweyo samamvetsetsabe zomwe timayika pamalo oyamba pano-chaka kapena tsiku? Ndipo adaganiza kuti ndi Januware 99, 2008, zomwe zidamudabwitsa. Nthawi zambiri, potumiza masiku m'mawu olembedwa, muyenera kuyang'ana mosamala momwe malowo adawazindikirira (makamaka, pendani chizindikiro cha datestyle ndi SHOW datestyle), popeza kusamveka bwino pankhaniyi kungakhale kokwera mtengo kwambiri.

Munazitenga kuti izi?

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

Chifukwa chiyani malo osungiramo zinthu zakale sangamvetse nthawi yodziwika bwino? Chifukwa nthawi zone alibe chidule, koma dzina lathunthu, zomwe n'zomveka mu nkhani ya deti, chifukwa zimatengera mbiri ya kusintha zone nthawi, ndipo sachiza popanda deti. Ndipo mawu omwe ali pamndandanda wa nthawi amadzutsa mafunso - kodi wopanga mapulogalamuwo amatanthauza chiyani? Choncho, zonse ndi zomveka apa, ngati inu muyang'ana pa izo.

Chavuta ndi chiyani ndi iye?

Tangolingalirani mkhalidwewo. Muli ndi gawo patebulo lanu ndi mtundu wa timestamptz. Mukufuna kuzilozera. Koma mukumvetsa kuti kupanga index pa gawo ili sikoyenera nthawi zonse chifukwa cha kusankha kwake kwakukulu (pafupifupi mayendedwe onse amtunduwu adzakhala apadera). Chifukwa chake mwaganiza zochepetsera kusankha kwa index poponya mtunduwo mpaka tsiku. Ndipo mukupeza zodabwitsa:

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

Vuto ndi chiyani? Chowonadi ndi chakuti kuponyera mtundu wa timestamptz ku mtundu wa tsiku, mtengo wa TimeZone system parameter umagwiritsidwa ntchito, zomwe zimapangitsa kuti mtundu wa kutembenuka ukhale wodalira pa chikhalidwe cha chikhalidwe, i.e. wosakhazikika. Ntchito zotere siziloledwa muzolozera. Pankhaniyi, muyenera kuwonetsa momveka bwino kuti ndi nthawi yanji yomwe mtunduwo ukuchitikira.

Pamene tsopano palibe ngakhale tsopano nkomwe

Tazolowera tsopano() kubweza tsiku/nthawi yomwe ilipo, poganizira nthawi yanthawiyo. Koma yang'anani mafunso otsatirawa:

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;

Tsiku / nthawi yabwezeredwa chimodzimodzi mosasamala kanthu kuti yadutsa nthawi yayitali bwanji kuchokera pa pempho lapitalo! Vuto ndi chiyani? Chowonadi ndi chakuti tsopano () si nthawi yapano, koma nthawi yoyambira ntchitoyo. Choncho, sizisintha mkati mwa malonda. Funso lililonse lomwe latulutsidwa kunja kwa gawo la malonda likukulungidwa muzochitika, chifukwa chake sitikuwona kuti nthawi yomwe yabwezedwa ndi KUSANKHA kosavuta tsopano (); kwenikweni, osati yamakono... Ngati mukufuna kupeza nthawi yowona mtima, muyenera kugwiritsa ntchito clock_timestamp() ntchito.

Fayilo nambala XNUMX. pang'ono

Chodabwitsa pang'ono

SELECT '111'::bit(4)

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

Ndi mbali iti yomwe ma bits ayenera kuwonjezeredwa ngati atakulitsa mtundu? Zikuoneka kuti zili kumanzere. Koma maziko okha ali ndi maganizo osiyana pa nkhaniyi. Samalani: ngati chiwerengero cha manambala sichikugwirizana popanga mtundu, simupeza zomwe mukufuna. Izi zimagwiranso ntchito powonjezera ma bits kumanja ndi zochepetsera. Komanso kumanja...

Fayilo nambala XNUMX. Mipikisano

Ngakhale NULL sanawombere

SELECT ARRAY[1, 2] || NULL

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

Monga anthu wamba omwe adakulira pa SQL, tikuyembekeza zotsatira za mawuwa kukhala NULL. Koma kunalibe kumeneko. Gulu labwezedwa. Chifukwa chiyani? Chifukwa pamenepa maziko amaponyera NULL kumagulu onse ndipo amatchula ntchito ya array_cat. Koma sizikudziwikabe chifukwa chake "mphaka wophatikizika" uyu samakhazikitsanso gululo. Khalidwelinso liyenera kukumbukiridwa.

Fotokozerani mwachidule. Pali zinthu zambiri zachilendo. Ambiri a iwo, ndithudi, sali otsutsa kwambiri kotero kuti amalankhula za khalidwe losayenera. Ndipo ena amafotokozedwa mosavuta kugwiritsa ntchito kapena kuchuluka kwa momwe angagwiritsire ntchito nthawi zina. Koma panthawi imodzimodziyo, pali zodabwitsa zambiri. Choncho, muyenera kudziwa za iwo. Ngati mupeza china chilichonse chodabwitsa kapena chosazolowereka pamakhalidwe amtundu uliwonse, lembani mu ndemanga, ndidzakhala wokondwa kuwonjezera ma dossiers omwe alipo.

Source: www.habr.com

Mitundu yokayikitsa