Epäilyttävät tyypit

Niiden ulkonäössä ei ole mitään epäselvää. Lisäksi ne näyttävät sinulle jopa tutuilta hyvin ja pitkään. Mutta se on vain siihen asti, kunnes tarkistat ne. Tässä he osoittavat salakavalan luonteensa ja toimivat täysin eri tavalla kuin odotit. Ja joskus he tekevät jotain, mikä saa hiukset nousemaan - esimerkiksi he menettävät heille uskottuja salaisia ​​tietoja. Kun kohtaat heidät, he väittävät, etteivät he tunne toisiaan, vaikka varjoissa he työskentelevät kovasti saman hupun alla. On aika viimein tuoda ne puhtaaseen veteen. Käsittelemme myös näitä epäilyttäviä tyyppejä.

Tietojen kirjoittaminen PostgreSQL:ssä, kaikesta sen logiikasta, tuottaa joskus hyvin outoja yllätyksiä. Tässä artikkelissa yritämme selventää joitain heidän omituisuuksiaan, ymmärtää heidän oudon käytöksensä syyn ja ymmärtää, kuinka olla joutumatta ongelmiin jokapäiväisessä käytännössä. Totta puhuen kokosin tämän artikkelin myös eräänlaiseksi hakuteokseksi itselleni, hakuteokseksi, johon voisi helposti viitata kiistanalaisissa tapauksissa. Siksi sitä täydennetään, kun uusia yllätyksiä epäilyttäviltä tyypeiltä löydetään. Joten mennään, oi väsymättömät tietokantajäljittäjät!

Asiakirja numero yksi. todellinen/kaksinkertainen tarkkuus/numeerinen/raha

Vaikuttaa siltä, ​​että numeeriset tyypit ovat vähiten ongelmallisia käyttäytymisen yllätysten kannalta. Mutta ei väliä kuinka se on. Joten aloitetaan niistä. Niin…

Unohtui kuinka laskea

SELECT 0.1::real = 0.1

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

Mikä hätänä? Ongelmana on, että PostgreSQL muuntaa kirjoittamattoman vakion 0.1 kaksinkertaisen tarkkuuden ja yrittää verrata sitä todellisen tyypin 0.1:een. Ja nämä ovat täysin erilaisia ​​merkityksiä! Ajatuksena on esittää reaalilukuja koneen muistissa. Koska 0.1:tä ei voida esittää äärellisenä binäärimurtolukuna (binäärissä se olisi 0.0(0011), luvut, joilla on eri bittisyvyydet, ovat erilaisia, joten seurauksena on, että ne eivät ole yhtä suuret. Yleisesti ottaen tämä on erillisen artikkelin aihe, en kirjoita tänne sen tarkemmin.

Mistä virhe tulee?

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

Monet ihmiset tietävät, että PostgreSQL mahdollistaa funktionaalisen merkinnän tyyppivalussa. Eli voit kirjoittaa paitsi 1::int, myös int(1), joka on vastaava. Mutta ei tyypeille, joiden nimet koostuvat useista sanoista! Siksi, jos haluat heittää numeerisen arvon kaksinkertaisen tarkkuuden tyyppiin toiminnallisessa muodossa, käytä tämän tyypin aliasta float8, eli SELECT float8(1).

Mikä on ääretöntä suurempaa?

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

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

Katso, millaista se on! Osoittautuu, että on jotain ääretöntä suurempaa, ja se on NaN! Samaan aikaan PostgreSQL-dokumentaatio katsoo meitä rehellisin silmin ja väittää, että NaN on selvästi suurempi kuin mikään muu luku, ja siten ääretön. Päinvastoin on myös -NaN:n kohdalla. Hei matematiikan ystävät! Mutta meidän on muistettava, että tämä kaikki toimii reaalilukujen yhteydessä.

Silmien pyöristäminen

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

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

Toinen odottamaton tervehdys tukikohdasta. Muista jälleen, että kaksinkertaisella tarkkuudella ja numeerisilla tyypeillä on erilaisia ​​pyöristystehosteita. Numeeriselle - tavallinen, kun 0,5 pyöristetään ylöspäin, ja kaksinkertaisella tarkkuudella - 0,5 pyöristetään kohti lähimpää parillista kokonaislukua.

Raha on jotain erityistä

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

PostgreSQL:n mukaan raha ei ole todellinen luku. Joidenkin ihmisten mukaan myös. Meidän on muistettava, että rahatyypin heittäminen on mahdollista vain numeeriseen tyyppiin, samoin kuin vain numeerinen tyyppi voidaan heittää rahatyyppiin. Mutta nyt voit leikkiä sillä niin kuin sydämesi haluaa. Mutta se ei ole sama raha.

Smallint ja sekvenssin luominen

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 ei halua tuhlata aikaa pikkuasioihin. Mitä nämä sekvenssit perustuvat smallint? int, ei vähempää! Siksi, kun yllä olevaa kyselyä yritetään suorittaa, tietokanta yrittää heittää smallint johonkin muuhun kokonaislukutyyppiin ja näkee, että tällaisia ​​heittoja voi olla useita. Mikä näyttelijä valita? Hän ei voi päättää tätä, ja siksi kaatuu virheen vuoksi.

Tiedosto numero kaksi. "char"/char/varchar/text

Hahmotyypeissä on myös useita omituisuuksia. Tutustutaanpa heihinkin.

Millaisia ​​temppuja nämä ovat?

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

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

Millainen "hiili" tämä on, millainen klovni tämä on? Emme tarvitse niitä... Koska se teeskentelee olevansa tavallinen merkki, vaikka se on lainausmerkeissä. Ja se eroaa tavallisesta merkistä, joka on ilman lainausmerkkejä, sillä se tulostaa vain merkkijonoesityksen ensimmäisen tavun, kun taas normaali merkki tulostaa ensimmäisen merkin. Meidän tapauksessamme ensimmäinen merkki on P-kirjain, joka vie unicode-esityksessä 2 tavua, mikä käy ilmi muuntamalla tulos tavutyypiksi. Ja "char"-tyyppi ottaa vain ensimmäisen tavun tästä unicode-esittelystä. Miksi tätä tyyppiä sitten tarvitaan? PostgreSQL-dokumentaatio sanoo, että tämä on erikoistyyppi, jota käytetään erityistarpeisiin. Joten tuskin tarvitsemme sitä. Mutta katso hänen silmiinsä, etkä erehdy, kun tapaat hänet hänen erityisellä käyttäytymisellään.

Ylimääräisiä tiloja. Poissa silmistä, poissa mielestä

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

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

Katso annettua esimerkkiä. Muunsin erityisesti kaikki tulokset bytea-tyyppisiksi, jotta oli selvästi nähtävissä mitä siellä oli. Missä ovat perässä olevat välilyönnit varchar(6)-heiton jälkeen? Dokumentaatiossa todetaan ytimekkäästi: "Kun merkin arvo syötetään toiseen merkkityyppiin, perässä oleva välilyönti hylätään." Tämä vastenmielisyys on muistettava. Ja huomaa, että jos lainattu merkkijonovakio heitetään suoraan tyyppiin varchar(6), loppuvälit säilyvät. Sellaisia ​​ovat ihmeet.

Tiedosto numero kolme. json/jsonb

JSON on erillinen rakenne, joka elää omaa elämäänsä. Siksi sen entiteetit ja PostgreSQL:n entiteetit ovat hieman erilaisia. Tässä on esimerkkejä.

Johnson ja Johnson. tunne erilaisuus

SELECT 'null'::jsonb IS NULL

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

Asia on, että JSON:lla on oma null-entiteetti, joka ei ole NULL:n analogi PostgreSQL:ssä. Samanaikaisesti itse JSON-objektilla voi hyvinkin olla arvo NULL, joten lauseke SELECT null::jsonb IS NULL (huomaa yksittäisten lainausmerkkien puuttuminen) palauttaa tällä kertaa tosi.

Yksi kirjain muuttaa kaiken

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

Asia on, että json ja jsonb ovat täysin erilaisia ​​​​rakenteita. Jsonissa objekti tallennetaan sellaisenaan, ja jsonb:ssä se on jo tallennettu jäsennetyn, indeksoidun rakenteen muodossa. Siksi toisessa tapauksessa objektin arvo avaimella 1 korvattiin [1, 2, 3]:sta [7, 8, 9]:ksi, joka tuli rakenteeseen aivan lopussa samalla avaimella.

Älä juo vettä kasvoiltasi

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

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

PostgreSQL muuttaa JSONB-toteutuksessaan reaalilukujen muotoilua ja tuo ne klassiseen muotoon. Tätä ei tapahdu JSON-tyypillä. Hieman outoa, mutta hän on oikeassa.

Tiedosto numero neljä. päivämäärä/aika/aikaleima

Päivämäärä-/aikatyypeissä on myös joitain omituisuuksia. Katsotaanpa niitä. Haluan tehdä varauksen heti, että jotkut käyttäytymispiirteet selviävät, jos ymmärrät hyvin aikavyöhykkeiden kanssa työskentelyn olemuksen. Mutta tämä on myös erillisen artikkelin aihe.

Omasi eivät ymmärrä

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

Vaikuttaa siltä, ​​että mikä tässä on käsittämätöntä? Mutta tietokanta ei vieläkään ymmärrä, mitä laitamme tässä ykkössijalle – vuoden vai päivän? Ja hän päättää, että on tammikuu 99, 2008, mikä räjäyttää hänen mielensä. Yleisesti ottaen, kun lähetät päivämääriä tekstimuodossa, sinun on tarkistettava erittäin huolellisesti, kuinka oikein tietokanta tunnisti ne (erityisesti analysoida datestyle-parametri SHOW datestyle-komennolla), koska epäselvyydet tässä asiassa voivat olla erittäin kalliita.

Mistä sinä tulit?

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

Miksi tietokanta ei ymmärrä nimenomaisesti määritettyä aikaa? Koska aikavyöhykkeellä ei ole lyhennettä, vaan koko nimi, joka on järkevä vain päivämäärän yhteydessä, koska se ottaa huomioon aikavyöhykkeen muutoshistorian, eikä se toimi ilman päivämäärää. Ja aikajanan sanamuoto herättää kysymyksiä - mitä ohjelmoija todella tarkoitti? Siksi kaikki on loogista tässä, jos katsot sitä.

Mikä häntä vaivaa?

Kuvittele tilanne. Taulukossasi on kenttä, jonka tyyppi on timestampz. Haluat indeksoida sen. Mutta ymmärrät, että indeksin rakentaminen tälle kentälle ei aina ole perusteltua sen korkean selektiivisyyden vuoksi (melkein kaikki tämän tyyppiset arvot ovat ainutlaatuisia). Joten päätät vähentää indeksin selektiivisyyttä lisäämällä tyypin päivämäärään. Ja saat yllätyksen:

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

Mikä hätänä? Tosiasia on, että aikaleimatyypin lähettämiseksi päivämäärätyyppiin käytetään TimeZone-järjestelmäparametrin arvoa, mikä tekee tyypin muunnosfunktiosta riippuvaisen mukautetusta parametrista, ts. haihtuvia. Tällaiset toiminnot eivät ole sallittuja hakemistossa. Tässä tapauksessa sinun on ilmoitettava selvästi, millä aikavyöhykkeellä tyyppilähetys suoritetaan.

Kun nyt ei ole edes nyt ollenkaan

Olemme tottuneet nyt() palauttamaan nykyisen päivämäärän/ajan, ottaen huomioon aikavyöhykkeen. Mutta katso seuraavat kyselyt:

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;

Päivämäärä/aika palautetaan samana riippumatta siitä, kuinka paljon aikaa on kulunut edellisestä pyynnöstä! Mikä hätänä? Tosiasia on, että now() ei ole nykyinen aika, vaan nykyisen tapahtuman alkamisaika. Siksi se ei muutu tapahtuman sisällä. Kaikki tapahtuman ulkopuolella käynnistetyt kyselyt kääritään tapahtumaan implisiittisesti, minkä vuoksi emme huomaa, että yksinkertaisen SELECT now(); itse asiassa, ei nykyistä... Jos haluat saada rehellisen kellonajan, sinun on käytettävä toimintoa clock_timestamp().

Tiedosto numero viisi. bitti

Outoa vähän

SELECT '111'::bit(4)

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

Kummalle puolelle bitit pitäisi lisätä tyyppilaajennuksessa? Se näyttää olevan vasemmalla. Mutta vain tukiasemalla on erilainen mielipide tästä asiasta. Ole varovainen: jos numeroiden määrä ei täsmää tyyppiä heittäessäsi, et saa haluamaasi. Tämä koskee sekä bittien lisäämistä oikealle että bittien trimmaamista. Myös oikealla...

Tiedosto numero kuusi. Taulukot

Jopa NULL ei ampunut

SELECT ARRAY[1, 2] || NULL

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

Kuten tavalliset SQL:llä kasvatetut ihmiset, odotamme tämän lausekkeen tuloksen olevan NULL. Mutta se ei ollut siellä. Joukko palautetaan. Miksi? Koska tässä tapauksessa kanta lähettää NULL:n kokonaislukutaulukkoon ja kutsuu implisiittisesti array_cat-funktiota. Mutta silti on epäselvää, miksi tämä "joukkokissa" ei nollaa taulukkoa. Tämä käytös on myös vain muistettava.

Tee yhteenveto. Outoja asioita on paljon. Useimmat heistä eivät tietenkään ole niin kriittisiä, että he puhuisivat räikeän sopimattomasta käytöksestä. Ja toiset selittyvät helppokäyttöisyydellä tai niiden käyttötiheydellä tietyissä tilanteissa. Mutta samaan aikaan on monia yllätyksiä. Siksi sinun on tiedettävä niistä. Jos löydät jotain muuta outoa tai epätavallista minkä tahansa tyyppisessä käyttäytymisessä, kirjoita kommentteihin, lisään mielelläni niistä saatavilla oleviin asiakirjoihin.

Lähde: will.com