ProHoster > Blog > Amministrazione > Tipi sospetti

Tipi sospetti

Ùn ci hè nunda di suspettu nantu à a so apparenza. Inoltre, vi parenu ancu familiari bè è per un bellu pezzu. Ma questu hè solu finu à chì li cuntrolla. Hè quì chì mostranu a so natura insidiosa, travagliendu in modu completamente diversu di ciò chì aspettavate. E qualchì volta facenu qualcosa chì face i vostri capelli in punta - per esempiu, perdenu dati secreti affidati à elli. Quandu li cunfruntate, dichjaranu chì ùn si cunnosci micca, ancu s'ellu in l'ombra travaglianu diligentemente sottu u stessu cappucciu. Hè ora di purtari infine à l'acqua pulita. Demu ancu trattà cù questi tipi sospetti.

A digitazione di dati in PostgreSQL, per tutta a so logica, presenta qualchì volta sorprese assai strane. In questu articulu avemu da pruvà à chjarificà certi di i so quirks, capisce u mutivu di u so cumpurtamentu stranu è capisce cumu ùn si mette in prublemi in a pratica di ogni ghjornu. Per dì a verità, aghju compilatu questu articulu ancu cum'è una spezia di libru di riferimentu per mè stessu, un libru di riferimentu chì puderia esse facilmente riferitu in casi cuntruversi. Per quessa, serà rimbursatu cum'è novi sorprese da i tipi sospetti sò scuperti. Allora, andemu, o instancabili trackers di basa di dati !

Dossier numeru unu. reale / doppia precisione / numericu / soldi

Sembra chì i tipi numerichi sò u menu problematicu in quantu à sorprese in u cumpurtamentu. Ma ùn importa micca cumu hè. Allora cuminciamu cun elli. Allora…

Scurdatu cumu cuntà

SELECT 0.1::real = 0.1

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

Chì ci hè? U prublema hè chì PostgreSQL cunverte a constante 0.1 senza tipografia in doppia precisione è prova di paragunà cù 0.1 di tipu reale. È questi sò significati completamente diversi! L'idea hè di rapprisintà numeri veri in a memoria di a macchina. Siccomu 0.1 ùn pò micca esse rapprisintatu cum'è una frazzioni binaria finita (saria 0.0 (0011) in binariu), i numeri cù numeri diffirenti seranu diffirenti, da quì u risultatu chì ùn sò micca uguali. In generale, questu hè un tema per un articulu separatu, ùn scriveraghju più in dettagliu quì.

Da induve vene l'errore?

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

Parechje persone sanu chì PostgreSQL permette a notazione funziunale per u casting di tipu. Questu hè, pudete scrive micca solu 1::int, ma ancu int (1), chì serà equivalente. Ma micca per i tipi chì i nomi sò custituiti da parechje parolle! Dunque, sè vo vulete scaccià un valore numericu per duppià u tipu di precisione in forma funziunale, utilizate l'alias di stu tipu float8, vale à dì SELECT float8 (1).

Chì ci hè più grande di l'infinitu ?

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

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

Fighjate cumu hè ! Risulta chì ci hè qualcosa più grande di l'infinitu, è hè NaN! À u listessu tempu, a ducumentazione PostgreSQL ci guarda cù ochji onesti è dichjara chì NaN hè ovviamente più grande di qualsiasi altru numeru, è, dunque, infinitu. U cuntrariu hè ancu veru per -NaN. Salute, amanti di a matematica! Ma avemu da ricurdà chì tuttu questu opera in u cuntestu di numeri veri.

Arrotondamentu di l'ochji

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

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

Un altru salutu inespettatu da a basa. À novu, ricordate chì i tipi di doppia precisione è numerichi anu effetti di arrotondamentu diffirenti. Per numericu - u solitu, quandu 0,5 hè arrotondatu, è per a doppia precisione - 0,5 hè arrotondatu versu l'enteru più vicinu.

U soldi hè qualcosa di speciale

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

Sicondu PostgreSQL, i soldi ùn hè micca un numeru veru. Sicondu certi individui, ancu. Avemu bisognu di ricurdà chì u casting u tipu di soldi hè pussibule solu à u tipu numericu, cum'è solu u tipu numericu pò esse cast à u tipu di soldi. Ma avà pudete ghjucà cun ellu cum'è u vostru core vole. Ma ùn sarà micca u listessu soldi.

Smallint è generazione di sequenza

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 ùn li piace micca perdi u tempu in trifles. Chì sò queste sequenze basate nantu à smallint? int, micca menu! Per quessa, quandu prova di eseguisce a quistione di sopra, a basa di dati prova di scaccià smallint à un altru tipu interu, è vede chì ci ponu esse parechji casts. Quale cast di sceglie ? Ella ùn pò micca decisu questu, è per quessa si sbatte cù un errore.

File numeru dui. "char"/char/varchar/text

Una quantità di stranezze sò ancu prisenti in tipi di caratteru. Cunniscitemu ancu elli.

Chì tippu di trucchi sò questi?

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

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

Chì tippu di "char" hè questu, chì tipu di clown hè questu? Ùn avemu micca bisognu di quelli... Perchè finta d'esse un charbon ordinariu, ancu s'ellu hè in virgulette. È difiere di un carattere regulare, chì hè senza virgulette, in u fattu chì pruduce solu u primu byte di a rapprisintazioni di a stringa, mentre chì un char nurmale produce u primu caratteru. In u nostru casu, u primu caratteru hè a lettera P, chì in a rapprisentazione unicode occupa 2 bytes, cum'è evidenza cunvertisce u risultatu à u tipu bytea. È u tippu "char" piglia solu u primu byte di sta rappresentazione Unicode. Allora perchè hè necessariu stu tipu? A documentazione PostgreSQL dice chì questu hè un tipu speciale utilizatu per i bisogni speciali. Dunque, hè improbabile chì avemu bisognu. Ma fighjate in i so ochji è ùn vi sbagliate micca quandu u scuntrà cù u so cumpurtamentu speciale.

Spazi extra. Fora di vista, fora di mente

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

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

Fighjate à l'esempiu datu. Aghju cunvertitu apposta tutti i risultati à u tipu di bytea, perchè era chjaramente visibile ciò chì ci era. Induve sò i spazii di fine dopu u casting à varchar (6) ? A documentazione succintamente dice: "Quandu u valore di u caratteru à un altru tipu di caratteru, i spazii bianchi sò scartati". Stu dislike deve esse ricurdatu. È nutate chì se una constante di stringa citata hè diretta direttamente à u tipu varchar (6), i spazii finali sò cunservati. Tali sò i miraculi.

File numeru trè. json/jsonb

JSON hè una struttura separata chì vive a so propria vita. Dunque, e so entità è quelli di PostgreSQL sò ligeramente diffirenti. Eccu esempi.

Johnson è Johnson. sente a diferenza

SELECT 'null'::jsonb IS NULL

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

A cosa hè chì JSON hà a so propria entità nulla, chì ùn hè micca l'analogue di NULL in PostgreSQL. À u listessu tempu, l'ughjettu JSON stessu pò avè u valore NULL, cusì l'espressione SELECT null::jsonb IS NULL (nota l'absenza di virgulette singuli) torna vera questa volta.

Una lettera cambia tuttu

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

A cosa hè chì json è jsonb sò strutture completamente diverse. In json, l'ughjettu hè guardatu cum'è hè, è in jsonb hè digià almacenatu in forma di una struttura indexata, analizata. Hè per quessa chì in u sicondu casu, u valore di l'ughjettu da a chjave 1 hè stata rimpiazzata da [1, 2, 3] à [7, 8, 9], chì hè ghjuntu in a struttura à a fine cù a stessa chjave.

Ùn beie micca acqua da a to faccia

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

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

PostgreSQL in a so implementazione JSONB cambia u furmatu di i numeri veri, purtendu à a forma classica. Questu ùn succede micca per u tipu JSON. Un pocu stranu, ma hà ragione.

U schedariu numeru quattru. data/ora/timestamp

Ci hè ancu qualchì stranezza cù tipi di data / ora. Fighjemu à elli. Lasciami fà una riservazione subitu chì alcune di e caratteristiche di cumportamentu diventanu chjaru se capisce bè l'essenza di travaglià cù i fusi orari. Ma questu hè ancu un tema per un articulu separatu.

U mo vostru ùn capisce micca

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

Sembra chì ciò chì hè incomprensibile quì ? Ma a basa di dati ùn capisce ancu ciò chì avemu messu in u primu locu quì - l'annu o u ghjornu? È ella decide chì hè u 99 di ghjennaghju di u 2008, chì a so mente. In generale, quandu si trasmette e date in u furmatu di testu, avete bisognu di cuntrollà assai currettamente quantu a basa di dati li hà ricunnisciutu (in particulare, analizà u paràmetru di datestyle cù u cumandamentu SHOW datestyle), postu chì l'ambiguità in questa materia pò esse assai caru.

Da induve avete pigliatu questu?

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

Perchè a basa di dati ùn pò micca capisce u tempu esplicitamente specificatu? Perchè u fusu orariu ùn hà micca una abbreviazione, ma un nome cumpletu, chì hè sensu solu in u cuntestu di una data, postu chì tene in contu a storia di i cambiamenti di u fusu orariu, è ùn viaghja micca senza data. È a stessa formulazione di a linea di u tempu suscite dumande - chì significava veramente u programatore? Per quessa, tuttu hè logicu quì, se guardate.

Chì ci hè di male ?

Imagine a situazione. Avete un campu in a vostra tavula cù u tipu timestamptz. Vulete indexà. Ma capisce chì a custruzzione di un indice nantu à questu campu ùn hè micca sempre ghjustificatu per via di a so alta selettività (quasi tutti i valori di stu tipu seranu unichi). Allora decide di riduce a selettività di l'indici casting u tipu à una data. È avete una sorpresa:

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

Chì ci hè? U fattu hè chì per scaccià un tipu timestamptz à un tipu di data, u valore di u paràmetru di u sistema TimeZone hè utilizatu, chì face a funzione di cunversione di u tipu dipende da un paràmetru persunalizatu, i.e. volatile. Tali funzioni ùn sò micca permessi in l'indici. In questu casu, duvete indicà esplicitamente in quale fusu orariu hè realizatu u tipu di cast.

Quandu avà ùn hè ancu avà in tuttu

Avemu abituatu à avà () riturnà a data / ora attuale, tenendu in contu u fusu orariu. Ma fighjate à e seguenti dumande:

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;

A data / l'ora hè tornata a stessa, ùn importa quantu tempu hè passatu da a dumanda precedente! Chì ci hè? U fattu hè chì avà () ùn hè micca l'ora attuale, ma l'ora di iniziu di a transazzione attuale. Dunque, ùn cambia micca in a transazzione. Ogni dumanda lanciata fora di u scopu di una transazzione hè impannillata in una transazzione implicitamente, chì hè per quessa chì ùn avemu micca nutatu chì u tempu tornatu da un simplice SELECT now (); in fattu, micca l'attuale ... Se vulete ottene un tempu attuale onestu, avete bisognu di utilizà a funzione clock_timestamp ().

U schedariu numeru cinque. pocu

Stranu un pocu

SELECT '111'::bit(4)

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

Chì latu deve esse aghjuntu i bits in casu di estensione di tipu? Sembra esse à manca. Ma solu a basa hà una opinione diversa nantu à sta materia. Attenti: se u numeru di cifre ùn currisponde micca à u casting di un tipu, ùn uttene micca ciò chì vulete. Questu hè applicà sia à l'aghjunzione di bits à a diritta sia à u trimming bits. Ancu à a diritta...

File numeru sei. Arrays

Ancu NULL ùn hà micca sparatu

SELECT ARRAY[1, 2] || NULL

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

Cum'è a ghjente normale suscitata nantu à SQL, aspittemu chì u risultatu di sta espressione sia NULL. Ma ùn era micca quì. Un array hè tornatu. Perchè? Perchè in questu casu a basa casts NULL à un array integer è implicitamente chjama a funzione array_cat. Ma ùn resta micca chjaru perchè stu "gattu array" ùn resetta micca l'array. Stu cumpurtamentu hè ancu solu deve esse ricurdatu.

Riassume. Ci sò parechje cose strane. A maiò parte di elli, sicuru, ùn sò micca cusì critichi per parlà di un cumpurtamentu sfarente inappropriatu. E altri sò spiegati da a facilità d'utilizazione o a freccia di a so applicabilità in certe situazioni. Ma à u listessu tempu, ci sò parechje sorprese. Dunque, avete bisognu di sapè di elli. Se truvate qualcosa di stranu o inusual in u cumpurtamentu di ogni tipu, scrivite in i cumenti, seraghju felice di aghjunghje à i dossiers dispunibili nantu à elli.

Source: www.habr.com

Add a comment