Non c'è nulla di sospetto nel loro aspetto. Inoltre, ti sembrano anche familiari bene e per molto tempo. Ma questo solo finché non li controlli. È qui che mostrano la loro natura insidiosa, lavorando in modo completamente diverso da quanto ti aspettavi. E a volte fanno qualcosa che ti fa rizzare i capelli, ad esempio perdono i dati segreti loro affidati. Quando li confronti, affermano di non conoscersi, anche se nell'ombra lavorano diligentemente sotto lo stesso cappuccio. È ora di portarli finalmente all'acqua pulita. Affrontiamo anche questi tipi sospetti.
La digitazione dei dati in PostgreSQL, nonostante tutta la sua logica, a volte presenta sorprese molto strane. In questo articolo cercheremo di chiarire alcune loro stranezze, comprendere il motivo del loro strano comportamento e capire come non incorrere in problemi nella pratica quotidiana. A dire il vero, ho compilato questo articolo anche come una sorta di libro di consultazione per me stesso, un libro di consultazione a cui si possa facilmente fare riferimento in casi controversi. Pertanto, verrà reintegrato man mano che verranno scoperte nuove sorprese da parte di tipi sospetti. Quindi, andiamo, oh instancabili tracker di database!
Dossier numero uno. reale/doppia precisione/numerico/denaro
Sembrerebbe che i tipi numerici siano i meno problematici in termini di sorprese nel comportamento. Ma non importa come sia. Quindi cominciamo con loro. COSÌ…
Ho dimenticato come contare
SELECT 0.1::real = 0.1
?column?
boolean
---------
fQual è il problema? Il problema è che PostgreSQL converte la costante non tipizzata 0.1 in doppia precisione e tenta di confrontarla con 0.1 di tipo reale. E questi sono significati completamente diversi! L'idea è di rappresentare i numeri reali nella memoria della macchina. Poiché 0.1 non può essere rappresentato come una frazione binaria finita (sarebbe 0.0 (0011) in binario), i numeri con profondità di bit diverse saranno diversi, da qui il risultato che non sono uguali. In generale, questo è un argomento per un articolo separato, non scriverò più in dettaglio qui.
Da dove viene 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
Символ: 24Molte persone sanno che PostgreSQL consente la notazione funzionale per il cast dei tipi. Cioè, puoi 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! Pertanto, se si desidera eseguire il cast di un valore numerico sul tipo a doppia precisione in forma funzionale, utilizzare l'alias di questo tipo float8, ovvero SELECT float8(1).
Cosa c'è di più grande dell'infinito?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tGuarda com'è! Si scopre che esiste qualcosa di più grande dell'infinito ed è NaN! Allo stesso tempo, la documentazione di PostgreSQL ci guarda con occhi onesti e afferma che NaN è ovviamente maggiore di qualsiasi altro numero e, quindi, infinito. Per -NaN vale anche il contrario. Ciao, amanti della matematica! Ma dobbiamo ricordare che tutto ciò opera 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 dalla base. Ancora una volta, ricorda che la doppia precisione e i tipi numerici hanno effetti di arrotondamento diversi. Per numerico - quello normale, quando 0,5 viene arrotondato per eccesso, e per doppia precisione - 0,5 viene arrotondato all'intero pari più vicino.
Il denaro è 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
Символ: 19Secondo PostgreSQL, il denaro non è un numero reale. Anche secondo alcuni. Dobbiamo ricordare che il cast del tipo money è possibile solo al tipo numerico, così come solo il tipo numerico può essere castato al tipo money. Ma ora puoi giocarci come desidera il tuo cuore. Ma non saranno gli stessi soldi.
Smallint e generazione di sequenze
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.
Символ: 18A PostgreSQL non piace perdere tempo in sciocchezze. Quali sono queste sequenze basate su smallint? nientemeno che! Pertanto, quando si tenta di eseguire la query precedente, il database tenta di eseguire il cast di smallint su un altro tipo intero e vede che potrebbero esserci diversi cast di questo tipo. Quale cast scegliere? Non può deciderlo e quindi si blocca con un errore.
Fascicolo numero due. "char"/char/varchar/testo
Una serie di stranezze sono presenti anche nei tipi di carattere. Conosciamo anche loro.
Che tipo di trucchi sono questi?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fChe tipo di "personaggio" è questo, che tipo di clown è questo? Non ne abbiamo bisogno... Perché finge di essere un carattere normale, anche se è tra virgolette. E differisce da un carattere normale, che è senza virgolette, in quanto restituisce solo il primo byte della rappresentazione della stringa, mentre un carattere normale restituisce il primo carattere. Nel nostro caso il primo carattere è la lettera P, che nella rappresentazione unicode occupa 2 byte, come evidenziato convertendo il risultato nel tipo bytea. E il tipo "char" richiede solo il primo byte di questa rappresentazione Unicode. Allora perché è necessario questo tipo? La documentazione di PostgreSQL dice che questo è un tipo speciale utilizzato per esigenze particolari. Quindi difficilmente ne avremo bisogno. Ma guardalo negli occhi e non sbaglierai quando lo incontrerai con il suo comportamento speciale.
Spazi aggiuntivi. Lontano dagli occhi, 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 | x616263202020Dai un'occhiata all'esempio fornito. Ho convertito appositamente tutti i risultati nel tipo bytea, in modo che fosse chiaramente visibile cosa c'era. Dove sono gli spazi finali dopo il cast su varchar(6)? La documentazione afferma succintamente: "Quando si trasmette il valore di carattere a un altro tipo di carattere, lo spazio bianco finale viene scartato." Questa antipatia deve essere ricordata. E tieni presente che se una costante di stringa tra virgolette viene convertita direttamente nel tipo varchar(6), gli spazi finali vengono conservati. Questi sono i miracoli.
Fascicolo numero tre. json/jsonb
JSON è una struttura separata che vive la propria vita. Pertanto, le sue entità e quelle di PostgreSQL sono leggermente diverse. Ecco alcuni esempi.
Johnson e Johnson. senti la differenza
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fIl fatto è che JSON ha una propria entità nulla, che non è l'analogo di NULL in PostgreSQL. Allo stesso tempo, l'oggetto JSON stesso potrebbe avere il valore NULL, quindi l'espressione SELECT null::jsonb IS NULL (notare l'assenza di virgolette singole) 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]}Il fatto è che json e jsonb sono strutture completamente diverse. In json, l'oggetto viene archiviato così com'è e in jsonb è già archiviato sotto forma di struttura analizzata e indicizzata. Ecco perché nel secondo caso il valore dell'oggetto con la chiave 1 è stato sostituito da [1, 2, 3] a [7, 8, 9], che è entrato nella struttura proprio alla fine con la stessa chiave.
Non bere l'acqua dal viso
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 modifica la formattazione dei numeri reali, portandoli alla forma classica. Ciò non accade per il tipo JSON. Un po' strano, ma ha ragione.
Fascicolo numero quattro. data/ora/timbro temporale
Ci sono anche alcune stranezze con i tipi di data/ora. Diamo un'occhiata a loro. Vorrei fare subito una prenotazione sul fatto che alcune caratteristiche comportamentali diventano chiare se si comprende bene l'essenza del lavoro con i fusi orari. Ma questo è anche argomento per un articolo a parte.
Il mio non capisco il tuo
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.
Символ: 8Sembrerebbe che cosa sia incomprensibile qui? Ma il database ancora non capisce cosa mettiamo al primo posto qui: l’anno o il giorno? E decide che è il gennaio 99 del 2008, cosa che la lascia a bocca aperta. In generale, quando si trasmettono date in formato testo, è necessario verificare molto attentamente se il database le ha riconosciute correttamente (in particolare, analizzare il parametro datestyle con il comando SHOW datestyle), poiché le ambiguità in questa materia possono essere molto costose.
Da dove vieni?
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
Символ: 8Perché il database non riesce a comprendere l'ora specificata esplicitamente? Perché il fuso orario non ha un'abbreviazione, ma un nome completo, che ha senso solo nel contesto di una data, poiché tiene conto della cronologia dei cambiamenti di fuso orario e senza data non funziona. E la formulazione stessa della linea temporale solleva domande: cosa intendeva veramente il programmatore? Pertanto, qui tutto è logico, se lo guardi.
Cosa c'è che non va in lui?
Immagina la situazione. Hai un campo nella tua tabella con tipo timestamptz. Vuoi indicizzarlo. Ma capisci che costruire un indice su questo campo non è sempre giustificato a causa della sua elevata selettività (quasi tutti i valori di questo tipo saranno univoci). Quindi decidi di ridurre la selettività dell'indice convertendo il tipo in una data. E avrai 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-состояние: 42P17Qual è il problema? Il fatto è che per convertire un tipo timestamptz in un tipo data, viene utilizzato il valore del parametro di sistema TimeZone, che rende la funzione di conversione del tipo dipendente da un parametro personalizzato, ad es. volatile. Tali funzioni non sono consentite nell'indice. In questo caso è necessario indicare esplicitamente in quale fuso orario viene eseguito il cast di tipo.
Quando adesso non è nemmeno adesso
Siamo abituati a now() che restituisce la data/ora corrente, 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;La data/ora viene restituita la stessa, indipendentemente da quanto tempo è trascorso dalla richiesta precedente! Qual è il problema? Il fatto è che now() non è l'ora corrente, ma l'ora di inizio della transazione corrente. Pertanto, non cambia all'interno della transazione. Qualsiasi query lanciata al di fuori dell'ambito di una transazione è implicitamente racchiusa in una transazione, motivo per cui non notiamo che l'ora restituita da una semplice SELECT now(); in effetti, non quella attuale... Se vuoi ottenere un'ora corrente onesta, devi usare la funzione clock_timestamp().
Fascicolo numero cinque. morso
Un po' strano
SELECT '111'::bit(4)
bit
bit(4)
------
1110Da che parte devono essere aggiunti i bit in caso di estensione del tipo? Sembra di essere a sinistra. Ma solo la base ha un'opinione diversa su questo argomento. Fai attenzione: se il numero di cifre non corrisponde quando trasmetti un tipo, non otterrai ciò che volevi. Questo vale sia per aggiungere bit a destra che per tagliare bit. Anche a destra...
Fascicolo numero sei. Array
Anche NULL non si è attivato
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 c'era. Viene restituito un array. Perché? Perché in questo caso la base lancia NULL su un array di numeri interi e chiama implicitamente la funzione array_cat. Ma non è ancora chiaro il motivo per cui questo “array cat” non ripristina l’array. Anche questo comportamento deve solo essere ricordato.
Riassumere. Ci sono molte cose strane. La maggior parte di loro, ovviamente, non è così critica da parlare di comportamenti palesemente inappropriati. E altri sono spiegati dalla facilità d'uso o dalla frequenza della loro applicabilità in determinate situazioni. Ma allo stesso tempo ci sono molte sorprese. Pertanto, è necessario conoscerli. Se trovi qualcos'altro di strano o insolito nel comportamento di qualsiasi tipo, scrivilo nei commenti, sarò felice di integrare i dossier disponibili su di loro.
Fonte: habr.com
