No hi ha res sospitós sobre el seu aspecte. A més, fins i tot us semblen familiars bé i durant molt de temps. Però això és només fins que els comproveu. Aquí és on mostren la seva naturalesa insidiosa, treballant completament diferent del que esperaves. I de vegades fan alguna cosa que et posa els cabells de punta; per exemple, perden les dades secretes que se'ls confia. Quan els enfrontes, afirmen que no es coneixen, tot i que a l'ombra treballen molt sota el mateix capó. És hora de portar-los finalment a l'aigua neta. Tractem també amb aquests tipus sospitosos.
L'escriptura de dades a PostgreSQL, amb tota la seva lògica, de vegades presenta sorpreses molt estranyes. En aquest article intentarem aclarir algunes de les seves peculiaritats, entendre el motiu del seu comportament estrany i comprendre com no trobar-se amb problemes en la pràctica diària. Per dir-te la veritat, vaig compilar aquest article també com una mena de llibre de referència per a mi mateix, un llibre de referència al qual es podria fer referència fàcilment en casos polèmics. Per tant, es reomplirà a mesura que es descobrin noves sorpreses de tipus sospitosos. Així doncs, anem, oh rastrejadors de bases de dades incansables!
Dossier número u. real/doble precisió/numèric/diners
Sembla que els tipus numèrics són els menys problemàtics pel que fa a les sorpreses de comportament. Però no importa com sigui. Així que comencem per ells. Tan…
He oblidat com comptar
SELECT 0.1::real = 0.1
?column?
boolean
---------
fQuè passa? El problema és que PostgreSQL converteix la constant no tipificada 0.1 a doble precisió i intenta comparar-la amb 0.1 de tipus real. I aquests són significats completament diferents! La idea és representar nombres reals a la memòria de la màquina. Com que 0.1 no es pot representar com una fracció binària finita (seria 0.0 (0011) en binària), els nombres amb diferents profunditats de bits seran diferents, per tant, el resultat que no són iguals. En termes generals, aquest és un tema per a un article separat; no escriuré amb més detall aquí.
D'on ve l'error?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Molta gent sap que PostgreSQL permet la notació funcional per al càsting de tipus. És a dir, podeu escriure no només 1::int, sinó també int(1), que serà equivalent. Però no per als tipus els noms dels quals consisteixen en diverses paraules! Per tant, si voleu emetre un valor numèric al tipus de precisió doble en forma funcional, utilitzeu l'àlies d'aquest tipus float8, és a dir, SELECT float8(1).
Què hi ha més gran que l'infinit?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tMira com és! Resulta que hi ha alguna cosa més gran que l'infinit, i és NaN! Al mateix temps, la documentació de PostgreSQL ens mira amb ulls sincers i afirma que NaN és òbviament més gran que qualsevol altre nombre i, per tant, infinit. El contrari també és cert per a -NaN. Hola, amants de les matemàtiques! Però hem de recordar que tot això opera en el context de nombres reals.
Arrodoniment d'ulls
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Una altra salutació inesperada des de la base. De nou, recordeu que els tipus de doble precisió i numèrics tenen efectes d'arrodoniment diferents. Per a numèrics, l'habitual, quan s'arrodoneix 0,5 cap amunt, i per a la doble precisió, 0,5 s'arrodoneix cap a l'enter parell més proper.
Els diners són una cosa especial
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
Символ: 19Segons PostgreSQL, els diners no són un nombre real. Segons algunes persones, també. Hem de recordar que només es pot emetre el tipus de diners al tipus numèric, de la mateixa manera que només es pot emetre el tipus numèric al tipus de diners. Però ara pots jugar amb ell com vulguis el teu cor. Però no seran els mateixos diners.
Smallint i generació de seqüències
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 no li agrada perdre el temps amb petiteses. Quines són aquestes seqüències basades en smallint? int, ni menys! Per tant, quan s'intenta executar la consulta anterior, la base de dades prova d'emetre smallint a algun altre tipus d'enter i veu que hi pot haver diversos models d'aquest tipus. Quin repartiment triar? Ella no pot decidir-ho i, per tant, s'estavella amb un error.
Fitxa número dos. "char"/char/varchar/text
També hi ha una sèrie de curiositats en els tipus de personatges. Anem a conèixer-los també.
Quin tipus de trucs són aquests?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fQuin tipus de "char" és aquest, quin tipus de pallasso és aquest? No els necessitem... Perquè pretén ser un caràcter normal, encara que estigui entre cometes. I es diferencia d'un caràcter normal, que no té cometes, en què només produeix el primer byte de la representació de cadena, mentre que un caràcter normal produeix el primer caràcter. En el nostre cas, el primer caràcter és la lletra P, que en la representació Unicode ocupa 2 bytes, com es demostra convertint el resultat al tipus bytea. I el tipus "char" només pren el primer byte d'aquesta representació Unicode. Aleshores, per què es necessita aquest tipus? La documentació de PostgreSQL diu que aquest és un tipus especial utilitzat per a necessitats especials. Per tant, és poc probable que ho necessitem. Però mira'l als ulls i no t'equivocaràs quan el trobis amb el seu comportament especial.
Espais addicionals. Fora de la vista fora de la ment
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Mireu l'exemple donat. Vaig convertir especialment tots els resultats al tipus bytea, de manera que es veiés clarament què hi havia. On són els espais al final després de llançar a varchar(6)? La documentació diu succintament: "Quan s'emet el valor del caràcter a un altre tipus de caràcter, es descarta l'espai en blanc final". Cal recordar aquesta antipatia. I tingueu en compte que si una constant de cadena entre cometes s'emet directament al tipus varchar(6), els espais al final es conserven. Aquests són els miracles.
Expedient número tres. json/jsonb
JSON és una estructura separada que viu la seva pròpia vida. Per tant, les seves entitats i les de PostgreSQL són lleugerament diferents. Aquí hi ha exemples.
Johnson i Johnson. sentir la diferència
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fEl cas és que JSON té la seva pròpia entitat nul·la, que no és l'anàleg de NULL a PostgreSQL. Al mateix temps, el propi objecte JSON pot tenir el valor NULL, de manera que l'expressió SELECT null::jsonb IS NULL (tingueu en compte l'absència de cometes simples) retornarà cert aquesta vegada.
Una lletra ho canvia tot
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]}El cas és que json i jsonb són estructures completament diferents. A json, l'objecte s'emmagatzema tal com és, i a jsonb ja s'emmagatzema en forma d'estructura analitzada i indexada. És per això que en el segon cas, el valor de l'objecte per la clau 1 es va substituir de [1, 2, 3] a [7, 8, 9], que va entrar a l'estructura al final amb la mateixa clau.
No beguis aigua de la teva cara
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL en la seva implementació JSONB canvia el format dels nombres reals, portant-los a la forma clàssica. Això no passa amb el tipus JSON. Una mica estrany, però té raó.
Fitxa número quatre. data/hora/marca de temps
També hi ha algunes curiositats amb els tipus de data/hora. Mirem-los. Permeteu-me fer una reserva immediatament perquè algunes de les característiques del comportament quedin clares si enteneu bé l'essència de treballar amb zones horàries. Però aquest és també un tema per a un article separat.
El meu no ho entenc
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.
Символ: 8Sembla que què és incomprensible aquí? Però la base de dades encara no entén què hem posat aquí en primer lloc: l'any o el dia? I ella decideix que és el gener del 99 de 2008, el que li fa bogeria. En termes generals, a l'hora de transmetre dates en format de text, cal comprovar amb molta cura com les va reconèixer correctament la base de dades (en particular, analitzar el paràmetre d'estil de dates amb l'ordre SHOW datestyle), ja que les ambigüitats en aquest tema poden ser molt cares.
D'on has tret això?
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
Символ: 8Per què la base de dades no pot entendre l'hora especificada explícitament? Perquè la zona horària no té una abreviatura, sinó un nom complet, que només té sentit en el context d'una data, ja que té en compte l'historial dels canvis de zona horària i no funciona sense data. I la mateixa redacció de la línia del temps planteja preguntes: què volia dir realment el programador? Per tant, aquí tot és lògic, si t'hi fixes.
Què li passa?
Imagineu la situació. Teniu un camp a la taula amb el tipus timestamptz. Voleu indexar-lo. Però enteneu que la construcció d'un índex en aquest camp no sempre està justificada per la seva alta selectivitat (gairebé tots els valors d'aquest tipus seran únics). Així que decidiu reduir la selectivitat de l'índex emetent el tipus a una data. I tens 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-состояние: 42P17Què passa? El fet és que per emetre un tipus timestamptz a un tipus de data, s'utilitza el valor del paràmetre del sistema TimeZone, que fa que la funció de conversió de tipus depengui d'un paràmetre personalitzat, és a dir. volàtil. Aquestes funcions no estan permeses a l'índex. En aquest cas, heu d'indicar explícitament en quina zona horària es realitza el tipus d'emissió.
Quan ara ni tan sols és ara
Estem acostumats a ara() tornar la data/hora actual, tenint en compte la zona horària. Però mireu les consultes següents:
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/hora es retornarà igual, sense importar quant de temps hagi passat des de la sol·licitud anterior! Què passa? El fet és que now() no és l'hora actual, sinó l'hora d'inici de la transacció actual. Per tant, no canvia dins de la transacció. Qualsevol consulta llançada fora de l'àmbit d'una transacció s'embolica implícitament en una transacció, per això no observem que el temps retornat per un simple SELECT now(); de fet, no l'actual... Si voleu obtenir una hora actual honesta, heu d'utilitzar la funció clock_timestamp().
Fitxa número cinc. bit
Una mica estrany
SELECT '111'::bit(4)
bit
bit(4)
------
1110De quin costat s'han d'afegir els bits en cas d'extensió de tipus? Sembla ser a l'esquerra. Però només la base té una opinió diferent sobre aquest tema. Aneu amb compte: si el nombre de dígits no coincideix en emetre un tipus, no obtindreu el que volíeu. Això s'aplica tant per afegir bits a la dreta com per retallar bits. També a la dreta...
Expedient número sis. Arrays
Fins i tot NULL no va disparar
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Com a gent normal que s'ha creat amb SQL, esperem que el resultat d'aquesta expressió sigui NULL. Però no hi era. Es retorna una matriu. Per què? Perquè en aquest cas, la base emet NULL a una matriu d'enters i implícitament crida a la funció array_cat. Però encara no està clar per què aquest "gat de matriu" no restableix la matriu. També cal recordar aquest comportament.
Resumir. Hi ha moltes coses estranyes. La majoria d'ells, per descomptat, no són tan crítics com per parlar de comportaments descaradament inadequats. I d'altres s'expliquen per la facilitat d'ús o la freqüència de la seva aplicabilitat en determinades situacions. Però al mateix temps, hi ha moltes sorpreses. Per tant, cal conèixer-los. Si trobeu alguna cosa més estranya o inusual en el comportament de qualsevol tipus, escriviu als comentaris, estaré encantat d'afegir-hi als dossiers disponibles.
Font: www.habr.com