Nel loro aspetto esteriore non c'è nulla che susciti sospetti. Anzi, sembrano addirittura familiari e ben noti. Ma è solo finché non decidi di verificarli. Ecco dove rivelano la loro natura insidiosa, comportandosi in un modo del tutto diverso da quello che ti aspettavi. A volte fanno cose talmente strane che i capelli si rizzano: per esempio, perdono dati segreti di cui si fidano. Quando li metti faccia a faccia, affermano di non conoscersi, mentre nell'ombra lavorano diligentemente sotto lo stesso tetto. È finalmente tempo di farli emergere in trasparenza. Analizziamo insieme questi tipi sospetti.
La tipizzazione dei dati in PostgreSQL, per quanto logica, a volte presenta sorprese davvero strane. In questo articolo cercheremo di chiarire alcune delle loro eccentricità, di capire il motivo del loro comportamento strano e di comprendere come evitare problemi nella pratica quotidiana. A dire il vero, ho scritto questo articolo anche come una sorta di guida per me stesso, un riferimento a cui poter facilmente attingere in caso di controversie. Pertanto, verrà aggiornato man mano che emergeranno nuove sorprese dai tipi sospetti. Ecco, quindi, partiamo, instancabili cacciatori di dati!
Dossier numero uno. real/double precision/numeric/money
A prima vista, i tipi numerici sembrano essere i meno problematici in termini di sorprese nel comportamento. Ma non è affatto così. Pertanto, iniziamo con loro. Quindi…
Hanno dimenticato come contare
SELECT 0.1::real = 0.1
?colonna?
boolean
---------
fQual è il problema? Il fatto è che PostgreSQL converte la costante non tipizzata 0.1 nel tipo double precision e cerca di confrontarla con 0.1 di tipo real. E questi sono valori completamente diversi! La questione riguarda la rappresentazione dei numeri in virgola mobile nella memoria della macchina. Poiché 0.1 non può essere rappresentato come una frazione binaria finita (sarà 0.0(0011) in forma binaria), i numeri con diverse precisioni differiranno, da qui il risultato che non sono uguali. In effetti, questo è un argomento per un articolo separato, quindi non mi dilungherò qui.
Da dove deriva l'errore?
SELECT double precision(1)
ERRORE: errore di sintassi vicino a "("
LINEA 1: SELECT double precision(1)
^
********** Errore **********
ERRORE: errore di sintassi vicino a "("
Stato SQL: 42601
Simbolo: 24Molti sanno che PostgreSQL consente una scrittura funzionale per la conversione dei tipi. Cioè, è possibile scrivere non solo 1::int, ma anche int(1), che sarà equivalente. Ma non per i tipi i cui nomi sono composti da più parole! Quindi, se desiderate convertire un valore numerico nel tipo double precision in forma funzionale, utilizzate l'alias di questo tipo float8, ossia SELECT float8(1).
Cosa c'è di più dell'infinito?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tEcco come! Si scopre che c'è qualcosa di più grande dell'infinito, ed è NaN! E nonostante ciò, la documentazione di PostgreSQL ci guarda con occhi onesti e afferma che NaN è di gran lunga maggiore di qualsiasi altro numero e, quindi, dell'infinito. Vale lo stesso anche per -NaN. Ciao, amanti dell'analisi matematica! Ma bisogna ricordare che tutto ciò vale nel contesto dei numeri reali.
Arrotondamento degli occhi
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Un altro saluto inaspettato dal database. E ancora una volta bisogna ricordare che per i tipi double precision e numeric si applicano diverse regole di arrotondamento. Per numeric c'è l'arrotondamento standard, dove 0,5 viene arrotondato per eccesso, mentre per double precision, 0,5 viene arrotondato al numero intero pari più vicino.
Il denaro è qualcosa di speciale
SELECT '10'::money::float8
ERROR: impossibile convertire il tipo money in double precision
LINE 1: SELECT '10'::money::float8
^
********** Errore **********
ERROR: impossibile convertire il tipo money in double precision
SQL-state: 42846
Carattere: 19Secondo PostgreSQL, il denaro non è un numero reale. Secondo alcuni individui, nemmeno. Dobbiamo ricordare che la conversione del tipo money è possibile solo verso il tipo numeric, così come il tipo money può essere convertito solo in tipo numeric. E già con quello si può giocare come si desidera. Ma questi non saranno più i veri soldi.
Smallint e generazione di sequenze
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERROR: la funzione generate_series(smallint, smallint, smallint) non è unica
LINE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
HINT: Impossibile scegliere la funzione candidata migliore. Potresti aver bisogno di aggiungere conversioni di tipo esplicite.
********** Errore **********
ERROR: la funzione generate_series(smallint, smallint, smallint) non è unica
SQL-state: 42725
Suggerimento: Impossibile scegliere la funzione candidata migliore. Potresti aver bisogno di aggiungere conversioni di tipo esplicite.
Carattere: 18Non ama fare le cose con poco riguardo PostgreSQL. Che sequenze ci sono basate su smallint? int, minimo! Quindi, quando si tenta di eseguire la query sopra riportata, il database cerca di convertire smallint in un altro tipo numerico, e si rende conto che ci possono essere diverse conversioni. Quale conversione scegliere? Questo non può deciderlo, e perciò si blocca con un errore.
Dossier numero due. «char»/char/varchar/text
Ci sono diverse stranezze anche nei tipi di caratteri. Incontriamole anche noi.
Che trucchi sono questi?
SELECT 'PETYA'::"char"
, 'PETYA'::"char"::bytea
, 'PETYA'::char
, 'PETYA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fChe tipo è questo «char», che clown è? Non ci serve… Perché si fa passare per un normale char, anche se è tra virgolette. Differisce dal normale char, che è senza virgolette, in quanto restituisce solo il primo byte della rappresentazione della stringa, mentre il normale char restituisce il primo carattere. Nel nostro caso, il primo carattere è la lettera П, che in rappresentazione unicode occupa 2 byte, come indica la conversione del risultato nel tipo bytea. E il tipo «char» prende solo il primo byte di questa rappresentazione unicode. Allora, a cosa serve questo tipo? La documentazione di PostgreSQL dice che è un tipo speciale usato per scopi particolari. Quindi probabilmente non ci sarà utile. Ma guardagli negli occhi e non sbagliarti quando lo incontri con il suo comportament speciale.
Spazi bianchi superflui. Fuori dalla vista, lontano dal cuore.
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Guarda l'esempio fornito. Ho appositamente convertito tutti i risultati al tipo bytea per mostrare chiaramente cosa c'è dentro. Dove sono gli spazi finali dopo la conversione al tipo varchar(6)? La documentazione afferma chiaramente: «Durante la conversione di un valore character in un altro tipo di carattere, gli spazi aggiuntivi vengono scartati». Questa non gradita abitudine bisogna ricordarla. E nota che se la Costante di stringa in virgolette viene subito convertita al tipo varchar(6), gli spazi finali vengono mantenuti. Ottime sorprese.
Dossier numero tre. json/jsonb
JSON è una struttura separata che vive la sua vita. Pertanto, le sue entità e le entità di PostgreSQL differiscono leggermente. Ecco alcuni esempi.
Johnson & Johnson. Senti la differenza
SELECT 'null'::jsonb IS NULL
?column?
bool
---------
fIl punto è che JSON ha la sua entità null, che non è equivalente a NULL in PostgreSQL. Allo stesso tempo, l'oggetto JSON può avere un valore NULL, quindi l'espressione SELECT null::jsonb IS NULL (nota l'assenza di virgolette semplici) restituirà true.
Una lettera cambia tutto
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]}Il fatto è che json e jsonb sono strutture completamente diverse. In json, l'oggetto è memorizzato così com'è, mentre in jsonb è memorizzato come una struttura indicizzata e analizzata. Per questo motivo, nel secondo caso, il valore dell'oggetto con chiave 1 è stato sostituito da [1, 2, 3] a [7, 8, 9], che era entrato nella struttura alla fine con la stessa chiave.
Dall'acqua non si beve
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL nella sua implementazione JSONB cambia il formato dei numeri decimali, portandoli in una forma classica. Per il tipo JSON ciò non avviene. Un po' strano, ma è il suo diritto.
Dossier numero quattro. date/time/timestamp
Anche con i tipi di data/ora ci sono alcune stranezze. Vediamole. Pre-metto che alcune delle peculiarità del comportamento si rendono chiare se si comprende bene il funzionamento dei fusi orari. Ma questo è anche un tema per un articolo a parte.
Non capisco
SELECT '08-Jan-99'::date
ERROR: valore del campo data/ora fuori intervallo: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Forse hai bisogno di una diversa impostazione di "datestyle".
********** Errore **********
ERROR: valore del campo data/ora fuori intervallo: "08-Jan-99"
Stato SQL: 22008
Suggerimento: Forse hai bisogno di una diversa impostazione di "datestyle".
Simbolo: 8Sembrerebbe che non ci sia nulla di incomprensibile. Ma la base dati non capisce se abbiamo messo prima l'anno o il giorno? E decide che sia il 99 gennaio 2008, il che la manda in tilt. In generale, nel caso si trasmetta date in formato testuale, bisogna prestare molta attenzione a come la base le ha riconosciute (in particolare, analizzare il parametro datestyle con il comando SHOW datestyle), poiché le ambiguità in questo senso possono costare molto.
Da dove sei venuto?
SELEZIONA '04:05 Europe/Moscow'::time
ERRORE: sintassi di input non valida per il tipo ora: "04:05 Europe/Moscow"
LINEA 1: SELEZIONA '04:05 Europe/Moscow'::time
^
********** Errore **********
ERRORE: sintassi di input non valida per il tipo ora: "04:05 Europe/Moscow"
Stato SQL: 22007
Simbolo: 8Perché il database non può comprendere l'ora specificata? Perché per il fuso orario è indicato il nome completo e non l'abbreviazione, la quale ha senso solo nel contesto di una data, poiché tiene conto della storia dei cambiamenti dei fusi orari, e questa senza una data non funziona. Inoltre, la formulazione stessa della stringa di tempo solleva domande: cosa voleva realmente significare il programmatore? Quindi, qui tutto è logico, se si fa attenzione.
Cosa non va?
Immagina una situazione. Hai un campo nella tua tabella di tipo timestamptz. Vuoi indicizzarlo. Ma capisci che costruire un indice su questo campo non è sempre giustificato a causa della sua alta selettività (quasi tutti i valori di questo tipo saranno unici). Pertanto decidi di ridurre la selettività dell'indice, convertendo questo tipo a una data. E ottieni una sorpresa:
CREA INDICE "iIdent-DateLastUpdate"
SULLA pubblica."Ident" UTILIZZANDO btree
(("DTLastUpdate"::date));
ERRORE: le funzioni nell'espressione dell'indice devono essere contrassegnate come IMMUTABLE
********** Errore **********
ERRORE: le funzioni nell'espressione dell'indice devono essere contrassegnate come IMMUTABLE
Stato SQL: 42P17Qual è il problema? Il fatto è che per convertire il tipo timestamptz in tipo date viene utilizzato il valore del parametro di sistema TimeZone, il che rende la funzione di conversione del tipo dipendente da un parametro configurabile, cioè variabile (volatile). Tali funzioni non sono ammissibili nell'indice. In questo caso, è necessario specificare esplicitamente in quale fuso orario viene effettuata la conversione del tipo.
Quando now non è affatto now
Siamo abituati a pensare che now() restituisca la data/ora attuale tenendo conto del fuso orario. Ma guarda le seguenti query:
INIZIA TRANSAZIONE;
SELEZIONA now();
now
timestamp con fuso orario
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELEZIONA now();
now
timestamp con fuso orario
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELEZIONA now();
now
timestamp con fuso orario
-----------------------------
2019-11-26 13:13:04.271419+03
COSTIUISCI;La data/orario rimangono gli stessi indipendentemente da quanto tempo sia passato dall'ultima richiesta! Qual è il problema? Il fatto è che now() non è l'ora corrente, ma l'ora di inizio della transazione attuale. Pertanto, all'interno della transazione non cambia. Qualsiasi richiesta eseguita al di fuori della transazione viene avvolta in una transazione implicitamente, quindi non ci rendiamo conto che l'ora restituita da una semplice richiesta SELECT now(); in realtà non è quella corrente... Se desideri ottenere il tempo corrente corretto, devi utilizzare la funzione clock_timestamp().
Dossier numero cinque. bit
Strano un po'.
SELECT '111'::bit(4)
bit
bit(4)
------
1110Da che parte dovrebbero essere aggiunti i bit nel caso di espansione del tipo? Sembra che a sinistra. Ma solo che il database ha un'opinione diversa in merito. Fai attenzione: se il numero di bit non corrisponde durante la conversione del tipo, otterrai qualcosa di molto diverso da ciò che volevi. Questo si applica sia all'aggiunta di bit a destra sia al taglio di bit. Anche questo a destra...
Dossier numero sei. Array
Nemmeno NULL ha sparato.
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Come persone normali, cresciute con SQL, ci aspettiamo che il risultato di questa espressione sia NULL. Ma non è così. Viene restituito un array. Perché? Perché in questo caso il database converte NULL in un array intero e chiama implicitamente la funzione array_cat. Ma resta comunque poco chiaro il motivo per cui questo "gattino array" non azzera l'array. Questo comportamento deve semplicemente essere ricordato.
In sintesi. Ci sono molte stranezze. La maggior parte di esse, ovviamente, non è così critica da parlare di un comportamento scandalosamente inadeguato. Altre si spiegano con la facilità d'uso o la frequenza della loro applicabilità in determinate situazioni. Ma ci sono anche molte sorprese. Quindi è importante esserne a conoscenza. Se trovi qualcosa di strano o insolito nel comportamento di alcuni tipi, scrivi nei commenti, sarò felice di aggiungere al dossier esistente.
Fonte: habr.com
