ProHoster > Blog > Utongi > Inofungidzirwa mhando

Inofungidzirwa mhando

Hapana chinonyumwira pachitarisiko chavo. Uyezve, ivo vanotoita sevanoziva kwauri uye kwenguva refu. Asi izvozvo chete kusvikira wavaongorora. Apa ndipo pavanoratidza hunhu hwavo hwehunyengeri, vachishanda zvakasiyana zvachose pane zvawaitarisira. Uye dzimwe nguva vanoita chimwe chinhu chinoita kuti bvudzi rako rimire - semuenzaniso, vanorasikirwa nedata rakavanzika ravakachengeterwa. Paunotarisana navo, vanoti havazivane, kunyange mumumvuri vanoshanda nesimba pasi pehood imwechete. Inguva yekupedzisira kuvaunza kumvura yakachena. Ngatibateiwo nemhando idzi dzinofungirwa.

Kunyora data muPostgreSQL, kune zvese zvine musoro, dzimwe nguva kunopa zvinoshamisa zvinoshamisa. Muchikamu chino tichaedza kujekesa zvimwe zvezvipfeko zvavo, kunzwisisa chikonzero chemaitiro avo asinganzwisisiki uye kunzwisisa kuti tisapinda sei mumatambudziko mukuita kwemazuva ose. Kuti nditaure chokwadi, ndakanyora chinyorwa ichi zvakare semhando yebhuku rereferenzi kwandiri, bhuku rereferenzi iro rinogona kutaurwa nyore kwariri munyaya dzine nharo. Naizvozvo, inozozadzikiswa sezvo zvinoshamisa zvitsva kubva kumhando dzinofungidzirwa zvinowanikwa. Saka, ngatiende, oh vasinganeti dhatabhesi trackers!

Dossier nhamba yekutanga. real/double precision/numeric/mari

Zvingaite sekuti mhando dzenhamba ndidzo dzinenge dzichinyanya kunetsa maererano nekushamisika mukuita. Asi zvisinei kuti zvaita sei. Saka ngatitangei navo. Saka…

Kukanganwa kuverenga

SELECT 0.1::real = 0.1

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

Chii chiri kunetsa? Dambudziko nderekuti PostgreSQL inoshandura isina kunyorwa 0.1 kuti iite zvakapetwa kaviri uye kuyedza kuienzanisa ne 0.1 yemhando chaiyo. Uye izvi zvinoreva zvakasiyana zvachose! Pfungwa ndeyekumiririra nhamba chaidzo mundangariro dzemuchina. Sezvo 0.1 isingagone kumiririrwa sechidimbu chebhinari chinogumira (chingave 0.0(0011) mubhinari), nhamba dzine hudzamu hwakasiyana huchasiyana, nokudaro mhedzisiro yekuti hadzina kuenzana. Kazhinji kutaura, iyi inyaya yechinyorwa chakasiyana; Ini handisi kuzonyora zvakadzama pano.

Iko kukanganisa kunobva kupi?

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

Vanhu vazhinji vanoziva kuti PostgreSQL inobvumira mashandiro ekunyora kwemhando yekukanda. Ndiko kuti, iwe unogona kunyora kwete chete 1::int, asiwo int (1), iyo ichave yakaenzana. Asi kwete zvemhando dzine mazita ane mazwi akati wandei! Naizvozvo, kana iwe uchida kukanda kukosha kwenhamba kune yakapetwa kaviri mhando mune inoshanda fomu, shandisa alias yemhando iyi float8, kureva, SELECT float8(1).

Chii chakakura kudarika infinity?

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

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

Tarisa zvazvakaita! Zvinoitika kuti pane chimwe chinhu chakakura kupfuura chisingaperi, uye iNaN! Panguva imwecheteyo, zvinyorwa zvePostgreSQL zvinotitarisa nemaziso akatendeseka uye zvinoti NaN zviri pachena kuti yakakura kudarika imwe nhamba, uye, naizvozvo, isingagumi. Zvakapesana ndezvechokwadi zvakare kune -NaN. Mhoroi, vanoda masvomhu! Asi tinofanira kuyeuka kuti zvose izvi zvinoshanda mumamiriro ezvinhu ehuwandu chaihwo.

Kutenderedza maziso

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

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

Imwe kwaziso isingatarisirwe kubva pachigadziko. Zvekare, rangarira kuti kunyatsoita kaviri uye nhamba dzemhando dzine akasiyana ekutenderera mhedzisiro. Panhamba - nzira yenguva dzose, kana 0,5 ichitenderedzwa, uye nekuita kaviri - 0,5 inotenderedzwa yakananga kune iri pedyo kunyange nhamba yakazara.

Mari chinhu chakakosha

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

Maererano nePostgreSQL, mari haisi nhamba chaiyo. Maererano nevamwe vanhu, zvakare. Isu tinofanirwa kuyeuka kuti kukanda rudzi rwemari kunokwanisika chete kune nhamba yemhando, sekungoita chete nhamba yemhando inogona kukandwa kurudzi rwemari. Asi ikozvino unogona kutamba nayo sekuda kwemoyo wako. Asi haisi kuzova mari imwe chete.

Smallint uye kutevedzana chizvarwa

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 haifarire kutambisa nguva pazvinhu zvidiki. Ndeapi aya akatevedzana akavakirwa pane diki? int, kwete zvishoma! Naizvozvo, kana uchiedza kuita mubvunzo uri pamusoro, dhatabhesi inoedza kukanda diki kune imwe nhamba yakakura, uye inoona kuti panogona kunge paine akati wandei akadai. Ndeupi dhizaini yekusarudza? Haakwanise kusarudza izvi, uye nekudaro anorovera nekukanganisa.

Faira nhamba mbiri. "char"/char/varchar/text

Chiverengero chezvisinganzwisisike chiripowo mumhando dzemhando. Ngativaziveiwo.

Manomano rudzii aya?

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

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

Ndeupi rudzi rwe "char" iyi, rudzii rweclown iyi? Hatidi izvo ... Nekuti inonyepedzera kuva munhuwo zvake char, kunyangwe iri mune makotesheni. Uye inosiyana nechar yakajairwa, isina makotesheni, pakuti inongoburitsa yekutanga byte yekumiririra tambo, nepo char yakajairwa ichiburitsa hunhu hwekutanga. Muchiitiko chedu, chimiro chekutanga itsamba P, iyo muunicode inomiririra inotora 2 bytes, sezvinoratidzwa nekushandura chigumisiro kumhando yebytea. Uye iyo "char" mhando inotora chete yekutanga byte yeiyo unicode inomiririra. Saka nei rudzi urwu ruchidikanwa? Zvinyorwa zvePostgreSQL zvinoti iyi imhando yakakosha inoshandiswa kune zvakakosha. Saka isu hatigone kuzvida. Asi tarisa mumaziso ake uye hauzokanganisi paunosangana naye nemaitiro ake akakosha.

Extra nzvimbo. Kunze kwekuona, kubva mupfungwa

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

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

Tarisa muenzaniso wakapihwa. Ndakanyatso shandura maresults ese kumhando yebytea, kuti zvinyatso oneka zvaivepo. Ndekupi nzvimbo dzekutevera mushure mekukanda ku varchar(6)? Zvinyorwa zvinonyatsoti: "Kana uchikanda kukosha kwechimiro kune imwe mhando yemhando, chena inoteedzera inoraswa." Kusada uku kunofanira kurangarirwa. Uye cherechedza kuti kana tambo yakadzokororwa inokandwa yakananga kutaipa varchar(6), nzvimbo dzinotevera dzinochengetedzwa. Akadaro mashura.

Faira nhamba yechitatu. json/jsonb

JSON chimiro chakasiyana chinorarama hupenyu hwayo. Naizvozvo, masangano ayo uye ayo ePostgreSQL akasiyana zvishoma. Heino mienzaniso.

Johnson naJohnson. inzwa musiyano

SELECT 'null'::jsonb IS NULL

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

Chinhu ndechekuti JSON ine yayo isina chinhu, isiri iyo analogue yeNULL muPostgreSQL. Panguva imwecheteyo, chinhu cheJSON pachacho chinogona kunge chine kukosha NULL, saka izwi rekuti SELECT null ::jsonb IS NULL (cherechedza kusavapo kwekotesheni imwe chete) ichadzoka ichokwadi panguva ino.

Tsamba imwe inoshandura zvese

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

Chinhu ndechekuti json nejsonb zvimiro zvakasiyana zvachose. Mujson, chinhu chacho chinochengetwa sezvachiri, uye mujsonb chatochengetwa muchimiro chechidimbu, chine indexed. Ndicho chikonzero muchiitiko chechipiri, kukosha kwechinhu nekiyi 1 kwakatsiviwa kubva ku [1, 2, 3] kusvika [7, 8, 9], iyo yakapinda muchimiro pamagumo chaiwo nekiyi imwechete.

Usanwa mvura kubva kumeso kwako

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

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

PostgreSQL mukuita kwayo kweJSONB inoshandura mafomati enhamba chaidzo, ichivaunza kumhando yekirasi. Izvi hazviitike kurudzi rweJSON. Zvinoshamisa zvishoma, asi ari pachokwadi.

File number four. date/nguva/timestamp

Kune zvakare zvimwe zvisinganzwisisike nemhando dzemazuva / nguva. Ngativatarise. Rega ndiite chengetedzo ipapo ipapo kuti zvimwe zvehunhu zvinobuda pachena kana iwe uchinyatsonzwisisa kukosha kwekushanda nenzvimbo dzenguva. Asi iyi zvakare inyaya yechinyorwa chakasiyana.

Zvangu handizvinzwisise

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

Zvingaita sekuti chii chisinganzwisisike apa? Asi dhatabhesi haisati yanzwisisa zvatinoisa panzvimbo yekutanga pano β€” gore kana zuva? Uye anosarudza kuti ndiNdira 99, 2008, izvo zvinomushungurudza. Kazhinji, kana uchitumira misi muchimiro chemavara, unofanirwa kunyatso tarisa kuti dhatabhesi yakaaziva sei (kunyanya, ongorora iyo datestyle parameter neiyo SHOW datestyle command), sezvo kusanzwisisika panyaya iyi kunogona kudhura zvakanyanya.

Izvi wakazviwanepi?

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

Sei dhatabhesi isinganzwisise nguva yakatsanangurwa? Nokuti nguva yenguva haina chidimbu, asi zita rakazara, izvo zvine musoro chete mumamiriro ezuva, sezvo ichifunga nezvenhoroondo yekuchinja kwenzvimbo, uye haishande pasina zuva. Uye iwo mazwi chaiwo emutsetse wenguva anomutsa mibvunzo - mugadziri airevei chaizvo? Naizvozvo, zvese zvine musoro pano, kana ukazvitarisa.

Chii chakaipa naye?

Fungidzira mamiriro acho ezvinhu. Une munda mutafura yako ine mhando timestamptz. Unoda kuinyora. Asi iwe unonzwisisa kuti kuvaka index pamunda uyu hakusi nguva dzose kwakarurama nekuda kwekusarudzwa kwayo kwepamusoro (zvinenge zvese zvakakosha zverudzi urwu zvichave zvakasiyana). Saka iwe unofunga kudzikisa kusarudzwa kweiyo index nekukanda mhando kune zuva. Uye iwe unowana zvinoshamisa:

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

Chii chiri kunetsa? Icho chokwadi ndechekuti kukanda timestamptz mhando kurudzi rwezuva, kukosha kweTimeZone system parameter kunoshandiswa, izvo zvinoita kuti mhando yekushandura basa inotsamira pane yakasarudzika parameter, i.e. volatile. Mabasa akadaro haatenderwi muindex. Muchiitiko ichi, iwe unofanirwa kunyatso ratidza kuti ndeipi nguva nzvimbo iyo mhando yekukandwa inoitwa.

Parizvino hapana kana iko zvino zvachose

Isu takajaira ikozvino () kudzorera iyo yazvino zuva / nguva, tichifunga nezve nguva yenguva. Asi tarisa mibvunzo inotevera:

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;

Zuva / nguva inodzoserwa zvakafanana zvisinei kuti inguva yakadii yapfuura kubva pakukumbira kwekare! Chii chiri kunetsa? Ichokwadi ndechekuti ikozvino () haisi nguva yazvino, asi nguva yekutanga yekutengeserana kwazvino. Nokudaro, hazvichinje mukati mekutengesa. Chero mubvunzo wakatangwa kunze kwechikamu chekutengeserana wakaputirwa mukutengeserana zvisina kujeka, ndosaka isu tisingaone kuti nguva yakadzoserwa ne SARUDZO iri nyore ikozvino (); chaizvoizvo, kwete iyezvino ... Kana iwe uchida kuwana yakatendeseka ikozvino nguva, unoda kushandisa clock_timestamp () basa.

Faira nhamba shanu. zvishoma

Zvinoshamisa zvishoma

SELECT '111'::bit(4)

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

Nderipi divi rinofanira kuwedzerwa mabhiti kana mhando yekuwedzera? Inoita kunge iri kuruboshwe. Asi chigadziko chete chine maonero akasiyana panyaya iyi. Ngwarira: kana huwandu hwemadhijitari husingaenderane pakukanda mhando, hauzowana zvawaida. Izvi zvinoshanda kune ese ari maviri anowedzera mabhiti kurudyi nekucheka mabhiti. Zvakare kurudyi...

File number six. Arrays

Kunyange NULL haina kuridza

SELECT ARRAY[1, 2] || NULL

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

Sevanhuwo zvavo vakasimudzwa paSQL, isu tinotarisira kuti mhedzisiro yekutaura uku kuve NULL. Asi yakanga isipo. Array inodzoserwa. Sei? Nekuti muchiitiko ichi chigadziko chinokanda NULL kune yakazara array uye inodaidza zvisingaite iyo array_cat basa. Asi hazvisati zvanyatsojeka kuti sei iyi "array cat" isingagadzirise hurongwa. Hunhu uhu hunongodawo kurangarirwa.

Pfupisa. Pane zvinhu zvakawanda zvinoshamisa. Vazhinji vavo, hongu, havana kunyanya kutsoropodza zvekuti vataure nezve maitiro asina kufanira. Uye mamwe anotsanangurwa nekureruka kwekushandisa kana kuwanda kwekushandiswa kwavo mune mamwe mamiriro. Asi panguva imwe chete, kune zvakawanda zvinoshamisa. Naizvozvo, unofanira kuziva nezvavo. Kana iwe ukawana chimwe chinhu chinoshamisa kana chisina kujairika mumafambiro emhando ipi neipi, nyora mumashoko, ini ndichafara kuwedzera kune dossiers iripo pavari.

Source: www.habr.com

Voeg