Dans leur apparence extérieure, rien ne suscite de soupçons. De plus, ils semblent te être bien et depuis longtemps familiers. Mais cela, jusqu'à ce que tu les vérifies. C'est à ce moment-là qu'ils révèlent leur nature sournoise, agissant tout à fait différemment de ce que tu avais prévu. Parfois, ils commettent des actes si incroyables que les cheveux se dressent sur la tête, comme par exemple, perdre des informations confidentielles qui leur ont été confiées. Quand tu les confrontes, ils affirment ne pas se connaître, bien qu'en coulisses, ils travaillent assidûment sous le même toit. Il est grand temps de les mettre au grand jour. Découvrons ensemble ces personnages douteux.
La typologie des données dans PostgreSQL, malgré toute sa logique, réserve parfois de très étranges surprises. Dans cet article, nous allons tenter d'éclaircir certaines de leurs bizarreries, comprendre les raisons de leur comportement étrange et savoir comment éviter les problèmes dans la pratique quotidienne. Pour être franc, j'ai écrit cet article aussi comme un genre de manuel pour moi-même, un manuel vers lequel je pourrais facilement me référer en cas de doutes. Il sera donc enrichi au fur et à mesure que de nouvelles surprises de personnages douteux seront découvertes. Alors, en route, intrépides détectives des bases de données !
Dossier numéro un. real/double precision/numeric/money
On pourrait penser que les types numériques sont les moins problématiques en termes de surprises dans leur comportement. Mais pas du tout. C'est donc par eux que nous allons commencer. Alors…
On a oublié comment compter
SELECT 0.1::real = 0.1
?column?
boolean
---------
fQuel est le problème ? C'est que PostgreSQL convertit la constante non typée 0.1 en type double precision et essaie de la comparer avec 0.1 de type real. Et ce sont des valeurs totalement différentes ! Cela vient de la représentation des nombres à virgule flottante dans la mémoire de l'ordinateur. Puisque 0.1 ne peut pas être représenté sous forme de fraction binaire finie (ce sera 0.0(0011) en binaire), les nombres avec des précisions différentes seront différents, d'où le résultat indiquant qu'ils ne sont pas égaux. En fait, c'est un sujet pour un article séparé, je n'entrerai pas dans les détails ici.
D'où vient l'erreur ?
SELECT double precision(1)
ERREUR : erreur de syntaxe à ou près de "("
LIGNE 1 : SELECT double precision(1)
^
********** Erreur **********
ERREUR : erreur de syntaxe à ou près de "("
État SQL : 42601
Symbole : 24Beaucoup savent que PostgreSQL permet l'écriture fonctionnelle des conversions de types. C'est-à-dire qu'on peut écrire non seulement 1::int, mais aussi int(1), ce qui sera équivalent. Mais ce n'est pas le cas pour les types dont le nom se compose de plusieurs mots ! Par conséquent, si vous souhaitez convertir une valeur numérique en type double precision de manière fonctionnelle, utilisez l'alias de ce type float8, c'est-à-dire SELECT float8(1).
Qu'est-ce qui est plus grand que l'infini ?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tVoilà ! Il s'avère qu'il existe quelque chose de plus grand que l'infini, et c'est NaN ! Pendant ce temps, la documentation de PostgreSQL nous regarde honnêtement et prétend que NaN est bel et bien plus grand que n'importe quel autre nombre, donc que l'infini. Et il en va de même pour -NaN. Bonjour, amateurs d'analyse mathématique ! Mais il faut garder à l'esprit que tout cela fonctionne dans le contexte des nombres réels.
Rond des yeux
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Encore un bonjour inattendu de la base. Et encore une fois, il faut se rappeler que pour les types double precision et numeric, il existe des arrondis différents. Pour numeric, l'arrondi est habituel, lorsque 0,5 est arrondi vers le haut, tandis que pour double precision, l'arrondi de 0,5 se fait vers le nombre entier pair le plus proche.
L'argent est quelque chose de spécial
SELECT '10'::money::float8
ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
^
********** Erreur **********
ERROR: cannot cast type money to double precision
SQL-state: 42846
Symbole: 19Selon PostgreSQL, l'argent n'est pas un nombre réel. Certains individus le pensent également. Nous devons nous rappeler que la conversion du type money n'est possible qu'en type numeric, tout comme le type money ne peut être converti qu'en type numeric. Avec celui-ci, on peut jouer comme bon nous semble. Mais cela ne sera plus de l'argent.
Smallint et génération de séquences
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.
********** Erreur **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-state: 42725
Conseil: Could not choose a best candidate function. You might need to add explicit type casts.
Symbole: 18PostgreSQL n'aime pas les subtilités. Quelles séquences sont basées sur smallint ? int, pas moins ! Ainsi, lorsqu'il essaie d'exécuter la requête ci-dessus, la base de données tente de convertir smallint en un autre type entier et constate qu'il peut y avoir plusieurs conversions. Quel type de conversion choisir ? Elle ne peut pas décider et échoue avec une erreur.
Dossier numéro deux. «char»/char/varchar/text
Une série d'étrangetés se retrouvent également dans les types de caractères. Familiarisons-nous aussi avec eux.
De quoi s'agit-il ?
SELECT 'PETIA'::"char"
, 'PETIA'::"char"::bytea
, 'PETIA'::char
, 'PETIA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fQuel est ce type «char», quel clown est-ce ? Nous n'en avons pas besoin... Parce qu'il se fait passer pour un char normal, même s'il est entre guillemets. Il se distingue du char normal, qui est sans guillemets, en ce qu'il n'affiche que le premier octet de la représentation chaîne, tandis qu'un char normal affiche le premier caractère. Dans notre cas, le premier caractère est la lettre П, qui en représentation unicode occupe 2 octets, comme l'indique la conversion du résultat en type bytea. Le type «char» ne prend que le premier octet de cette représentation unicode. Alors à quoi sert ce type ? La documentation de PostgreSQL dit qu'il s'agit d'un type spécial utilisé pour des besoins particuliers. Donc, il est peu probable qu'il nous soit utile. Mais regardez-le dans les yeux et ne vous trompez pas quand vous le rencontrerez avec son comportement particulier.
Espaces superflus. Loin des yeux, loin du cœur.
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Examinez l'exemple donné. J'ai spécifiquement converti tous les résultats en type bytea, pour que ce soit bien visible ce qui s'y trouve. Où sont les espaces de fin après conversion en type varchar(6) ? La documentation affirme sobrement : «Lors de la conversion d'une valeur caractère en un autre type de caractère, les espaces d'alignement sont supprimés». Il faut retenir cette aversion. Et notez que si une constante chaîne entre guillemets est immédiatement convertie en type varchar(6), les espaces de fin sont préservés. Ce sont là des merveilles.
Dossier numéro trois. json/jsonb
JSON est une structure distincte qui vit sa propre vie. Ainsi, ses entités et celles de PostgreSQL diffèrent légèrement. Voici des exemples.
Johnson & Johnson. Ressentez la différence
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fLe fait est que JSON a sa propre entité null, qui n'est pas équivalente à NULL dans PostgreSQL. En même temps, l'objet JSON lui-même peut avoir une valeur NULL, donc l'expression SELECT null::jsonb IS NULL (notez l'absence de guillemets simples) renverra cette fois true.
Une lettre change tout
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]}Le fait est que json et jsonb sont des structures complètement différentes. Dans json, l'objet est stocké tel quel, tandis que dans jsonb, il est déjà stocké sous la forme d'une structure indexée et décomposée. C'est pourquoi, dans ce second cas, la valeur de l'objet pour la clé 1 a été remplacée de [1, 2, 3] par [7, 8, 9], qui est arrivée dans la structure à la fin avec la même clé.
De la surface de l'eau, il n'y a rien à boire
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL, dans son implémentation de JSONB, change le format des nombres réels, les ramenant à un format classique. Pour le type JSON, cela ne se produit pas. C'est un peu étrange, mais c'est son droit.
Dossier numéro quatre. date/heure/timestamp
Avec les types date/heure, il y a également certaines étrangetés. Examinons-les. Je préciserai que certaines des particularités de comportement deviennent compréhensibles si l'on comprend bien le fonctionnement des fuseaux horaires. Mais c'est également un sujet pour un autre article.
Je ne comprends pas votre point de vue
SELECT '08-Jan-99'::date
ERROR: date/heure valeur du champ hors de portée : "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Peut-être avez-vous besoin d'un autre réglage "datestyle".
********** Erreur **********
ERROR: date/heure valeur du champ hors de portée : "08-Jan-99"
État SQL: 22008
Conseil: Peut-être avez-vous besoin d'un autre réglage "datestyle".
Symbole: 8On pourrait penser que ce n'est pas compliqué. Mais la base ne comprend pas ce que nous avons mis au premier plan — l'année ou le jour ? Et elle décide que c'est le 99 janvier 2008, ce qui lui fait exploser le cerveau. En général, lors de la transmission de dates au format texte, il faut vérifier très attentivement à quel point la base les a reconnues (en particulier, analyser le paramètre datestyle avec la commande SHOW datestyle), car les ambiguïtés à ce sujet peuvent être très coûteuses.
D'où viens-tu ?
SELECT '04:05 Europe/Moscow'::time
ERREUR : syntaxe d'entrée invalide pour le type time : "04:05 Europe/Moscow"
LIGNE 1 : SELECT '04:05 Europe/Moscow'::time
^
********** Erreur **********
ERREUR : syntaxe d'entrée invalide pour le type time : "04:05 Europe/Moscow"
État SQL : 22007
Symbole : 8Pourquoi la base ne peut-elle pas comprendre l'heure explicitement indiquée ? Parce que pour le fuseau horaire, on a spécifié un nom complet plutôt qu'une abréviation, ce qui a du sens uniquement dans le contexte d'une date, car cela prend en compte l'historique des changements de fuseaux horaires, et cela ne fonctionne pas sans date. De plus, la formulation de la chaîne temporelle soulève des questions — que voulait réellement dire le programmeur ? Tout cela est logique, une fois qu'on s'en est rendu compte.
Qu'est-ce qui ne va pas ?
Imaginez la situation. Vous avez un champ avec le type timestamptz dans votre table. Vous souhaitez l'indexer. Mais vous comprenez que construire un index sur ce champ n'est pas toujours justifié en raison de sa haute sélectivité (presque toutes les valeurs de ce type seront uniques). Vous décidez donc de réduire la sélectivité de l'index en convertissant ce type en date. Et vous obtenez une surprise :
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
ERREUR : les fonctions dans l'expression d'index doivent être marquées IMMUTABLE
********** Erreur **********
ERREUR : les fonctions dans l'expression d'index doivent être marquées IMMUTABLE
État SQL : 42P17Quel est le problème ? Le fait que pour convertir le type timestamptz en type date, la valeur du paramètre système TimeZone est utilisée, ce qui rend la fonction de conversion dépendante d'un paramètre configurable, c'est-à-dire variable. De telles fonctions ne sont pas autorisées dans un index. Dans ce cas, il faut spécifier explicitement dans quel fuseau horaire la conversion de type est effectuée.
Quand now n'est pas vraiment now
Nous avons l'habitude que now() retourne la date/l'heure actuelle en tenant compte du fuseau horaire. Mais regardez les requêtes suivantes :
DÉBUT DE 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
VALIDER;La date/heure revient identique, peu importe le temps écoulé depuis la dernière requête ! Pourquoi ? Parce que now() n'est pas l'heure actuelle, mais le temps du début de la transaction en cours. Donc, dans le cadre de la transaction, il ne change pas. Toute requête exécutée en dehors du cadre de la transaction est implicitement encapsulée dans une transaction, donc nous ne remarquons pas que le temps renvoyé par une simple requête SELECT now(); n'est en fait pas l'heure actuelle… Si vous voulez obtenir l'heure actuelle honnêtement, vous devez utiliser la fonction clock_timestamp().
Dossier numéro cinq. bit
Un peu étrange
SELECT '111'::bit(4)
bit
bit(4)
------
1110De quel côté faut-il ajouter les bits lors de l'extension du type ? Il semble que ce soit à gauche. Mais la base a un avis différent à ce sujet. Faites attention : en cas d'inadéquation du nombre de bits lors de la conversion de type, vous obtiendrez quelque chose de complètement différent de ce que vous vouliez. Cela s'applique aussi bien à l'ajout de bits à droite qu'à la réduction des bits. Également à droite…
Dossier numéro six. Tableaux
Même NULL n'a pas tiré.
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Comme des gens normaux, élevés sur SQL, nous nous attendons à ce que le résultat de cette expression soit NULL. Mais pas du tout. Un tableau est renvoyé. Pourquoi ? Parce qu'ici, la base convertit NULL en tableau entier et appelle implicitement la fonction array_cat. Mais il reste flou pourquoi ce « petit chat de tableau » ne rend pas le tableau nul. Ce comportement doit aussi simplement être mémorisé.
En résumé. Il y a beaucoup de curiosités. La plupart d'entre elles, bien sûr, ne sont pas assez critiques pour parler d'un comportement désespérément inadéquat. D'autres s'expliquent par la commodité d'utilisation ou la fréquence de leur applicabilité dans certaines situations. Mais en même temps, il y a beaucoup de surprises. Donc, il faut en être conscient. Si vous trouvez quelque chose d'étrange ou d'inhabituel dans le comportement de certains types, écrivez dans les commentaires, je serais ravi d'ajouter ce qui existe déjà.
Source : habr.com
