🥇 Nau'ukan tuhuma

Babu wani abin tuhuma game da bayyanar su. Bugu da ƙari, har ma suna da alama sun saba muku da kyau kuma na dogon lokaci. Amma wannan kawai sai kun duba su. Anan ne suke nuna dabi'ar yaudararsu, suna aiki da bambanci fiye da yadda kuke zato. Kuma wani lokacin suna yin wani abu da ke sa gashin ku ya tsaya a ƙarshe - alal misali, suna rasa bayanan sirri da aka ba su. Idan ka fuskanci su sai su ce ba su san juna ba, duk da cewa a cikin inuwa suna aiki tuƙuru a ƙarƙashin hular guda ɗaya. Lokaci ya yi da za a kawo su cikin ruwa mai tsabta. Bari kuma mu magance waɗannan nau'ikan zato.

Buga bayanai a cikin PostgreSQL, ga dukkan dabaru, wani lokacin yana gabatar da abubuwan ban mamaki. A cikin wannan labarin za mu yi ƙoƙari mu bayyana wasu daga cikin quirks, fahimtar dalilin da ya sa su m hali da kuma fahimtar yadda ba za su fuskanci matsaloli a cikin rayuwar yau da kullum. Don faɗi gaskiya, na haɗa wannan labarin kuma a matsayin wani nau'in littafin tunani don kaina, littafin tunani wanda za'a iya magana da shi cikin sauƙi a lokuta masu rikitarwa. Saboda haka, za a sake cika shi yayin da aka gano sababbin abubuwan ban mamaki daga nau'ikan da ake tuhuma. Don haka, mu tafi, ya ku masu bin diddigin bayanai mara gajiya!

Dossier lamba daya. ainihin / biyu daidaici / lamba / kudi

Da alama nau'ikan lambobi sune mafi ƙarancin matsala dangane da abubuwan mamaki a cikin ɗabi'a. Amma ko yaya abin yake. Don haka bari mu fara da su. Don haka…

Manta yadda ake kirga

SELECT 0.1::real = 0.1

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

Akwai matsala? Matsalar ita ce PostgreSQL tana jujjuya 0.1 ɗin da ba a buga ba zuwa daidaitaccen ninki biyu kuma yana ƙoƙarin kwatanta shi da 0.1 na ainihin nau'in. Kuma waɗannan ma'anoni ne mabanbanta! Manufar ita ce wakiltar lambobi na ainihi a ƙwaƙwalwar na'ura. Tun da 0.1 ba za a iya wakilta a matsayin iyakataccen juzu'i na binary (zai zama 0.0 (0011) a cikin binary), lambobi tare da zurfin bit daban-daban za su bambanta, saboda haka sakamakon cewa ba daidai ba ne. Gabaɗaya magana, wannan batu ne na wani labarin dabam; Ba zan yi ƙarin bayani a nan ba.

Daga ina kuskuren ya fito?

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

Mutane da yawa sun san cewa PostgreSQL yana ba da damar bayanin aiki don nau'in simintin gyare-gyare. Wato za ku iya rubuta ba kawai 1 ::int ba, har ma da int(1), wanda zai yi daidai. Amma ba don nau'ikan da sunayensu ya ƙunshi kalmomi da yawa ba! Don haka, idan kuna son jefa ƙimar lamba zuwa nau'in madaidaicin nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau’in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau’in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in nau'in kima da za a iya jefawa a cikin sigar aiki, yi amfani da laƙabin wannan nau’in float8, wato, SELECT float8(1).

Menene girma fiye da rashin iyaka?

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

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

Kalli yadda abin yake! Ya zama akwai wani abu mafi girma fiye da rashin iyaka, kuma NaN ne! A lokaci guda, takardun PostgreSQL suna kallon mu da idanu masu gaskiya kuma suna da'awar cewa NaN ya fi kowane lamba a fili, kuma, saboda haka, rashin iyaka. Akasin haka ma gaskiya ne ga -NaN. Sannu, masoya lissafi! Amma dole ne mu tuna cewa duk wannan yana aiki a cikin mahallin lambobi na ainihi.

Zagayen ido

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

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

Wani gaisuwar bazata daga tushe. Bugu da ƙari, tuna cewa daidaitattun ninki biyu da nau'ikan lambobi suna da tasirin zagaye daban-daban. Don lambobi - na yau da kullun, lokacin da aka tattara 0,5, kuma don daidaitattun ninki biyu - 0,5 an zagaye zuwa madaidaicin madaidaicin lamba.

Kudi wani abu ne na musamman

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

A cewar PostgreSQL, kuɗi ba adadi ba ne na gaske. A cewar wasu mutane ma. Muna bukatar mu tuna cewa jefa nau'in kuɗi yana yiwuwa ne kawai zuwa nau'in lambobi, kamar yadda kawai nau'in lambobi kawai za a iya jefawa zuwa nau'in kuɗi. Amma yanzu za ku iya wasa da shi yadda zuciyar ku ke so. Amma ba zai zama kuɗi ɗaya ba.

Smallint da jerin tsarawa

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 ba ya son ɓata lokaci akan ƙananan abubuwa. Menene waɗannan jeri bisa ga ƙarami? int, ba kasa! Don haka, lokacin ƙoƙarin aiwatar da tambayar da ke sama, ma'aunin bayanai yana ƙoƙarin jefa ƙarami zuwa wani nau'in lamba, kuma yana ganin cewa za a iya samun irin waɗannan simintin gyare-gyare da yawa. Wanne simintin zaɓe? Ba za ta iya yanke shawarar wannan ba, don haka ta fashe da kuskure.

Fayil mai lamba biyu. "char"/char/varchar/rubutu

Hakanan akwai wasu abubuwan ban mamaki a cikin nau'ikan halaye. Mu ma mu san su.

Wadanne irin dabaru ne wadannan?

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

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

Wane irin “char” ne wannan, wane irin wawa ne wannan? Ba ma buƙatar waɗannan ... Domin yana yin kamar ya zama char na yau da kullum, ko da yake yana cikin ƙididdiga. Kuma ya bambanta da char na yau da kullun, wanda ba shi da fa'ida, ta yadda yake fitar da byte na farko na wakilcin kirtani, yayin da char na yau da kullun ke fitar da halin farko. A cikin yanayinmu, harafin farko shine harafin P, wanda a cikin wakilcin unicode ya ɗauki 2 bytes, kamar yadda aka tabbatar ta hanyar canza sakamakon zuwa nau'in bytea. Kuma nau'in "char" yana ɗaukar byte na farko na wannan wakilcin unicode. To me yasa ake bukatar irin wannan? Takaddun PostgreSQL sun ce wannan nau'i ne na musamman da ake amfani da shi don buƙatu na musamman. Don haka da wuya mu bukaci hakan. Amma ku kalli idanunsa ba za ku yi kuskure ba idan kun haɗu da shi da halayensa na musamman.

Karin wurare. Daga gani, daga hankali

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

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

Dubi misalin da aka bayar. Na musamman na canza duk sakamakon zuwa nau'in byte, don a iya ganin abin da ke wurin a fili. Ina wuraren da ke biyo bayan jefawa zuwa varchar(6)? Takardun ya faɗi a takaice: "Lokacin da aka jefa ƙimar hali zuwa wani nau'in halayen, ana zubar da farar sararin samaniya." Dole ne a tuna da wannan ƙiyayya. Kuma lura cewa idan an jefa madaidaicin kirtani kai tsaye zuwa nau'in varchar(6), ana adana wuraren da ke biyo baya. Waɗannan su ne mu'ujizai.

Fayil mai lamba uku. json/jsonb

JSON wani tsari ne daban wanda ke rayuwar kansa. Saboda haka, abubuwan sa da na PostgreSQL sun ɗan bambanta. Ga misalai.

Johnson da Johnson. ji bambanci

SELECT 'null'::jsonb IS NULL

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

Abun shine JSON yana da nata mahallin mara amfani, wanda ba shine analogue na NULL a PostgreSQL ba. A lokaci guda, abin JSON da kansa yana iya samun darajar NULL, don haka kalmar SELECT null :: jsonb IS NULL (lura rashin fa'ida ɗaya) zai dawo gaskiya a wannan lokacin.

Harafi ɗaya yana canza komai

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

Abun shine json da jsonb gaba daya sifofi ne. A cikin json, ana adana abu kamar yadda yake, kuma a cikin jsonb an riga an adana shi a cikin nau'in tsari mai ƙididdiga. Shi ya sa a cikin yanayi na biyu, an maye gurbin darajar abu da maɓalli 1 daga [1, 2, 3] zuwa [7, 8, 9], wanda ya zo cikin tsari a ƙarshe da maɓalli iri ɗaya.

Kada ka sha ruwa daga fuskarka

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

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

PostgreSQL a cikin aiwatar da JSONB ɗin sa yana canza tsarin lambobi na ainihi, yana kawo su cikin sigar gargajiya. Wannan baya faruwa ga nau'in JSON. Dan ban mamaki, amma yana da gaskiya.

Fayil mai lamba hudu. kwanan wata/lokaci/tambarin lokaci

Hakanan akwai wasu abubuwan ban mamaki tare da nau'ikan kwanan wata/lokaci. Mu duba su. Bari in yi ajiyar wuri nan da nan cewa wasu fasalolin ɗabi'a sun bayyana a sarari idan kun fahimci ainihin ainihin aiki tare da yankuna lokaci. Amma wannan kuma jigo ne na wani labarin dabam.

Nawa naku ban gane ba

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

Zai yi kama da abin da ba a fahimta a nan? Amma har yanzu bayanan ba su fahimci abin da muka sanya a farko a nan ba - shekara ko rana? Kuma ta yanke shawarar cewa a ranar 99 ga Janairu, 2008 ne, wanda ya busa mata zuciya. Gabaɗaya magana, lokacin watsa kwanan wata a cikin tsarin rubutu, kuna buƙatar bincika sosai yadda daidaitattun bayanai suka gane su (musamman, bincika sigar kwanan wata tare da umarnin kwanan wata na SHOW), tunda shubuha a cikin wannan lamari na iya zama tsada sosai.

Daga ina kuka samo wannan?

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

Me yasa ma'ajin bayanai ba za su iya fahimtar takamaiman lokacin da aka kayyade ba? Domin yankin lokaci ba shi da raguwa, amma cikakken suna, wanda ke da ma'ana kawai a cikin mahallin kwanan wata, tun da yake la'akari da tarihin canjin lokaci, kuma ba ya aiki ba tare da kwanan wata ba. Kuma ainihin ma’anar layin lokaci yana haifar da tambayoyi - menene ainihin ma’aikacin shirin yake nufi? Saboda haka, komai yana da ma'ana a nan, idan kun kalle shi.

Me ke damunsa?

Ka yi tunanin yanayin. Kuna da filin a cikin teburin ku tare da nau'in timestamptz. Kuna so ku nuna shi. Amma ka fahimci cewa gina index a kan wannan filin ba ko da yaushe barata saboda da high selectivity (kusan duk dabi'u na irin wannan zai zama na musamman). Don haka ka yanke shawarar rage zaɓin fihirisar ta hanyar jefa nau'in zuwa kwanan wata. Kuma kuna samun abin mamaki:

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

Akwai matsala? Gaskiyar ita ce, don jefa nau'in timestamptz zuwa nau'in kwanan wata, ana amfani da ƙimar tsarin tsarin TimeZone, wanda ke sa nau'in canjin nau'in ya dogara da ma'auni na al'ada, watau. m. Ba a yarda da irin waɗannan ayyuka a cikin fihirisar ba. A wannan yanayin, dole ne ka nuna a sarari a cikin wane yanki na lokaci nau'in simintin gyaran kafa.

Lokacin har yanzu ba ma yanzu ba

Mun saba da yanzu () dawo da kwanan wata/lokaci na yanzu, la'akari da yankin lokaci. Amma dubi wadannan tambayoyin:

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;

Ana mayar da kwanan wata/lokaci iri ɗaya komai nawa lokaci ya wuce tun daga buƙatar da ta gabata! Akwai matsala? Gaskiyar ita ce yanzu () ba shine lokacin yanzu ba, amma farkon lokacin ma'amala na yanzu. Saboda haka, ba ya canzawa a cikin ma'amala. Duk wata tambaya da aka ƙaddamar a wajen iyakokin ciniki ana lulluɓe ta cikin wata ma'amala a fakaice, wanda shine dalilin da ya sa ba mu lura cewa lokacin da SELECT mai sauƙi ya dawo (); a gaskiya, ba na yanzu ba... Idan kuna son samun lokacin gaskiya na yanzu, kuna buƙatar amfani da aikin clock_timestamp().

Fayil mai lamba biyar. bit

Ban mamaki kadan

SELECT '111'::bit(4)

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

Wanne gefen ya kamata a ƙara raƙuman ruwa idan akwai nau'in tsawo? Da alama yana hagu. Amma kawai tushe yana da ra'ayi daban-daban akan wannan batu. Yi hankali: idan adadin lambobi bai yi daidai ba lokacin yin wani nau'i, ba za ku sami abin da kuke so ba. Wannan ya shafi duka biyun ƙara ragowa zuwa dama da datsa ragowa. Hakanan a hannun dama ...

Fayil mai lamba shida. Tsare-tsare

Ko NULL ba ta yi harbi ba

SELECT ARRAY[1, 2] || NULL

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

Kamar yadda mutane na yau da kullun suka taso akan SQL, muna tsammanin sakamakon wannan magana ya zama NULL. Amma ba a can. Ana mayar da tsararru. Me yasa? Domin a wannan yanayin tushe yana jefa NULL zuwa tsararrun lamba kuma yana kiran aikin array_cat a fakaice. Amma har yanzu ba a san dalilin da yasa wannan "tsarin cat" ba ya sake saita tsararrun. Wannan halin kuma yana buƙatar tunawa kawai.

Takaita. Akwai abubuwa masu ban mamaki da yawa. Yawancinsu, ba shakka, ba su da mahimmanci har suna magana game da halayen da ba su dace ba a fili. Wasu kuma ana bayyana su ta hanyar sauƙin amfani ko yawan amfani da su a wasu yanayi. Amma a lokaci guda, akwai abubuwan mamaki da yawa. Don haka, kuna buƙatar sanin game da su. Idan kun sami wani abu mai ban mamaki ko sabon abu a cikin halayen kowane nau'in, rubuta a cikin sharhi, zan yi farin cikin ƙarawa zuwa bayanan da ke akwai akan su.

source: www.habr.com

Nau'ukan tuhuma