🥇 Fertochte soarten

Der is neat fertocht oer harren uterlik. Boppedat lykje se jo sels goed en lang fertroud. Mar dat is allinich oant jo se kontrolearje. Dit is wêr't se har ferrifeljende aard sjen litte, folslein oars wurkje as jo ferwachte. En soms dogge se wat dat jo hier op 'e ein bringt - se ferlieze bygelyks geheime gegevens dy't har tafertroud binne. As jo se konfrontearje, beweare se dat se inoar net kenne, hoewol se yn 'e skaden hurd wurkje ûnder deselde kap. It is tiid om se einlings nei skjin wetter te bringen. Lit ús ek omgean mei dizze fertochte typen.

Gegevenstypen yn PostgreSQL, foar al syn logika, presintearret soms heul nuvere ferrassingen. Yn dit artikel sille wy besykje te ferdúdlikjen guon fan harren eigenaardichheden, begripe de reden foar harren frjemde gedrach en begripe hoe't net komme yn problemen yn it deistich praktyk. Om de wierheid te sizzen, haw ik dit artikel ek gearstald as in soarte fan neislachboek foar mysels, in naslagboek dêr't maklik nei ferwiisd wurde koe yn kontroversjele gefallen. Dêrom sil it oanfolle wurde as nije ferrassingen fan fertochte soarten ûntdutsen wurde. Dat, lit ús gean, oh ûnfermindere database trackers!

Dossier nûmer ien. echte / dûbele presyzje / numerike / jild

It soe lykje dat numerike typen binne it minst problematysk yn termen fan ferrassingen yn gedrach. Mar hoe't it ek is. Sa litte wy begjinne mei harren. Sa…

Fergetten hoe te tellen

SELECT 0.1::real = 0.1

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

Wat is der oan de hân? It probleem is dat PostgreSQL de untypte konstante 0.1 konvertearret nei dûbele presyzje en besiket it te fergelykjen mei 0.1 fan echte type. En dit binne folslein oare betsjuttingen! It idee is in fertsjintwurdigje echte nûmers yn masine ûnthâld. Sûnt 0.1 kin net fertsjintwurdige wurde as in einige binêre fraksje (it soe 0.0 (0011) yn binêr wêze), sille getallen mei ferskillende bit djipten oars wêze, dus it resultaat dat se net gelyk binne. Yn 't algemien is dit in ûnderwerp foar in apart artikel; Ik sil hjir net yn mear detail skriuwe.

Wêr komt de flater wei?

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

In protte minsken witte dat PostgreSQL funksjonele notaasje mooglik makket foar type casting. Dat is, jo kinne net allinich 1::int skriuwe, mar ek int(1), dy't lykweardich wêze sil. Mar net foar typen wêrfan de nammen besteane út ferskate wurden! Dêrom, as jo wolle cast in numerike wearde te ferdûbeljen presys type yn funksjonele foarm, brûk dan de alias fan dit type float8, dat is, SELECT float8 (1).

Wat is grutter dan ûneinichheid?

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

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

Sjoch hoe't it is! It docht bliken dat d'r wat grutter is dan ûneinichheid, en it is NaN! Tagelyk besjocht de PostgreSQL-dokumintaasje ús mei earlike eagen en beweart dat NaN fansels grutter is dan elk oar nûmer, en dus ûneinich. It tsjinoerstelde is ek wier foar -NaN. Hallo, wiskundeleafhawwers! Mar wy moatte betinke dat dit alles wurket yn 'e kontekst fan echte sifers.

Eye rounding

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

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

In oare ûnferwachte groet fan 'e basis. Unthâld nochris dat dûbele presyzje en numerike typen ferskillende rûningseffekten hawwe. Foar numerike - de gewoane, as 0,5 wurdt ôfrûn nei boppen, en foar dûbele presyzje - 0,5 wurdt ôfrûn nei it tichtstby even hiel getal.

Jild is wat bysûnders

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

Neffens PostgreSQL is jild gjin echt nûmer. Neffens guon partikulieren ek. Wy moatte betinke dat it castjen fan it jildtype allinich mooglik is nei it numerike type, lykas allinich it numerike type kin wurde cast nei it jildtype. Mar no kinne jo dermei boartsje lykas jo hert wol. Mar it sil net itselde jild wêze.

Smallint en sequence generaasje

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 hâldt net fan tiid te fergrieme oan lytse dingen. Wat binne dizze sekwinsjes basearre op smallint? ynt, net minder! Dêrom, by it besykjen om de boppesteande query út te fieren, besiket de databank smallint te casten nei in oar hielgetaltype, en sjocht dat d'r ferskate sokke casts kinne wêze. Hokker cast te kiezen? Se kin net beslute dit, en dêrom crashes mei in flater.

File nûmer twa. "char"/char/varchar/text

In oantal nuverheden binne ek oanwêzich yn karaktertypen. Litte wy se ek yn 'e kunde komme.

Wat foar trúkjes binne dit?

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

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

Hokker soarte fan "char" is dit, wat foar clown is dit? Dy hawwe wy net nedich... Want it docht as in gewoane char, ek al stiet it tusken aanhalingstekens. En it ferskilt fan in gewoane char, dy't sûnder oanhalingstekens is, yn dat it allinich de earste byte fan 'e tekenrigefoarstelling útfiert, wylst in normale char it earste karakter útfiert. Yn ús gefal is it earste karakter de letter P, dy't yn 'e unicode-representaasje 2 bytes opnimt, lykas bliken docht út it konvertearjen fan it resultaat nei it bytea-type. En it type "char" nimt allinich de earste byte fan dizze unicode-representaasje. Dan wêrom is dit type nedich? De PostgreSQL-dokumintaasje seit dat dit in spesjaal type is dat wurdt brûkt foar spesjale behoeften. Dus it is net wierskynlik dat wy it nedich hawwe. Mar sjoch yn syn eagen en jo sille net fersinne wurde as jo him moetsje mei syn bysûndere gedrach.

Ekstra romten. Út it each út it hert

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

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

Sjoch ris nei it jûn foarbyld. Ik haw alle resultaten spesjaal omsetten nei it bytea-type, sadat dúdlik te sjen wie wat der stie. Wêr binne de slepende romten nei casting nei varchar (6)? De dokumintaasje stelt bondich: "By it casten fan de wearde fan karakter nei in oar karaktertype, wurdt eftersteande wite romte ferwidere." Dizze ôfkear moat betocht wurde. En tink derom dat as in oanhelle snaarkonstante direkt nei type varchar (6) cast wurdt, wurde de efterste spaasjes bewarre. Sa binne de wûnders.

Triem nûmer trije. json/jsonb

JSON is in aparte struktuer dy't syn eigen libben libbet. Dêrom binne har entiteiten en dy fan PostgreSQL wat oars. Hjir binne foarbylden.

Johnson en Johnson. fiel it ferskil

SELECT 'null'::jsonb IS NULL

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

It ding is dat JSON in eigen nul-entiteit hat, dy't net de analoog is fan NULL yn PostgreSQL. Tagelyk kin it JSON-objekt sels de wearde NULL hawwe, sadat de útdrukking SELECT null :: jsonb IS NULL (notysje op it ûntbrekken fan inkele oanhalings) sil dizze kear wier weromkomme.

Ien letter feroaret alles

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

It ding is dat json en jsonb folslein ferskillende struktueren binne. Yn json wurdt it objekt opslein sa't it is, en yn jsonb is it al opslein yn 'e foarm fan in parsed, yndeksearre struktuer. Dêrom waard yn it twadde gefal de wearde fan it objekt troch kaai 1 ferfongen fan [1, 2, 3] nei [7, 8, 9], dy't yn 'e struktuer oan' e ein kaam mei deselde kaai.

Drink gjin wetter fan jo gesicht

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

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

PostgreSQL yn syn JSONB-ymplemintaasje feroaret de opmaak fan echte nûmers, en bringt se nei de klassike foarm. Dit bart net foar it JSON-type. In bytsje nuver, mar hy hat gelyk.

File nûmer fjouwer. datum/tiid/tiidstempel

D'r binne ek wat nuverheden mei datum-/tiidtypen. Litte wy nei se sjen. Lit my fuortdaliks reservearje dat guon fan 'e gedrachsfunksjes dúdlik wurde as jo de essinsje fan wurkje mei tiidsônes goed begripe. Mar dit is ek in ûnderwerp foar in apart artikel.

Myn dyn begrypt it net

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

It soe lykje dat wat is ûnbegryplik hjir? Mar de databank begrypt noch altyd net wat wy hjir op it earste plak sette - it jier of de dei? En se beslút dat it 99 jannewaris 2008 is, dy't har yn 't sin blaast. Yn 't algemien moatte jo by it oerdragen fan datums yn tekstformaat tige soarchfâldich kontrolearje hoe goed de databank se erkende (benammen analysearje de parameter datestyle mei it kommando SHOW datestyle), om't ûndúdlikheden yn dizze saak heul djoer kinne wêze.

Wêr hasto dit wei?

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

Wêrom kin de databank de eksplisyt oantsjutte tiid net begripe? Omdat de tiidsône hat gjin ôfkoarting, mar in folsleine namme, dat makket sin allinnich yn 'e kontekst fan in datum, sûnt it hâldt rekken mei de skiednis fan tiidsône feroarings, en it wurket net sûnder in datum. En de formulearring fan 'e tiidline ropt fragen op - wat betsjutte de programmeur echt? Dêrom is alles hjir logysk, as jo it sjogge.

Wat is der mis mei him?

Stel jo de situaasje foar. Jo hawwe in fjild yn jo tabel mei type timestamptz. Jo wolle it yndeksearje. Mar jo begripe dat it bouwen fan in yndeks op dit fjild net altyd rjochtfeardige is fanwegen syn hege selektiviteit (hast alle wearden fan dit type sille unyk wêze). Dat jo beslute om de selektiviteit fan 'e yndeks te ferminderjen troch it type nei in datum te casten. En jo krije in ferrassing:

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

Wat is der oan de hân? It feit is dat om in timestamptz-type nei in datumtype te smiten, wurdt de wearde fan 'e TimeZone-systeemparameter brûkt, wêrtroch't de typekonverzjefunksje ôfhinklik is fan in oanpaste parameter, d.w.s. flechtich. Sokke funksjes binne net tastien yn de yndeks. Yn dit gefal moatte jo eksplisyt oanjaan yn hokker tiidsône it type cast wurdt útfierd.

Wannear no is no hielendal net iens

Wy binne wend om no () de aktuele datum / tiid werom te jaan, rekken hâldend mei de tiidsône. Mar sjoch nei de folgjende fragen:

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;

De datum/tiid wurdt itselde weromjûn, nettsjinsteande hoefolle tiid is ferrûn sûnt it foarige fersyk! Wat is der oan de hân? It feit is dat no () net de aktuele tiid is, mar de starttiid fan 'e aktuele transaksje. Dêrom feroaret it net binnen de transaksje. Eltse query lansearre bûten it berik fan in transaksje wurdt ferpakt yn in transaksje ymplisyt, dat is wêrom wy net fernimme dat de tiid werom troch in ienfâldige SELECT no (); feitlik net de aktuele ... As jo in earlike aktuele tiid krije wolle, moatte jo de funksje clock_timestamp() brûke.

File nûmer fiif. bit

Nuver in bytsje

SELECT '111'::bit(4)

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

Hokker kant moatte de bits tafoege wurde yn gefal fan type útwreiding? It liket te wêzen oan de linkerkant. Mar allinnich de basis hat in oare miening oer dizze saak. Wês foarsichtich: as it oantal sifers net oerienkomt by it casten fan in type, krije jo net wat jo woenen. Dit jildt foar sawol it tafoegjen fan bits nei rjochts as it trimmen fan bits. Ek rjochts...

File nûmer seis. Arrays

Sels NULL net fjoer

SELECT ARRAY[1, 2] || NULL

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

As normale minsken opwekke op SQL, ferwachtsje wy dat it resultaat fan dizze útdrukking NULL is. Mar it wie der net. In array wurdt weromjûn. Wêrom? Om't yn dit gefal de basis NULL smyt nei in hielgetal array en ropt ymplisyt de array_cat-funksje op. Mar it bliuwt noch ûndúdlik wêrom dizze "array cat" de array net weromsette. Dit gedrach moat ek gewoan betocht wurde.

Gearfetsje. Der binne genôch nuvere dingen. De measten fan harren binne fansels net sa kritysk om te praten oer blatant ûnfatsoenlik gedrach. En oaren wurde ferklearre troch it gemak fan gebrûk of de frekwinsje fan har tapassing yn bepaalde situaasjes. Mar tagelyk binne der in protte ferrassingen. Dêrom moatte jo witte oer harren. As jo wat oars frjemd of ûngewoan fine yn it gedrach fan elke soarten, skriuw dan yn 'e kommentaren, ik sil graach tafoegje oan' e dossiers dy't op har beskikber binne.

Boarne: www.habr.com

Fertochte soarten