🥇Įtartini tipai

Jų išvaizdoje nėra nieko įtartino. Be to, jie jums net atrodo gerai ir ilgai pažįstami. Bet tai tik tol, kol juos patikrinsite. Čia jie parodo savo klastingą prigimtį, dirbdami visiškai kitaip, nei tikėjotės. O kartais jie daro tai, dėl ko tau stoja plaukai – pavyzdžiui, pameta jiems patikėtus slaptus duomenis. Kai susiduri su jais, jie teigia, kad vienas kito nepažįsta, nors šešėlyje sunkiai dirba po vienu gaubtu. Laikas pagaliau nunešti juos į švarų vandenį. Taip pat susidorokime su šiais įtartinais tipais.

Duomenų įvedimas PostgreSQL, nepaisant jo logikos, kartais pateikia labai keistų staigmenų. Šiame straipsnyje mes stengsimės išsiaiškinti kai kurias jų keistenybes, suprasti jų keisto elgesio priežastis ir suprasti, kaip kasdienėje praktikoje nesusidurti su problemomis. Tiesą pasakius, šį straipsnį sukūriau ir kaip savotišką žinyną sau, žinyną, į kurį būtų galima lengvai kreiptis ginčytinais atvejais. Todėl jis bus papildytas, kai bus atrasta naujų netikėtumų iš įtartinų tipų. Taigi, eikime, nenuilstantys duomenų bazių sekėjai!

Dokumentacija numeris vienas. tikras / dvigubas tikslumas / skaitmeninis / pinigai

Atrodytų, kad skaitmeniniai tipai yra mažiausiai problemiški, kalbant apie netikėtumus elgesyje. Bet kad ir kaip būtų. Taigi pradėkime nuo jų. Taigi…

Pamiršau, kaip skaičiuoti

SELECT 0.1::real = 0.1

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

Kas nutiko? Problema ta, kad PostgreSQL konvertuoja neįrašytą konstantą 0.1 į dvigubą tikslumą ir bando ją palyginti su tikrojo tipo 0.1. Ir tai yra visiškai skirtingos reikšmės! Idėja yra pateikti realius skaičius mašinos atmintyje. Kadangi 0.1 negalima pavaizduoti kaip baigtinės dvejetainės trupmenos (dvejetainėje būtų 0.0(0011), skaičiai su skirtingu bitų gyliu bus skirtingi, todėl jie nėra lygūs. Paprastai kalbant, tai atskiro straipsnio tema, plačiau čia nerašysiu.

Iš kur atsiranda klaida?

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

Daugelis žmonių žino, kad „PostgreSQL“ leidžia funkcionaliai žymėti tipus. Tai yra, galite parašyti ne tik 1::int, bet ir int(1), kuris bus lygiavertis. Bet ne tipams, kurių pavadinimai susideda iš kelių žodžių! Todėl, jei norite perkelti skaitmeninę reikšmę į dvigubo tikslumo tipą funkcinėje formoje, naudokite šio tipo slapyvardį float8, ty SELECT float8(1).

Kas didesnis už begalybę?

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

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

Pažiūrėk, kaip tai atrodo! Pasirodo, yra kažkas didesnio už begalybę, ir tai yra NaN! Tuo pačiu metu PostgreSQL dokumentacija žiūri į mus sąžiningomis akimis ir teigia, kad NaN yra akivaizdžiai didesnis nei bet kuris kitas skaičius, taigi ir begalybė. Priešingai taip pat galioja -NaN. Sveiki, matematikos mylėtojai! Tačiau turime atsiminti, kad visa tai veikia realių skaičių kontekste.

Akių apvalinimas

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

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

Dar vienas netikėtas pasisveikinimas iš bazės. Vėlgi, atminkite, kad dvigubo tikslumo ir skaičių tipai turi skirtingus apvalinimo efektus. Skaitmeniniam - įprastas, kai 0,5 suapvalinamas, o dvigubam tikslumui - 0,5 suapvalinamas iki artimiausio lyginio sveikojo skaičiaus.

Pinigai yra kažkas ypatingo

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

Pagal PostgreSQL, pinigai nėra tikras skaičius. Pasak kai kurių asmenų, taip pat. Turime atsiminti, kad pinigų tipą galima perkelti tik į skaitinį tipą, kaip ir tik skaitinį tipą galima perkelti į pinigų tipą. Bet dabar galite žaisti su juo taip, kaip širdis geidžia. Bet tai nebus tie patys pinigai.

Smallint ir sekos generavimas

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 nemėgsta gaišti laiko smulkmenoms. Kokios yra šios sekos, pagrįstos smallint? int, ne mažiau! Todėl, bandant vykdyti aukščiau pateiktą užklausą, duomenų bazė bando perduoti smallint į kokį nors kitą sveikųjų skaičių tipą ir mato, kad gali būti keletas tokių atmetimų. Kuriuos apyrankes rinktis? Ji negali to nuspręsti, todėl sugenda dėl klaidos.

Failas numeris du. „char“/char/varchar/text

Simbolių tipuose taip pat yra nemažai keistenybių. Susipažinkime ir su jais.

Kokie tai triukai?

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

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

Koks čia "char" tipas, koks tai klounas? Mums tų nereikia... Nes jis apsimeta paprastu žmogumi, nors yra kabutėse. Nuo įprasto simbolio, kuris yra be kabučių, jis skiriasi tuo, kad išveda tik pirmąjį eilutės atvaizdavimo baitą, o įprastas simbolis – pirmąjį simbolį. Mūsų atveju pirmasis simbolis yra raidė P, kuri unikodo vaizde užima 2 baitus, tai patvirtina konvertavus rezultatą į baito tipą. Ir „char“ tipas užima tik pirmąjį šio unikodo vaizdavimo baitą. Tai kam tada reikalingas šis tipas? PostgreSQL dokumentacijoje teigiama, kad tai yra specialus tipas, naudojamas specialiems poreikiams tenkinti. Taigi vargu ar mums to prireiks. Tačiau pažvelkite į jo akis ir nesuklysite, kai sutiksite jį su jo ypatingu elgesiu.

Papildomos erdvės. Iš akių, iš proto

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

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

Pažvelkite į pateiktą pavyzdį. Visus rezultatus specialiai konvertavau į bytea tipą, kad būtų aiškiai matyti, kas ten yra. Kur yra užpakaliniai tarpai po atmetimo į varchar(6)? Dokumentacijoje glaustai rašoma: „Perduodant simbolio reikšmę kitam simbolių tipui, po jų esantis tarpas atmetamas“. Šį nemalonumą reikia atsiminti. Ir atminkite, kad jei kabutės eilutės konstanta yra perduodama tiesiai į tipą varchar(6), galiniai tarpai išsaugomi. Štai tokie stebuklai.

Failas numeris trys. json/jsonb

JSON yra atskira struktūra, kuri gyvena savo gyvenimą. Todėl jo ir PostgreSQL subjektai šiek tiek skiriasi. Štai pavyzdžiai.

Johnsonas ir Johnsonas. jausti skirtumą

SELECT 'null'::jsonb IS NULL

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

Reikalas tas, kad JSON turi savo nulinį objektą, kuris nėra NULL analogas PostgreSQL. Tuo pačiu metu pats JSON objektas gali turėti reikšmę NULL, todėl išraiška SELECT null::jsonb IS NULL (atkreipkite dėmesį, kad nėra pavienių kabučių) šį kartą bus teisinga.

Viena raidė viską pakeičia

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

Reikalas tas, kad json ir jsonb yra visiškai skirtingos struktūros. Json objektas saugomas toks, koks yra, o jsonb jis jau saugomas analizuotos, indeksuotos struktūros pavidalu. Štai kodėl antruoju atveju objekto reikšmė raktu 1 buvo pakeista iš [1, 2, 3] į [7, 8, 9], kuri pateko į struktūrą pačioje pabaigoje su tuo pačiu raktu.

Negerkite vandens nuo veido

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

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

„PostgreSQL“ įgyvendindamas JSONB pakeičia realių skaičių formatavimą, suteikdamas jiems klasikinę formą. Tai neįvyksta JSON tipui. Šiek tiek keista, bet jis teisus.

Failas numeris keturi. data / laikas / laiko žyma

Taip pat yra tam tikrų keistenybių, susijusių su datos / laiko tipais. Pažiūrėkime į juos. Leiskite man iš karto padaryti išlygą, kad kai kurios elgesio ypatybės paaiškės, jei gerai suprasite darbo su laiko juostomis esmę. Bet tai taip pat yra atskiro straipsnio tema.

Mano tavo nesupranta

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

Atrodytų, kas čia nesuprantamo? Tačiau duomenų bazė vis dar nesupranta, ką mes čia įtraukėme į pirmą vietą – metus ar dieną? Ir ji nusprendžia, kad dabar 99 m. sausio 2008 d. Paprastai tariant, perduodant datas tekstiniu formatu, reikia labai atidžiai patikrinti, ar teisingai duomenų bazė jas atpažino (ypač išanalizuoti datestyle parametrą su komanda SHOW datestyle), nes neaiškumai šiuo klausimu gali būti labai brangūs.

Iš kur tai gavai?

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

Kodėl duomenų bazė negali suprasti aiškiai nurodyto laiko? Kadangi laiko juosta turi ne santrumpą, o pilną pavadinimą, kuris prasmingas tik datos kontekste, nes atsižvelgiama į laiko juostų pasikeitimų istoriją, o be datos ji neveikia. O pati laiko juostos formuluotė kelia klausimų – ką iš tikrųjų turėjo omenyje programuotojas? Todėl čia viskas logiška, jei pažiūrėsi.

Kas su juo blogai?

Įsivaizduokite situaciją. Lentelėje yra laukas, kurio tipas yra timestampz. Norite jį indeksuoti. Bet jūs suprantate, kad indekso kūrimas šiame lauke ne visada pateisinamas dėl didelio selektyvumo (beveik visos šio tipo reikšmės bus unikalios). Taigi nuspręsite sumažinti indekso selektyvumą, nurodydami tipą į datą. Ir jūs gausite staigmeną:

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

Kas nutiko? Faktas yra tas, kad norint perduoti timestampz tipą į datos tipą, naudojama TimeZone sistemos parametro reikšmė, todėl tipo konvertavimo funkcija priklauso nuo pasirinktinio parametro, t.y. nepastovios. Tokios funkcijos indekse neleidžiamos. Tokiu atveju turite aiškiai nurodyti, kurioje laiko juostoje atliekamas tipo perdavimas.

Kai dabar visai nėra net dabar

Mes įpratę dabar() grąžinti dabartinę datą/laiką, atsižvelgiant į laiko juostą. Tačiau pažiūrėkite į šiuos klausimus:

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;

Data/laikas grąžinamas ta pati, nesvarbu, kiek laiko praėjo nuo ankstesnio prašymo! Kas nutiko? Faktas yra tas, kad dabar() yra ne dabartinis laikas, o dabartinės operacijos pradžios laikas. Todėl sandorio metu jis nesikeičia. Bet kuri užklausa, paleista už operacijos ribų, netiesiogiai įtraukiama į operaciją, todėl nepastebime, kad laikas grąžintas paprastu SELECT now(); Tiesą sakant, ne dabartinis... Jei norite gauti sąžiningą dabartinį laiką, turite naudoti funkciją clock_timestamp().

Failo numeris penki. šiek tiek

Keista truputi

SELECT '111'::bit(4)

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

Kurioje pusėje reikia pridėti bitų tipo išplėtimo atveju? Atrodo, kad yra kairėje. Tačiau tik bazė šiuo klausimu turi kitokią nuomonę. Būkite atsargūs: jei skaitmenų skaičius nesutampa išduodant tipą, negausite to, ko norėjote. Tai taikoma tiek pridedant bitus į dešinę, tiek apkarpant bitus. Taip pat dešinėje...

Failas numeris šeši. Masyvai

Net NULL neiššovė

SELECT ARRAY[1, 2] || NULL

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

Kaip paprasti žmonės, augantys naudojant SQL, tikimės, kad šios išraiškos rezultatas bus NULL. Bet jo ten nebuvo. Grąžinamas masyvas. Kodėl? Kadangi šiuo atveju bazė į sveikųjų skaičių masyvą perduoda NULL ir netiesiogiai iškviečia funkciją array_cat. Tačiau vis tiek lieka neaišku, kodėl ši „masyvo katė“ nenustato masyvo iš naujo. Tokį elgesį taip pat tiesiog reikia atsiminti.

Apibendrinti. Yra daug keistų dalykų. Dauguma jų, žinoma, nėra tokie kritiški, kad kalbėtų apie akivaizdžiai netinkamą elgesį. O kiti paaiškinami naudojimo paprastumu arba jų taikymo tam tikrose situacijose dažnumu. Tačiau tuo pačiu metu yra daug netikėtumų. Todėl jūs turite apie juos žinoti. Jei bet kokių tipų elgesyje pastebėsite ką nors keisto ar neįprasto, parašykite komentaruose, mielai papildysiu juose esančią dokumentaciją.

Šaltinis: www.habr.com

Įtartini tipai