Nel loro aspetto esteriore nulla desta sospetti. Anzi, ti sembrano addirittura ben noti e familiari. Ma questo solo fino a quando non decidi di verificarli. È proprio in quel momento che rivelano la loro natura subdola, comportandosi in modo completamente diverso da come ti aspettavi. E talvolta fanno cose che ti fanno accapponare la pelle, come perdere dati sensibili di cui si dovevano occupare. Quando li metti faccia a faccia, dichiarano di non conoscersi, mentre nell'ombra lavorano ognuno per conto dell'altro sotto lo stesso tetto. È giunto il momento di farli venire allo scoperto. Affrontiamo anche noi questi tipi sospetti.
La tipizzazione dei dati in PostgreSQL, per quanto logica, può riservare sorprese inaspettate. In questo articolo cercheremo di chiarire alcune delle sue peculiarità, di capire le ragioni del suo comportamento strano e di scoprire come evitare problemi nella pratica quotidiana. A dire il vero, ho redatto questo articolo anche come sorta di guida per me stesso, una guida alla quale poter facilmente consultare in situazioni ambigue. Pertanto, sarà aggiornato man mano che si scoprono nuove sorprese dai tipi discutibili. Dunque, partiamo, instancabili esploratori di database!
Dossier numero uno. real/double precision/numeric/money
A prima vista, i tipi numerici sembrano i meno problematici in termini di sorprese comportamentali. Ma non è così. Quindi iniziamo da qui.
Abbiamo dimenticato come contare
SELECT 0.1::real = 0.1
?column?
boolean
---------
fQual è il problema? 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 in memoria. Poiché 0.1 non può essere rappresentato come una frazione binaria finita (sarà 0.0(0011) in forma binaria), i numeri con una precisione diversa saranno differenti, ed ecco il motivo per cui non sono uguali. In realtà, questo è un argomento per un articolo a parte, per cui non mi allargherò qui.
Da dove proviene l'errore?
SELECT double precision(1)
ERROR: errore di sintassi vicino a "("
LINEA 1: SELECT double precision(1)
^
********** Errore **********
ERROR: errore di sintassi vicino a "("
Stato SQL: 42601
Simbolo: 24Molti sanno che PostgreSQL consente la scrittura funzionale della conversione dei tipi. Ciò significa che è possibile scrivere non solo 1::int, ma anche int(1), il che è equivalente. Ma non per i tipi il cui nome è composto da più parole! Quindi, se vuoi convertire un valore numerico nel tipo double precision in forma funzionale, usa l'alias di questo tipo, float8, cioè SELECT float8(1).
Cosa c'è di più grande dell'infinito?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tEcco com'è! Si scopre che c'è qualcosa di più grande dell'infinito, ed è NaN! La documentazione di PostgreSQL ci osserva onestamente e afferma che NaN è sempre maggiore di qualsiasi altro numero, e quindi dell'infinito. È giusto considerare il contrario per -NaN. Salve, amanti dell'analisi matematica! Ma ricordate che tutto questo opera nel contesto dei numeri reali.
Rotondità 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 arrotondamenti diversi. Per numeric si tratta di un arrotondamento normale, dove 0,5 viene arrotondato verso l'alto, mentre per double precision l'arrotondamento di 0,5 avviene verso il numero intero pari più vicino.
Il denaro è qualcosa di speciale
SELECT '10'::money::float8
ERRORE: impossibile convertire il tipo money in double precision
LINEA 1: SELECT '10'::money::float8
^
********** Errore **********
ERRORE: impossibile convertire il tipo money in double precision
Stato SQL: 42846
Simbolo: 19Secondo PostgreSQL, il denaro non è un numero reale. Anche alcuni individui la pensano così. Dobbiamo però ricordare che la conversione del tipo money è possibile solo con il tipo numeric, mentre il tipo money può essere convertito solo nel tipo numeric. Con quest'ultimo, si può giocare come si vuole. Ma non saranno più quei soldi.
Smallint e generazione di sequenze
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERRORE: la funzione generate_series(smallint, smallint, smallint) non è univoca
LINEA 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
SUGGERIMENTO: Non è stato possibile scegliere la funzione migliore. Potresti dover aggiungere conversioni di tipo esplicite.
********** Errore **********
ERRORE: la funzione generate_series(smallint, smallint, smallint) non è univoca
Stato SQL: 42725
Suggerimento: Non è stato possibile scegliere la funzione migliore. Potresti dover aggiungere conversioni di tipo esplicite.
Simbolo: 18PostgreSQL non ama essere superficiale. Quali sequenze sulla base di smallint? int, almeno! Pertanto, quando si tenta di eseguire la query sopra riportata, il database cerca di convertire smallint in un altro tipo intero e si rende conto che ci sono diverse possibilità di conversione. Quale conversione scegliere? Non può decidere e quindi si verifica un errore.
Dossier numero due. «char»/char/varchar/text
Ci sono alcune stranezze anche nei tipi carattere. Facciamo anche la loro conoscenza.
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 | П | xd09fCos'è questo tipo «char», chi è questo clown? Non ci servono tipi del genere… Perché si finge un normale char, anche se tra virgolette. Si differenzia dal normale char, che non è tra virgolette, in quanto restituisce solo il primo byte della rappresentazione della stringa, mentre un char normale restituisce il primo carattere. Nel nostro caso, il primo carattere è la lettera П, che nella rappresentazione unicode richiede 2 byte, come dimostra 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 esigenze particolari. Quindi è improbabile che ci serva. Ma guardalo negli occhi e non sbagliare quando lo incontri con il suo comportamento particolare.
Spazi inutili. Fuori dalla vista, fuori dal cuore.
SELEZIONA 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Dai un'occhiata a questo esempio. Ho voluto mostrare tutti i risultati nel tipo bytea per evidenziare cosa ci sia dentro. Dove sono gli spazi finali dopo la conversione in varchar(6)? La documentazione afferma concisamente: «Quando un valore di tipo character è convertito in un altro tipo di carattere, gli spazi supplementari vengono scartati». Questa avversione va ricordata. E nota che, se la costante stringa tra virgolette è subito convertita in varchar(6), gli spazi finali vengono conservati. Questi sono i misteri.
Dossier numero tre. json/jsonb
JSON è una struttura separata che vive di vita propria. Pertanto, le sue entità e le entità di PostgreSQL differiscono leggermente. Ecco alcuni esempi.
Johnson e Johnson. Senti la differenza
SELEZIONA 'null'::jsonb È NULL
?column?
boolean
---------
fIl punto è che JSON ha la propria entità null, che non è equivalente a NULL in PostgreSQL. Tuttavia, un oggetto JSON può avere un valore NULL, quindi l'espressione SELECT null::jsonb È NULL (nota l'assenza di virgolette) questa volta 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]}La questione è che json e jsonb sono strutture completamente diverse. In json, l'oggetto è memorizzato così com'è, mentre in jsonb è memorizzato in una struttura indicizzata già analizzata. Ecco perché nel secondo caso il valore dell'oggetto con la chiave 1 è stato sostituito da [1, 2, 3] a [7, 8, 9], che è arrivato nella struttura alla fine con la stessa chiave.
Dalla faccia non si beve acqua
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 di JSONB cambia il formato dei numeri reali, portandoli a una forma classica. Per il tipo JSON non succede. È un po' strano, ma è un suo diritto.
Dossier numero quattro. date/time/timestamp
Ci sono delle stranezze anche con i tipi di data/ora. Analizziamole. Preciso subito che alcune delle caratteristiche comportamentali si chiariscono se si comprende bene come funzionano i fusi orari. Ma questo è anche un tema per un articolo a parte.
Non capisco te, non capisco.
SELECT '08-Jan-99'::date
ERRORE: valore del campo data/ora fuori intervallo: "08-Jan-99"
LINEA 1: SELECT '08-Jan-99'::date
^
SUGGERIMENTO: Forse hai bisogno di un'impostazione di "datestyle" diversa.
********** Errore **********
ERRORE: valore del campo data/ora fuori intervallo: "08-Jan-99"
Stato SQL: 22008
Suggerimento: Forse hai bisogno di un'impostazione di "datestyle" diversa.
Simbolo: 8A prima vista, cosa c'è di incomprensibile? Ma la base dati non capisce cosa abbiamo messo per primo — l'anno o il giorno? E decide che si tratta del 99 gennaio 2008, il che le fa saltare la testa. In effetti, quando si forniscono date in formato testuale, è fondamentale controllare attentamente quanto bene la base dati le ha riconosciute (in particolare, analizzare il parametro datestyle con il comando SHOW datestyle), poiché le ambiguità in questo campo possono costare molto care.
Da dove sei sbucato?
SELECT '04:05 Europe/Moscow'::time
ERRORE: sintassi di input non valida per il tipo time: "04:05 Europe/Moscow"
LINEA 1: SELECT '04:05 Europe/Moscow'::time
^
********** Errore **********
ERRORE: sintassi di input non valida per il tipo time: "04:05 Europe/Moscow"
Stato SQL: 22007
Simbolo: 8Perché il database non può comprendere il tempo specificato chiaramente? Perché per il fuso orario è stata indicata la denominazione completa e non l'abbreviazione, che ha senso solo nel contesto di una data, poiché tiene conto della storia degli cambiamenti dei fusi orari, cosa che non funziona senza una data. E anche la formulazione della stringa di tempo solleva dubbi: cosa intendeva davvero il programmatore? Quindi, qui tutto è logico, se si analizza.
Cosa non va?
Immaginate una situazione. Nel vostro tavolo avete un campo di tipo timestamptz. Volete indicizzarlo. Ma capite 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, decidete di ridurre la selettività dell'indice, portando questo tipo a una data. E ricevete una sorpresa:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING 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: 42P17Il problema è che per convertire il tipo timestamptz in un tipo date si utilizza il valore del parametro di sistema TimeZone, il che rende la funzione di conversione dipendente da un parametro configurabile, cioè volatile. Tali funzioni non possono essere usate negli indici. In questo caso, è necessario specificare esplicitamente in quale fuso orario avviene la conversione del tipo.
Quando now non è affatto now
Siamo abituati a considerare che now() restituisca la data/ora attuale tenendo conto del fuso orario. Ma guarda le seguenti query:
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;Data/ora restituita è sempre la stessa, indipendentemente da quanto tempo è passato dall'ultima richiesta! Qual è il problema? Il fatto che now() non rappresenta il tempo attuale, ma il tempo d'inizio della transazione corrente. Pertanto, all'interno della transazione, non cambia. Qualsiasi richiesta eseguita al di fuori della transazione viene avvolta in una transazione in modo implicito, motivo per cui non notiamo che il tempo restituito da una semplice richiesta SELECT now(); in realtà non è l'attuale... Se desiderate ottenere l'orario attuale, dovete usare la funzione clock_timestamp().
Dossier numero cinque. bit
Strana un po'
SELECT '111'::bit(4)
bit
bit(4)
------
1110Da che parte bisognerebbe aggiungere i bit in caso di espansione del tipo? Sembra a sinistra. Ma il database ha un'altra opinione al riguardo. Fate attenzione: se il numero di bit non corrisponde durante la conversione del tipo, otterrete qualcosa di completamente diverso da ciò che volevate. Questo vale sia per l'aggiunta di bit a destra, sia per il taglio dei bit. Anche questo a destra...
Dossier numero sei. Array
Nemmeno NULL ha funzionato
SELECT ARRAY[1, 2] || NULL
?colonna?
integer[]
---------
{1,2}Come persone normali, cresciute con SQL, ci aspettiamo che il risultato di questa espressione sia NULL. E invece no. Viene restituito un array. Perché? Perché in questo caso il database converte NULL in un array intero e chiama implicitamente la funzione array_cat. Eppure è ancora poco chiaro perché questo 'gattino array' non azzera l'array. Comportamenti di questo tipo vanno semplicemente memorizzati.
Facciamo un riepilogo. Ci sono molte stranezze. La maggior parte di esse, ovviamente, non è così critica da giustificare un comportamento inaccettabile. Altre sono spiegate dal comfort d'uso o dalla loro frequenza di applicazione in certe situazioni. Tuttavia ci sono molte sorprese. È bene esserne informati. Se trovi qualcosa di strano o insolito nel comportamento di qualsiasi tipo, scrivi nei commenti, sarò lieto di ampliare i dossier esistenti.
Fonte: habr.com
