Apmierinoši tipi

To their outward appearance, nothing raises suspicion. Furthermore, they even seem familiar and well-known to you. But that is only until you check them. This is when they reveal their treacherous nature, behaving completely differently than you expected. Sometimes, they do things that make your hair stand on end — for instance, they might lose the confidential data entrusted to them. When you confront them, they claim not to know each other, while in the shadows, they are diligently working under the same hood. It’s time to finally bring them into the light. Let’s also sort out these suspicious types.

Data typing in PostgreSQL, despite its logical foundation, often presents very strange surprises. In this article, we will try to clarify some of their quirks, understand the reasons for their odd behavior, and learn how to avoid problems in everyday practice. To be honest, I composed this article also as a sort of manual for myself, a manual to which I could easily refer in contentious situations. Therefore, it will be updated as new surprises from suspicious types are discovered. So, let’s embark on this journey, you tireless database detectives!

File number one. real/double precision/numeric/money

It would seem that numeric types are the least problematic in terms of behavioral surprises. But that’s not the case at all. So let’s start with them. So…

Have forgotten how to count

SELECT 0.1::real = 0.1

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

What’s the issue? The fact is that PostgreSQL casts the untyped constant 0.1 to the double precision type and attempts to compare it with 0.1 of the real type. And these are absolutely different values! The crux of the matter lies in the representation of real numbers in machine memory. Since 0.1 cannot be represented as a finite binary fraction (it will be 0.0(0011) in binary), numbers of different precisions will differ, hence the result that they are not equal. Generally speaking, this is a topic for a separate article, and I won't go into more detail here.

Where does the error come from?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Error **********
ERROR: syntax error at or near "("
SQL state: 42601
Character: 24

Daudzi zina, ka PostgreSQL atļauj funkcionālo veida konvertēšanu. Tas nozīmē, ka var rakstīt ne tikai 1::int, bet arī int(1), kas būs ekvivalenti. Tomēr tas neattiecas uz datu tipiem, kuru nosaukums sastāv no vairākiem vārdiem! Tādēļ, ja vēlaties konvertēt skaitlisko vērtību uz double precision tipa funkcionālo formu, izmantojiet šī tipa alias float8, piemēram, SELECT float8(1).

Kas ir lielāks par bezgalību?

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

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

Un tā nu iznāk! Izrādās, ir kaut kas, kas ir lielāks par bezgalību, un tas ir NaN! Turklāt PostgreSQL dokumentācija ar godīgu skatienu apgalvo, ka NaN ir acīmredzami lielāks par jebkuru citu skaitli, līdz ar to par bezgalību. Tas pats ir taisnīgi attiecībā uz -NaN. Sveiciens, matemātikas analīzes cienītāji! Bet ir jāatceras, ka viss tas darbojas reālu skaitļu kontekstā.

Acis noapaļojoties

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

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

Vēl viens negaidīts sveiciens no datubāzes. Un vēlreiz jāpatur prātā, ka double precision un numeric tipiem ir atšķirīga noapaļošana. Numeric tipam — parasta, kur 0,5 noapaļojas uz augšu, bet double precision — 0,5 noapaļojas uz tuvāko pāra veselo skaitli.

Nauda ir kaut kas īpašs

SELECT '10'::money::float8

ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Kļūda **********
ERROR: cannot cast type money to double precision
SQL stāvoklis: 42846
Simbols: 19

Pēc PostgreSQL domām, nauda nav reāls skaitlis. Dažu indivīdu prātā — arī. Mums jāatceras, ka naudas tipa konvertēšana ir iespējama tikai uz numeric tipu, kā arī naudas tipu var konvertēt tikai uz numeric tipu. Taču ar to jau var spēlēties, kā vien sirds vēlas. Bet tas vairs nebūs tas pats naudu.

Smallint un secību ģenerēšana

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.
********** Kļūda **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL stāvoklis: 42725
Padoms: Could not choose a best candidate function. You might need to add explicit type casts.
Simbols: 18

PostgreSQL nepatīk ierobežoties. Kādas tādas secības uz smallint bāzes? int, ne mazāk! Tāpēc, mēģinot izpildīt iepriekš minēto vaicājumu, datu bāze mēģina pārvērst smallint kādā citā veselā skaitļa tipā un redz, ka šādām pārvēršanām var būt vairāki varianti. Kuru pārvēršanu izvēlēties? To tā nevar izšķirt, un tāpēc tā avarē ar kļūdu.

Dossjē numurs divi. „char”/char/varchar/text

Dažas dīvainības ir arī rakstzīmju tipos. Iepazīsimies ar tām.

Kas tas par triku?

SELECT 'PETJA'::"char"
     , 'PETJA'::"char"::bytea
     , 'PETJA'::char
     , 'PETJA'::char::bytea

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

Kas ir šis „char” tips, kas tas par klaunu? Mums tādi nav vajadzīgi… Tāpēc, ka viņš izskatās kā parasts char, neskatoties uz to, ka ir pēdiņās. Bet atšķiras no parasta char, kas ir bez pēdiņām, ar to, ka izvada tikai pirmo baito virknes attēlojumā, bet normāls char izvada pirmo simbolu. Mūsu gadījumā pirmais simbols ir burts П, kurš unicode attēlojumā aizņem 2 baitus, par ko liecina rezultāta konvertēšana uz bytea tipu. Šis „char” tips ņem tikai pirmo baito no šī unicode attēlojuma. Kāpēc tad šis tips ir nepieciešams? PostgreSQL dokumentācija saka, ka tas ir īpašs tips, kas paredzēts īpašām vajadzībām. Tāpēc tas, visticamāk, nebūs mums nepieciešams. Bet paskatieties tam acīs un nesajaucat, kad sastopat to ar tā īpašo uzvedību.

Lieki tukšie zīmuli. Prom no acīm, prom no sirds

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

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

Iepazīstieties ar sniegto piemēru. Es speciāli visus rezultātus pārvēru uz bytea tipu, lai būtu skaidri redzams, kas tur ir. Kur ir beigu tukšie zīmuli pēc pārvēršanas uz varchar(6)? Dokumentācija īsi apgalvo: „Pārvēršot rakstzīmju vērtību uz citu rakstzīmju tipu, papildus tukšie zīmuli tiek izmesti.” Šo nepatiku vajadzētu atcerēties. Un ņemiet vērā, ka, ja virknes konstante pēdiņās nekavējoties tiek pārvērsta uz varchar(6), beigu tukšie zīmuli paliek. Tāda nu ir šī burvība.

Dossjē numurs trīs. json/jsonb

JSON ir atsevišķa struktūra, kas dzīvo savu dzīvi. Tāpēc tās entitātes un PostgreSQL entitātes nedaudz atšķiras. Lūk, piemēri.

Džonsons un Džonsons. Izjūti atšķirību

SELECT 'null'::jsonb IS NULL

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

Viss ir par to, ka JSON ir sava null tipa būtība, kas nav analogs NULL PostgreSQL. Tajā pašā laikā, pats JSON objekts var būt NULL vērtība, tāpēc izteikums SELECT null::jsonb IS NULL (ievērojiet, ka nav vienkāršo pēdiņu) šoreiz atgriezīs true.

Viena burta izmaiņas maina visu

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

Viss ir par to, ka json un jsonb ir pilnīgi atšķirīgas struktūras. json objekts tiek glabāts tāds, kāds tas ir, bet jsonb tas tiek glabāts kā jau strukturēts un indeksēts. Tieši tāpēc otrajā gadījumā objekta vērtība pie atslēgas 1 tika nomainīta no [1, 2, 3] uz [7, 8, 9], kas bija nākusi struktūrā pašā beigās ar to pašu atslēgu.

No sejas ūdens nedzer

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

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

PostgreSQL JSONB realizācijā maina reālo skaitļu formatējumu, padarot tos par klasisku formu. JSON tipam tā nenotiek. Dīvaini nedaudz, bet tā ir tāda kā viņu tiesība.

Dosiē numurs četri. date/time/timestamp

Ar datuma un laika tipiem ir arī dažas dīvainības. Apskatīsim tās. Uzreiz piebildīšu, ka dažas no uzvedības iezīmēm kļūst skaidras, ja labi saprot, kā strādāt ar laika zonām. Bet tas ir arī atsevišķa raksta tēma.

Mans tavs nesaprot

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.
********** Kļūda **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL stāvoklis: 22008
Ieteikums: Perhaps you need a different "datestyle" setting.
Simbols: 8

Izskatās, ka te nav nekā neskaidra? Bet tomēr datubāze nesaprot, ko mēs šeit esam pirmās vietas ievietojuši — gadu vai dienu? Un nolemj, ka tā ir 99. janvāris 2008. gadā, kas viņai izraisa galvas eksploziju. Vispārīgi runājot, ja datumi tiek nodoti teksta formātā, jāpārbauda, cik pareizi dati tiek atpazīti (īpaši jāanalizē parametrs datestyle ar komandu SHOW datestyle), jo šajā jautājumā neskaidrības var izmaksāt ļoti dārgi.

Tu no kurienes tāds parādījies?

IZVADĪT '04:05 Eiropa/Moskva'::laiks

KĻŪDA: nederīgs ievades sintakse laika tipam: "04:05 Eiropa/Moskva"
LINE 1: IZVADĪT '04:05 Eiropa/Moskva'::laiks
               ^
********** Kļūda **********
KĻŪDA: nederīgs ievades sintakse laika tipam: "04:05 Eiropa/Moskva"
SQL-stāvoklis: 22007
Simbols: 8

Kāpēc datubāze nevar saprast skaidri norādīto laiku? Tāpēc, ka laika joslai ir norādīts nevis saīsinājums, bet pilnais nosaukums, kas iegūst jēgu tikai datuma kontekstā, jo tas ņem vērā laika joslu izmaiņu vēsturi, un bez datuma tas nedarbojas. Un arī paša laika virknes formulējums rada jautājumus — ko tieši domāja programmētājs? Tāpēc šeit viss ir loģiski, ja padomā.

Kas viņam nepatīk?

Iedomājieties situāciju. Jūsu tabulā ir lauks ar tipu timestamptz. Jūs vēlaties to indeksēt. Bet saprotat, ka indeksu veidot pēc šī lauka ne vienmēr ir pamatoti, ņemot vērā tā augsto selektivitāti (gandrīz visas vērtības šī tipa būs unikālas). Tāpēc jūs nolemjat samazināt indeksa selektivitāti, pārvēršot šo tipu par datumu. Un saņemat pārsteigumu:

IZVEIDOT INDAX "iIdent-DateLastUpdate"
  NO public."Ident" izmantojot btree
  (("DTLastUpdate"::datums));

KĻŪDA: funkcijām indeksu izteiksmē ir jābūt marķētām kā IMMUTABLE
********** Kļūda **********
KĻŪDA: funkcijām indeksu izteiksmē ir jābūt marķētām kā IMMUTABLE
SQL-stāvoklis: 42P17

Kas notiek? Tas, ka, lai pārvērstu tipu timestamptz par tipu datums, tiek izmantota sistēmas parametra TimeZone vērtība, kas padara tipa pārvēršanas funkciju atkarīgu no iestatāmā parametra, t.i., mainīgas (volatile). Šādas funkcijas indeksā nav pieļaujamas. Šajā gadījumā ir jānorāda, kurā laika joslā tiek veikta tipa pārvēršana.

Kad now vispār nav now

Mēs esam pieraduši, ka now() atgriež pašreizējo datumu/laiku, ņemot vērā laika joslu. Bet paskatieties uz šiem pieprasījumiem:

SĀKT TRANSAKCIJU;
IZVADĪT now();

            now
  timestamp ar laika joslu
-----------------------------
2019-11-26 13:13:04.271419+03

...

IZVADĪT now();

            now
  timestamp ar laika joslu
-----------------------------
2019-11-26 13:13:04.271419+03

...

IZVADĪT now();

            now
  timestamp ar laika joslu
-----------------------------
2019-11-26 13:13:04.271419+03

APSTIPRINĀT;

Datums/laiks tiek atgriezts vienādi, neatkarīgi no tā, cik ilgs laiks ir pagājis kopš iepriekšējā pieprasījuma! Kas notiek? Tas ir tāpēc, ka now() nav pašreizējais laiks, bet gan pašreizējās tranzakcijas sākuma laiks. Tādējādi tranzakcijas ietvaros tas nemainās. Ikviens pieprasījums, kas tiek izpildīts ārpus tranzakcijas, netieši tiek iekļauts tranzakcijā, tādēļ mēs nepamanām, ka laiks, ko sniedz vienkāršais pieprasījums SELECT now(); patiesībā nav aktuāls... Ja vēlies iegūt patiesi aktuālu laiku, jāizmanto funkcija clock_timestamp().

Dossier numurs pieci. bit

Nedaudz dīvaini

SELECT '111'::bit(4)

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

No kuras puses vajadzētu pievienot bitus tipa paplašināšanas gadījumā? Izskatās, ka no kreisās. Bet tikai datubāze ir citā domā. Esi uzmanīgs: ja bitu skaits tipa konvertēšanas laikā nesakrīt, tu saņemsi pavisam ko citu, nekā gribēji. Tas attiecas gan uz bitu pievienošanu labajā pusē, gan uz bitu samazināšanu. Tāpat labajā pusē...

Dossier numurs seši. Masīvi

Pat NULL neiznīcināja

SELECT ARRAY[1, 2] || NULL

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

Kā normāli cilvēki, kas izaudzināti SQL, mēs gaidām, ka šī izteiksme atgriezīs NULL. Bet ne šajā gadījumā. Tiek atgriezts masīvs. Kāpēc? Tāpēc, ka šajā gadījumā datubāze pārvērš NULL par vesela skaitļa masīvu un netieši izsauc funkciju array_cat. Bet tomēr paliek neskaidrs, kāpēc šis "masīvu kaķis" nenokaujas masīvu. Šādu uzvedību ir vienkārši jāatceras.

Noslēgsim. Ir daudz dīvainību. Lielākā daļa no tām, protams, nav tik kritiskas, lai runātu par klajo nepiemērotību. Citas izskaidro lietošanas ērtība vai to piemērošanas biežums konkrētās situācijās. Bet tajā pašā laikā ir daudz pārsteigumu. Tātad par tām jāzina. Ja atrodiet vēl kaut ko dīvainu vai neparastu kādu tipu uzvedībā, rakstiet komentāros, ar prieku papildināšu esošos dossē.

Avots: habr.com