🥇 ʻAno kānalua

ʻAʻohe mea kānalua i ko lākou helehelena. Eia kekahi, ua kamaʻāina lākou iā ʻoe a no ka manawa lōʻihi. Akā aia wale nō a nānā ʻoe iā lākou. ʻO kēia kahi e hōʻike ai lākou i ko lākou ʻano insidious, hana ʻokoʻa loa ma mua o kāu i manaʻo ai. A i kekahi manawa hana lākou i kahi mea e kū ai kou lauoho - no ka laʻana, nalowale lākou i ka ʻikepili huna i hāʻawi ʻia iā lākou. Ke hoʻohālikelike nei ʻoe iā lākou, ʻōlelo lākou ʻaʻole lākou i ʻike kekahi i kekahi, ʻoiai ma ka malu e hana ikaika lākou ma lalo o ka puʻupuʻu hoʻokahi. ʻO ka manawa kēia e lawe ai iā lākou i ka wai maʻemaʻe. E hana pū kākou me kēia mau ʻano kānalua.

ʻO ka paʻi ʻana i ka ʻikepili ma PostgreSQL, no kāna mau loina āpau, ke hōʻike nei i kekahi mau mea kupanaha loa. Ma kēia ʻatikala e hoʻāʻo mākou e wehewehe i kekahi o kā lākou quirks, hoʻomaopopo i ke kumu o kā lākou ʻano ʻano ʻē a hoʻomaopopo i ke ʻano o ka holo ʻana i nā pilikia i ka hana o kēlā me kēia lā. No ka haʻi ʻana i ka ʻoiaʻiʻo, ua hōʻuluʻulu au i kēia ʻatikala ma ke ʻano he puke kuhikuhi noʻu iho, he puke kuhikuhi i hiki ke maʻalahi i nā hihia hoʻopaʻapaʻa. No laila, e hoʻopiha ʻia i ka wā e ʻike ʻia ai nā mea kupanaha hou mai nā ʻano kānalua. No laila, e hele kāua, e ka poʻe mākaʻikaʻi ʻikepili luhi ʻole!

Helu helu ekahi. ʻoiaʻiʻo / pālua ka pololei / helu / kālā

Me he mea lā ʻo nā ʻano helu ka pilikia liʻiliʻi loa e pili ana i nā haʻalulu i ka hana. Akā, ʻaʻohe mea pehea. No laila e hoʻomaka kākou me lākou. No laila…

Poina i ka helu ʻana

SELECT 0.1::real = 0.1

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

He aha ka pilikia? ʻO ka pilikia, ua hoʻololi ʻo PostgreSQL i ka 0.1 mau ʻole i hoʻopaʻa ʻia a hoʻāʻo e hoʻohālikelike me 0.1 o ke ʻano maoli. A he mau manaʻo ʻokoʻa loa kēia! ʻO ka manaʻo e hōʻike i nā helu maoli i ka hoʻomanaʻo mīkini. No ka mea ʻaʻole hiki ke hōʻike ʻia ka 0.1 ma ke ʻano he hakina binary palena (ʻo ia ka 0.0(0011) ma ka binary), ʻokoʻa nā helu me nā ʻāpana ʻokoʻa, no laila ʻaʻole like lākou. Ma ka ʻōlelo maʻamau, he kumuhana kēia no kahi ʻatikala ʻokoʻa; ʻAʻole wau e kākau i nā kikoʻī hou aku ma aneʻi.

No hea mai ka hewa?

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

Ua ʻike ka poʻe he nui ka PostgreSQL e ʻae i ka notation hana no ka hoʻolei ʻana. ʻO ia hoʻi, hiki iā ʻoe ke kākau ʻaʻole wale i ka 1::int, akā i ka int(1), ʻo ia ka mea like. ʻAʻole naʻe no nā ʻano i loaʻa nā inoa he mau huaʻōlelo! No laila, inā makemake ʻoe e hoʻolei i kahi waiwai helu e pālua i ke ʻano pololei ma ke ʻano hana, e hoʻohana i ka inoa o kēia ʻano float8, ʻo ia hoʻi, SELECT float8(1).

He aha ka mea nui ma mua o ka infinity?

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

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

E nana i kona ano! ʻIke ʻia aia kekahi mea ʻoi aku ka nui ma mua o ka infinity, a ʻo ia ʻo NaN! I ka manawa like, nānā ka palapala PostgreSQL iā mākou me nā maka pono a ʻōlelo ʻo NaN ʻoi aku ka nui ma mua o nā helu ʻē aʻe, a no laila, pau ʻole. He ʻoiaʻiʻo nō hoʻi ka ʻokoʻa no -NaN. Aloha mai e ka poe aloha makemakika! Akā, pono mākou e hoʻomanaʻo e hana kēia mau mea a pau i ka pōʻaiapili o nā helu maoli.

Poʻai maka

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

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

ʻO kekahi aloha i manaʻo ʻole ʻia mai ke kumu. Eia hou, e hoʻomanaʻo he ʻokoʻa nā hopena pōʻai ʻē aʻe o ka pololei pālua a me nā ʻano helu. No ka helu - ke ala maʻamau, ke hoʻopuni ʻia ka 0,5, a no ka pololei pālua - ua hoʻopuni ʻia ʻo 0,5 i ka integer kokoke loa.

He mea kūikawā ke kālā

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

Wahi a PostgreSQL, ʻaʻole helu maoli ke kālā. Wahi a kekahi poʻe. Pono mākou e hoʻomanaʻo i ka hoʻolei ʻana i ke ʻano kālā e hiki ke hoʻolei wale ʻia i ke ʻano helu, e like me ke ʻano helu helu hiki ke hoʻolei ʻia i ke ʻano kālā. Akā i kēia manawa hiki iā ʻoe ke pāʻani me ia e like me ka makemake o kou puʻuwai. Akā, ʻaʻole ia e like ke kālā.

Liʻiliʻi a me ke kaʻina hana

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

ʻAʻole makemake ʻo PostgreSQL e hoʻopau manawa i nā mea liʻiliʻi. He aha kēia mau kaʻina ma muli o ka smallint? int, aole emi! No laila, i ka wā e ho'āʻo ai e hoʻokō i ka nīnau ma luna, hoʻāʻo ka ʻikepili e hoʻolei i ka smallint i kekahi ʻano integer ʻē aʻe, a ʻike ʻo ia he nui nā pahu like. ʻO wai ka pahu e koho ai? ʻAʻole hiki iā ia ke hoʻoholo i kēia, a no laila hāʻule me ka hewa.

Helu waihona ʻelua. "char"/char/varchar/text

Aia kekahi mau mea ʻokoʻa i nā ʻano ʻano. E ʻike pū kākou iā lākou.

He aha ke ʻano o kēia mau mea hoʻopunipuni?

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

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

He aha kēia ʻano "char", he aha kēia ʻano clown? ʻAʻole pono mākou i kēlā ... No ka mea, hoʻohālike ia he char maʻamau, ʻoiai aia ma nā ʻōlelo. A he ʻokoʻa ia mai ka char maʻamau, ʻaʻohe huaʻōlelo, ʻo ia ka mea e hoʻopuka wale i ka byte mua o ka hōʻike string, aʻo ka char maʻamau e hoʻopuka i ke ʻano mua. I kā mākou hihia, ʻo ka hua mua ka leka P, ka mea ma ka hōʻike unicode e lawe i 2 bytes, e like me ka hōʻike ʻana ma ka hoʻololi ʻana i ka hopena i ke ʻano bytea. A ʻo ke ʻano "char" e lawe wale i ka byte mua o kēia hōʻike unicode. A laila no ke aha e pono ai kēia ʻano? Wahi a ka palapala PostgreSQL he ʻano kūikawā kēia i hoʻohana ʻia no nā pono kūikawā. No laila ʻaʻole pono mākou. Akā, e nānā i kona mau maka a ʻaʻole ʻoe e kuhihewa ke hālāwai ʻoe me ia me kāna ʻano ʻano kūikawā.

Nā hakahaka keu. Ma waho o ka maka, ma waho o ka manaʻo

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

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

E nānā i ka laʻana i hāʻawi ʻia. Ua hoʻololi kūikawā wau i nā hopena āpau i ke ʻano bytea, i ʻike maopopo ʻia ka mea i laila. ʻAuhea nā hakahaka ma hope o ka hoʻolei ʻana iā varchar(6)? Ua ʻōlelo maʻalahi ka palapala: "I ka hoʻolei ʻana i ka waiwai o ke ʻano i kahi ʻano ʻano ʻē aʻe, e hoʻolei ʻia ke keʻokeʻo ma hope." Pono e hoʻomanaʻo ʻia kēia makemake ʻole. A e hoʻomaopopo inā hoʻolei pololei ʻia kahi kaula i ʻōlelo ʻia i ke ʻano varchar(6), mālama ʻia nā hakahaka. Pela na hana mana.

Helu waihona ʻekolu. json/jsonb

He hale kaʻawale ʻo JSON e ola ana i kona ola ponoʻī. No laila, ʻokoʻa iki kāna mau hui a me nā PostgreSQL. Eia nā laʻana.

Johnson a me Johnson. manaʻo i ka ʻokoʻa

SELECT 'null'::jsonb IS NULL

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

ʻO ka mea ʻo JSON kona ʻano null ponoʻī, ʻaʻole ia ka analogue o NULL ma PostgreSQL. I ka manawa like, hiki i ka JSON pono'ī ke loaʻa i ka waiwai NULL, no laila e hoʻi ʻoiaʻiʻo ka huaʻōlelo SELECT null::jsonb IS NULL (e nānā i ka nele o nā huaʻōlelo hoʻokahi) i kēia manawa.

Hoʻokahi leka hoʻololi i nā mea a pau

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

ʻO ka mea ʻo json a me jsonb nā hale ʻokoʻa loa. Ma json, mālama ʻia ka mea e like me ia, a ma jsonb ua mālama ʻia ia ma ke ʻano o kahi ʻano parsed, i kuhikuhi ʻia. ʻO ia ke kumu ma ka hihia ʻelua, ua hoʻololi ʻia ka waiwai o ka mea ma ke kī 1 mai [1, 2, 3] a i [7, 8, 9], i komo i loko o ka hale ma ka hope loa me ke kī like.

Mai inu i ka wai mai kou alo

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

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

Hoʻololi ʻo PostgreSQL i kāna hoʻokō JSONB i ke ʻano o nā helu maoli, e lawe ana iā lākou i ke ʻano maʻamau. ʻAʻole hiki kēia no ke ʻano JSON. He mea ʻē aʻe, akā pololei ʻo ia.

Helu waihona ʻehā. lā/manawa/pepa manawa

Aia kekahi mau mea ʻokoʻa me nā ʻano lā/manawa. E nānā kākou iā lākou. E ʻae mai iaʻu e hoʻopaʻa koke i ka maopopo ʻana o kekahi o nā hiʻohiʻona hana inā maopopo ʻoe i ke ʻano o ka hana ʻana me nā ʻāpana manawa. Akā he kumuhana hoʻi kēia no kahi ʻatikala kaʻawale.

ʻAʻole maopopo kaʻu kāu

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

Me he mea lā ka mea hiki ʻole ke hoʻomaopopo ʻia ma ʻaneʻi? Akā ʻaʻole maopopo ka ʻikepili i ka mea a mākou e kau ai ma kahi mua ma ʻaneʻi-ʻo ka makahiki a i ʻole ka lā? A ua hoʻoholo ʻo ia ʻo Ianuali 99, 2008, ka mea e paʻa ai kona manaʻo. ʻO ka ʻōlelo maʻamau, i ka wā e hoʻouna ai i nā lā ma ke ʻano kikokikona, pono ʻoe e nānā pono i ka ʻike pololei ʻana o ka waihona iā lākou (ʻo ia hoʻi, ka nānā ʻana i ka palena datestyle me ke kauoha SHOW datestyle), no ka mea hiki ke kumukūʻai nui nā ambiguities i kēia mea.

No hea mai ʻoe i kēia?

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

No ke aha i hiki ʻole ai i ka waihona ke hoʻomaopopo i ka manawa i ʻōlelo ʻia? No ka mea, ʻaʻohe pōkole o ka manawa, akā he inoa piha, ʻo ia ka manaʻo wale nō i ka pōʻaiapili o kahi lā, no ka mea e noʻonoʻo ana i ka mōʻaukala o nā loli manawa, ʻaʻole ia e hana me ka ʻole o ka lā. A ʻo ka huaʻōlelo o ka laina manawa e hoʻāla ai i nā nīnau - he aha ka manaʻo o ka mea polokalamu? No laila, kūpono nā mea a pau ma ʻaneʻi, ke nānā ʻoe.

He aha ka hewa iā ia?

E noʻonoʻo i ke kūlana. Loaʻa iā ʻoe kahi kahua ma kāu pākaukau me ke ʻano timestamptz. Makemake ʻoe e kuhikuhi. Akā ʻike ʻoe ʻaʻole ʻae ʻia ke kūkulu ʻana i kahi kuhikuhi ma kēia kahua ma muli o kāna koho kiʻekiʻe (kokoke nā waiwai āpau o kēia ʻano he ʻokoʻa). No laila hoʻoholo ʻoe e hōʻemi i ke koho ʻana o ka index ma ka hoʻolei ʻana i ke ʻano i kahi lā. A loaʻa iā ʻoe kahi pīhoihoi:

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

He aha ka pilikia? ʻO ka ʻoiaʻiʻo, ʻo ka hoʻolei ʻana i kahi ʻano timestamptz i kahi ʻano lā, hoʻohana ʻia ka waiwai o ka ʻōnaehana ʻōnaehana TimeZone, kahi e hilinaʻi ai ka hana hoʻololi ʻano i kahi ʻāpana maʻamau, ʻo ia. hikiwawe. ʻAʻole ʻae ʻia ia mau hana i ka index. I kēia hihia, pono ʻoe e hōʻike maopopo i kahi manawa i hana ʻia ai ke ʻano hoʻolei.

I kēia manawa ʻaʻole i kēia manawa

Ua maʻa mākou i kēia manawa () e hoʻihoʻi i ka lā / manawa o kēia manawa, me ka noʻonoʻo ʻana i ka palena manawa. Akā e nānā i kēia mau nīnau:

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;

Hoʻihoʻi ʻia ka lā/manawa like me ka nui o ka manawa i hala mai ka noi mua! He aha ka pilikia? ʻO ka mea ʻoiaʻiʻo, ʻaʻole kēia manawa () ka manawa o kēia manawa, akā ʻo ka manawa hoʻomaka o ke kālepa o kēia manawa. No laila, ʻaʻole ia e loli i loko o ke kālepa. ʻO kēlā me kēia nīnau i hoʻokuʻu ʻia ma waho o ke ʻano o kahi kālepa i hoʻopaʻa ʻia i loko o kahi kālepa implicitly, ʻo ia ke kumu ʻaʻole mākou e ʻike i ka manawa i hoʻihoʻi ʻia e kahi SELECT maʻalahi i kēia manawa (); ʻoiaʻiʻo, ʻaʻole ka mea i kēia manawa ... Inā makemake ʻoe e loaʻa kahi manawa kūpono i kēia manawa, pono ʻoe e hoʻohana i ka hana clock_timestamp ().

Helu waihona ʻelima. bit

Kupanaha iki

SELECT '111'::bit(4)

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

ʻO ka ʻaoʻao hea e hoʻohui ʻia nā bits inā loaʻa ke ʻano hoʻonui? Me he mea lā aia ma ka hema. Akā ʻo ke kumu wale nō ka manaʻo ʻokoʻa no kēia mea. E akahele: inā ʻaʻole like ka helu o nā huahelu i ka hoʻolei ʻana i kahi ʻano, ʻaʻole e loaʻa iā ʻoe ka mea āu i makemake ai. Pili kēia i ka hoʻohui ʻana i nā ʻāpana ma ka ʻākau a me ka ʻoki ʻana. Aia nō ma ka ʻākau...

Helu waihona ʻeono. Nā pūʻulu

ʻAʻole i puhi ʻo NULL

SELECT ARRAY[1, 2] || NULL

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

E like me ka poʻe maʻamau i hāpai ʻia ma SQL, manaʻo mākou he NULL ka hopena o kēia ʻōlelo. ʻAʻole naʻe i laila. Hoʻihoʻi ʻia kahi laha. No ke aha mai? No ka mea, ma kēia hihia, hoʻolei ka base i ka NULL i kahi ʻāpana integer a kāhea i ka hana array_cat. Akā ʻaʻole maopopo ke kumu i hoʻonohonoho ʻole ai kēia "cat array" i ka array. Pono e hoʻomanaʻo wale ʻia kēia ʻano.

Hōʻuluʻulu. Nui nā mea ʻē. ʻO ka hapa nui o lākou, ʻoiaʻiʻo, ʻaʻole koʻikoʻi e kamaʻilio e pili ana i ka hana pono ʻole. A wehewehe ʻia nā mea ʻē aʻe e ka maʻalahi o ka hoʻohana ʻana a i ʻole ke alapine o kā lākou hoʻohana ʻana i kekahi mau kūlana. Akā i ka manawa like, nui nā mea kupanaha. No laila, pono ʻoe e ʻike e pili ana iā lākou. Inā ʻike ʻoe i kekahi mea ʻē aʻe a ʻokoʻa paha i ka ʻano o kēlā me kēia ʻano, e kākau i nā ʻōlelo, e hauʻoli wau e hoʻohui i nā dosiers i loaʻa iā lākou.

Source: www.habr.com

ʻAno kānalua