ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Yam txawv txawv

Yam txawv txawv

Tsis muaj ib yam dab tsi txawv ntawm lawv qhov tsos. Ntxiv mus, lawv txawm zoo li paub koj zoo thiab ntev. Tab sis qhov ntawd tsuas yog txog thaum koj kuaj lawv. Qhov no yog qhov uas lawv qhia lawv qhov kev tsis txaus ntseeg, ua haujlwm txawv tshaj qhov koj xav tau. Thiab qee zaum lawv ua qee yam uas ua rau koj cov plaub hau sawv ntawm qhov kawg - piv txwv li, lawv poob cov ntaub ntawv zais cia rau lawv. Thaum koj tawm tsam lawv, lawv thov tias lawv tsis paub ib leeg, txawm tias nyob hauv qhov ntxoov ntxoo lawv ua haujlwm hnyav hauv tib lub hood. Nws yog lub sij hawm thaum kawg coj lawv mus ntxuav dej. Cia peb kuj nrog cov yam tsis txaus ntseeg no.

Cov ntaub ntawv ntaus ntawv hauv PostgreSQL, rau tag nrho nws cov logic, qee zaum nthuav tawm kev xav tsis thoob. Hauv tsab xov xwm no peb yuav sim piav qhia qee qhov ntawm lawv cov quirks, nkag siab txog qhov laj thawj ntawm lawv tus cwj pwm coj txawv txawv thiab nkag siab tias yuav ua li cas kom tsis txhob muaj teeb meem hauv kev xyaum txhua hnub. Yuav kom qhia qhov tseeb, kuv tau sau tsab xov xwm no los ua ib hom ntawv siv rau kuv tus kheej, ib phau ntawv siv uas tuaj yeem raug xa mus rau hauv cov teeb meem tsis sib haum xeeb. Yog li ntawd, nws yuav raug muab ntxiv raws li qhov xav tsis thoob tshiab los ntawm cov yam tsis txaus ntseeg tau pom. Yog li, cia peb mus, huag tsis nkees database trackers!

Dossier naj npawb ib. tiag / ob chav precision / numeric / nyiaj

Nws yuav zoo li tias cov naj npawb yog qhov teeb meem tsawg tshaj plaws ntawm kev xav tsis thoob hauv kev coj cwj pwm. Tab sis txawm li cas los xij. Yog li cia peb pib nrog lawv. Yog li ntawd…

Tsis nco qab suav li cas

SELECT 0.1::real = 0.1

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

Dab tsi yog qhov teeb meem? Qhov teeb meem yog tias PostgreSQL hloov pauv qhov tsis tu ncua 0.1 mus rau ob npaug ntawm qhov tseeb thiab sim muab piv nrog 0.1 ntawm hom tiag tiag. Thiab cov no yog cov ntsiab lus sib txawv kiag li! Lub tswv yim yog los sawv cev rau tus lej tiag hauv lub tshuab nco. Txij li thaum 0.1 tsis tuaj yeem sawv cev raws li qhov kawg binary feem (nws yuav yog 0.0(0011) hauv binary), cov lej nrog tus lej sib txawv yuav txawv, yog li qhov tshwm sim uas lawv tsis sib npaug. Feem ntau hais lus, qhov no yog ib lub ntsiab lus rau ib tsab xov xwm cais; Kuv yuav tsis sau kom meej ntxiv ntawm no.

Qhov yuam kev tuaj qhov twg?

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

Ntau tus neeg paub tias PostgreSQL tso cai rau cov ntawv sau ua haujlwm rau hom casting. Ntawd yog, koj tuaj yeem sau tsis tsuas yog 1:: int, tab sis kuj int(1), uas yuav sib npaug. Tab sis tsis yog rau hom uas nws lub npe muaj ob peb lo lus! Yog li ntawd, yog tias koj xav nrum tus lej tus nqi rau ob hom precision hauv daim ntawv ua haujlwm, siv lub npe ntawm hom float8, uas yog, SELECT float8(1).

Dab tsi yog qhov loj dua infinity?

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

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

Saib seb nws zoo li cas! Nws hloov tawm muaj ib yam dab tsi loj dua infinity, thiab nws yog NaN! Nyob rau tib lub sijhawm, PostgreSQL cov ntaub ntawv saib peb nrog lub qhov muag ncaj ncees thiab thov tias NaN yog qhov tseeb ntau dua li lwm tus lej, thiab, yog li ntawd, infinity. Qhov ntxeev kuj muaj tseeb rau -NaN. Nyob zoo, math lovers! Tab sis peb yuav tsum nco ntsoov tias txhua yam no ua haujlwm hauv cov ntsiab lus ntawm cov lej tiag.

Qhov muag ncig

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

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

Lwm qhov kev npaj tos txais tos los ntawm lub hauv paus. Ntxiv dua thiab, nco ntsoov tias ob chav precision thiab cov lej muaj ntau yam sib txawv. Rau tus lej - txoj hauv kev ib txwm muaj, thaum 0,5 yog sib npaug, thiab rau qhov tseeb dua - 0,5 yog sib npaug ntawm qhov ze tshaj plaws txawm tus lej.

Nyiaj yog ib yam tshwj xeeb

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

Raws li PostgreSQL, nyiaj tsis yog tus lej tiag. Raws li qee tus neeg, ib yam nkaus. Peb yuav tsum nco ntsoov tias casting hom nyiaj tsuas yog ua tau rau hom lej xwb, tsuas yog hom lej tuaj yeem muab pov rau hom nyiaj. Tab sis tam sim no koj tuaj yeem ua si nrog nws raws li koj lub siab xav. Tab sis nws yuav tsis yog tib cov nyiaj.

Smallint thiab sequence tiam

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 tsis nyiam nkim sij hawm nyob rau hauv trifles. Dab tsi yog cov sequences raws li me me? int, tsi! Yog li ntawd, thaum sim ua kom tiav cov lus nug saum toj no, cov ntaub ntawv sim muab cov ntawv me me rau qee qhov sib npaug, thiab pom tias yuav muaj ntau yam pov tseg. Leej twg xaiv? Nws tsis tuaj yeem txiav txim siab qhov no, thiab yog li ntawd poob nrog qhov yuam kev.

Cov ntaub ntawv thib ob. "char"/char/varchar/text

Ib tug xov tooj ntawm oddities kuj muaj nyob rau hauv cov cim hom. Cia peb mus paub lawv thiab.

Cov kev dag no yog dab tsi?

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

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

Hom "char" no yog dab tsi, hom clown yog dab tsi? Peb tsis xav tau cov ntawd ... Vim nws ua txuj ua ib qho yooj yim char, txawm tias nws yog nyob rau hauv quotes. Thiab nws txawv ntawm ib txwm char, uas yog tsis muaj quotes, nyob rau hauv hais tias nws outputs tsuas yog thawj byte ntawm cov hlua sawv cev, thaum ib txwm char outputs thawj cim. Nyob rau hauv peb cov ntaub ntawv, thawj tus cim yog tsab ntawv P, uas nyob rau hauv lub unicode sawv cev yuav siv sij hawm txog 2 bytes, raws li pov thawj los ntawm converting qhov tshwm sim rau hom bytea. Thiab hom "char" tsuas yog siv thawj byte ntawm tus sawv cev unicode no. Tom qab ntawd vim li cas hom no xav tau? Cov ntaub ntawv PostgreSQL hais tias qhov no yog hom tshwj xeeb siv rau kev xav tau tshwj xeeb. Yog li peb tsis zoo li xav tau. Tab sis saib rau hauv nws ob lub qhov muag thiab koj yuav tsis yuam kev thaum koj ntsib nws nrog nws tus cwj pwm tshwj xeeb.

Ntxiv qhov chaw. Tawm ntawm qhov pom, tawm ntawm lub siab

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

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

Ua tib zoo saib ntawm qhov piv txwv tau muab. Kuv tshwj xeeb hloov tag nrho cov txiaj ntsig rau hom bytea kom nws pom meej tias muaj dab tsi nyob ntawd. Qhov chaw nyob qhov twg tom qab casting rau varchar(6)? Cov ntaub ntawv succinctly hais tias: "Thaum muab tus nqi ntawm tus cwj pwm mus rau lwm hom cim, trailing whitespace yog pov tseg." Qhov kev tsis nyiam no yuav tsum nco ntsoov. Thiab nco ntsoov tias yog hais tias ib txoj hlua tsis tu ncua raug nrum ncaj qha rau hom varchar(6), qhov chaw txuas ntxiv tau khaws cia. Tej txujci zoo li no.

Cov ntaub ntawv thib peb. json/jsonb

JSON yog ib qho qauv cais uas ua rau nws tus kheej lub neej. Yog li ntawd, nws cov chaw thiab cov ntawm PostgreSQL txawv me ntsis. Nov yog piv txwv.

Johnson thiab Johnson. xav tias qhov txawv

SELECT 'null'::jsonb IS NULL

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

Qhov tshaj plaws yog tias JSON muaj nws tus kheej null qhov chaw, uas tsis yog qhov analogue ntawm NULL hauv PostgreSQL. Nyob rau tib lub sijhawm, JSON cov khoom nws tus kheej kuj yuav muaj tus nqi NULL, yog li cov lus qhia SELECT null::jsonb YOG NULL (ceeb toom qhov tsis muaj ib nqe lus) yuav rov qab muaj tseeb lub sijhawm no.

Ib tsab ntawv hloov txhua yam

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

Qhov tseeb yog tias json thiab jsonb yog cov qauv sib txawv kiag li. Hauv json, cov khoom raug khaws cia zoo li yog, thiab hauv jsonb nws twb tau muab khaws cia rau hauv daim ntawv ntawm parsed, indexed qauv. Tias yog vim li cas nyob rau hauv rooj plaub thib ob, tus nqi ntawm cov khoom los ntawm qhov tseem ceeb 1 tau hloov los ntawm [1, 2, 3] mus rau [7, 8, 9], uas tuaj rau hauv cov qauv ntawm qhov kawg nrog tib tus yuam sij.

Tsis txhob haus dej ntawm koj lub ntsej muag

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

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

PostgreSQL hauv nws qhov kev siv JSONB hloov pauv cov qauv ntawm cov lej tiag, coj lawv mus rau daim ntawv classical. Qhov no tsis tshwm sim rau hom JSON. Ib qho txawv me ntsis, tab sis nws yog.

Cov ntaub ntawv naj npawb plaub. date/time/timestamp

Kuj tseem muaj qee qhov tsis txaus ntseeg nrog hom hnub / sijhawm. Cia peb saib lawv. Cia kuv ua qhov tshwj xeeb tam sim ntawd tias qee qhov kev coj cwj pwm ua kom pom tseeb yog tias koj nkag siab zoo txog qhov tseem ceeb ntawm kev ua haujlwm nrog lub sijhawm. Tab sis qhov no kuj yog ib lub ntsiab lus rau ib tsab xov xwm cais.

kuv li koj tsis nkag siab

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

Nws yuav zoo li tias dab tsi yog incomprehensible ntawm no? Tab sis cov ntaub ntawv tseem tsis nkag siab qhov peb muab tso rau hauv thawj qhov chaw ntawm no - xyoo lossis hnub? Thiab nws txiav txim siab tias nws yog Lub Ib Hlis 99, 2008, uas ua rau nws lub siab. Feem ntau hais lus, thaum xa cov hnub hauv cov ntawv nyeem, koj yuav tsum tau ua tib zoo xyuas seb qhov chaw khaws ntaub ntawv tau lees paub lawv li cas (tshwj xeeb, txheeb xyuas cov hnub qub tsis nrog SHOW datestyle cov lus txib), vim qhov tsis meej pem hauv qhov teeb meem no tuaj yeem kim heev.

Koj tau qhov no los ntawm qhov twg?

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

Vim li cas lub database tsis tuaj yeem nkag siab lub sijhawm qhia meej? Vim hais tias lub sij hawm cheeb tsam tsis muaj ib tug luv luv, tab sis ib tug tag nrho lub npe, uas ua rau kev nkag siab tsuas yog nyob rau hauv lub ntsiab lus teb ntawm ib hnub, txij li thaum nws yuav siv sij hawm mus rau hauv tus account lub keeb kwm ntawm lub sij hawm cheeb tsam hloov, thiab nws tsis ua hauj lwm yam tsis muaj hnub. Thiab cov lus ntawm lub sij hawm kab lus nug - dab tsi yog tus programmer tiag tiag? Yog li ntawd, txhua yam yog qhov tseeb ntawm no, yog tias koj saib nws.

Nws yog dab tsi?

Xav txog qhov xwm txheej. Koj muaj ib daim teb hauv koj lub rooj nrog hom timestamptz. Koj xav index nws. Tab sis koj nkag siab tias kev tsim qhov ntsuas ntawm daim teb no tsis yog ib txwm tsim nyog vim nws qhov kev xaiv siab (yuav luag txhua qhov txiaj ntsig ntawm hom no yuav txawv). Yog li koj txiav txim siab los txo qhov kev xaiv ntawm qhov Performance index los ntawm casting hom mus rau hnub tim. Thiab koj tau txais ib qho surprise:

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

Dab tsi yog qhov teeb meem? Qhov tseeb yog tias yuav nrum lub timestamptz hom mus rau hom hnub, tus nqi ntawm TimeZone system parameter yog siv, uas ua rau hom hloov dua siab tshiab ua haujlwm nyob ntawm qhov kev cai parameter, i.e. tsis hloov pauv. Cov haujlwm zoo li no tsis raug tso cai hauv qhov ntsuas. Nyob rau hauv cov ntaub ntawv no, koj yuav tsum qhia meej meej nyob rau hauv lub sij hawm cheeb tsam lub hom cam khwb cia yog ua.

Thaum tam sim no tsis yog tam sim no kiag li

Peb tau siv tam sim no () rov qab hnub tim / lub sijhawm tam sim no, suav nrog lub sijhawm. Tab sis saib cov lus nug hauv qab no:

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;

Hnub tim/lub sij hawm rov qab zoo li qub txawm tias lub sijhawm dhau mus npaum li cas txij li qhov kev thov dhau los! Dab tsi yog qhov teeb meem? Qhov tseeb yog tias tam sim no () tsis yog lub sijhawm tam sim no, tab sis lub sijhawm pib ntawm kev hloov pauv tam sim no. Yog li ntawd, nws tsis hloov nyob rau hauv kev sib pauv. Ib qho lus nug tawm sab nraud ntawm qhov kev lag luam yog qhwv hauv kev lag luam implicitly, uas yog vim li cas peb tsis pom tias lub sij hawm rov qab los ntawm ib qho yooj yim SELECT tam sim no(); qhov tseeb, tsis yog tam sim no ... Yog tias koj xav tau lub sijhawm tam sim no ncaj ncees, koj yuav tsum siv lub sijhawm clock_timestamp() ua haujlwm.

Cov ntaub ntawv naj npawb tsib. me ntsis

Coj txawv me ntsis

SELECT '111'::bit(4)

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

Sab twg yuav tsum tau ntxiv cov khoom nyob rau hauv rooj plaub ntawm hom extension? Zoo li nyob rau sab laug. Tab sis tsuas yog lub hauv paus muaj kev xav txawv ntawm qhov teeb meem no. Ceev faj: yog tias tus lej ntawm tus lej tsis sib xws thaum casting ib hom, koj yuav tsis tau qhov koj xav tau. Qhov no siv tau rau ob qho tib si ntxiv cov khoom mus rau sab xis thiab trimming khoom. Tseem nyob sab xis...

Cov ntaub ntawv naj npawb rau. Arrays

Txawm NULL tsis tua

SELECT ARRAY[1, 2] || NULL

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

Raws li cov neeg ib txwm tsa ntawm SQL, peb cia siab tias qhov tshwm sim ntawm qhov kev qhia no yuav NULL. Tab sis nws tsis nyob ntawd. Ib qho array raug xa rov qab. Vim li cas? Vim hais tias nyob rau hauv cov ntaub ntawv no lub hauv paus nrum NULL mus rau ib tug integer array thiab implicitly hu rau lub array_cat muaj nuj nqi. Tab sis nws tseem tsis tau paub meej vim li cas qhov "array miv" tsis rov pib dua lub array. Tus cwj pwm no tsuas yog yuav tsum nco ntsoov.

Cov ntsiab lus. Muaj ntau yam txawv txawv. Feem ntau ntawm lawv, ntawm chav kawm, tsis yog qhov tseem ceeb heev los tham txog kev coj cwj pwm tsis tsim nyog. Thiab lwm tus tau piav qhia los ntawm kev yooj yim ntawm kev siv lossis qhov zaus ntawm lawv qhov kev siv tau hauv qee qhov xwm txheej. Tab sis tib lub sijhawm, muaj ntau yam surprises. Yog li ntawd, koj yuav tsum paub txog lawv. Yog tias koj pom lwm yam txawv txawv lossis txawv txawv hauv tus cwj pwm ntawm txhua hom, sau rau hauv cov lus, Kuv yuav zoo siab ntxiv rau cov ntawv sau muaj nyob rau ntawm lawv.

Tau qhov twg los: www.hab.com

Ntxiv ib saib