Il n’y a rien de suspect dans leur apparence. D’ailleurs, ils vous semblent même bien et depuis longtemps familiers. Mais ce n'est que jusqu'à ce que vous les vérifiiez. C’est là qu’ils montrent leur nature insidieuse, travaillant complètement différemment de ce à quoi vous vous attendiez. Et parfois, ils font quelque chose qui vous fait dresser les cheveux sur la tête - par exemple, ils perdent des données secrètes qui leur ont été confiées. Lorsqu’on les confronte, ils prétendent qu’ils ne se connaissent pas, même si dans l’ombre ils travaillent dur sous la même capuche. Il est temps de les amener enfin à l'eau potable. Traitons également de ces types suspects.
La saisie de données dans PostgreSQL, malgré toute sa logique, présente parfois de très étranges surprises. Dans cet article, nous essaierons de clarifier certaines de leurs bizarreries, de comprendre la raison de leur comportement étrange et de comprendre comment ne pas rencontrer de problèmes dans la pratique quotidienne. À vrai dire, j’ai également rédigé cet article comme une sorte d’ouvrage de référence pour moi-même, un ouvrage de référence auquel on pourrait facilement se référer dans des cas controversés. Par conséquent, il sera reconstitué à mesure que de nouvelles surprises provenant de types suspects seront découvertes. Alors c'est parti, ô infatigables traqueurs de bases de données !
Dossier numéro un. réel/double précision/numérique/argent
Il semblerait que les types numériques soient les moins problématiques en termes de surprises de comportement. Mais peu importe comment c'est. Alors commençons par eux. Donc…
J'ai oublié comment compter
SELECT 0.1::real = 0.1
?column?
boolean
---------
fQuel est le problème? Le problème est que PostgreSQL convertit la constante non typée 0.1 en double précision et essaie de la comparer avec 0.1 de type réel. Et ce sont des significations complètement différentes ! L'idée est de représenter des nombres réels dans la mémoire machine. Puisque 0.1 ne peut pas être représenté comme une fraction binaire finie (ce serait 0.0 (0011) en binaire), les nombres avec des profondeurs de bits différentes seront différents, d'où le résultat qu'ils ne sont pas égaux. D'une manière générale, il s'agit d'un sujet pour un article séparé, je n'écrirai pas plus en détail ici.
D'où vient l'erreur ?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Beaucoup de gens savent que PostgreSQL autorise la notation fonctionnelle pour la conversion de type. Autrement dit, vous pouvez écrire non seulement 1::int, mais aussi int(1), ce qui sera équivalent. Mais pas pour les types dont les noms sont composés de plusieurs mots ! Par conséquent, si vous souhaitez convertir une valeur numérique en type double précision sous forme fonctionnelle, utilisez l'alias de ce type float8, c'est-à-dire SELECT float8(1).
Qu'y a-t-il de plus grand que l'infini ?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tRegardez à quoi ça ressemble ! Il s'avère qu'il existe quelque chose de plus grand que l'infini, et c'est NaN ! Dans le même temps, la documentation PostgreSQL nous regarde avec des yeux honnêtes et affirme que NaN est évidemment supérieur à tout autre nombre, et donc à l'infini. L’inverse est également vrai pour -NaN. Bonjour, amoureux des mathématiques ! Mais nous devons garder à l’esprit que tout cela s’effectue dans le contexte de chiffres réels.
Arrondi des yeux
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Une autre salutation inattendue de la base. Encore une fois, n’oubliez pas que la double précision et les types numériques ont des effets d’arrondi différents. Pour les numériques - l'habituel, lorsque 0,5 est arrondi, et pour la double précision - 0,5 est arrondi vers l'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
^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19Selon PostgreSQL, l’argent n’est pas un nombre réel. Selon certains individus également. Nous devons nous rappeler que la conversion du type money n'est possible qu'en type numérique, tout comme seul le type numérique peut être converti en type money. Mais maintenant, vous pouvez jouer avec comme bon vous semble. Mais ce ne sera pas le même 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.
********** Ошибка **********
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.
Символ: 18PostgreSQL n'aime pas perdre du temps avec des bagatelles. Quelles sont ces séquences basées sur smallint ? int, rien de moins ! Par conséquent, lorsqu'elle tente d'exécuter la requête ci-dessus, la base de données essaie de convertir smallint en un autre type entier et voit qu'il peut y avoir plusieurs conversions de ce type. Quel casting choisir ? Elle ne peut pas en décider et se bloque donc avec une erreur.
Dossier numéro deux. "char"/char/varchar/texte
Un certain nombre de bizarreries sont également présentes dans les types de caractères. Apprenons à les connaître aussi.
De quel genre d'astuces s'agit-il ?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fDe quel type de « personnage » s'agit-il, de quel genre de clown s'agit-il ? Nous n’en avons pas besoin... Parce qu’il prétend être un caractère ordinaire, même s’il est entre guillemets. Et il diffère d'un char normal, qui est sans guillemets, en ce sens qu'il génère uniquement le premier octet de la représentation sous forme de chaîne, tandis qu'un char normal génère le premier caractère. Dans notre cas, le premier caractère est la lettre P, qui dans la représentation Unicode occupe 2 octets, comme en témoigne la conversion du résultat en type bytea. Et le type « char » ne prend que le premier octet de cette représentation Unicode. Alors pourquoi ce type est-il nécessaire ? La documentation PostgreSQL indique qu'il s'agit d'un type spécial utilisé pour des besoins particuliers. Il est donc peu probable que nous en ayons besoin. Mais regardez-le dans les yeux et vous ne vous tromperez pas lorsque vous le rencontrerez avec son comportement particulier.
Espaces supplémentaires. Hors de vue, hors de l'esprit
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Jetez un œil à l’exemple donné. J'ai spécialement converti tous les résultats au type bytea, afin que ce qui s'y trouvait soit clairement visible. Où sont les espaces de fin après le casting vers varchar(6) ? La documentation indique succinctement : "Lors de la conversion de la valeur de caractère en un autre type de caractère, les espaces de fin sont supprimés." Il faut se rappeler de cette aversion. Et notez que si une constante chaîne entre guillemets est convertie directement en type varchar(6), les espaces de fin sont conservés. Tels sont les miracles.
Dossier numéro trois. json/jsonb
JSON est une structure distincte qui vit sa propre vie. Ses entités et celles de PostgreSQL sont donc légèrement différentes. Voici des exemples.
Johnson et Johnson. sentir la différence
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fLe fait est que JSON a sa propre entité nulle, qui n'est pas l'analogue de NULL dans PostgreSQL. Dans le même temps, l'objet JSON lui-même peut très bien avoir la valeur NULL, donc l'expression SELECT null::jsonb IS NULL (notez l'absence de guillemets simples) renverra true cette fois.
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, et dans jsonb, il est déjà stocké sous la forme d'une structure analysée et indexée. C'est pourquoi dans le second cas, la valeur de l'objet par la clé 1 a été remplacée de [1, 2, 3] par [7, 8, 9], qui est entré dans la structure à la toute fin avec la même clé.
Ne buvez pas d'eau sur votre visage
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 JSONB modifie le formatage des nombres réels, les ramenant à la forme classique. Cela ne se produit pas pour le type JSON. C'est un peu étrange, mais il a raison.
Dossier numéro quatre. date/heure/horodatage
Il existe également quelques bizarreries avec les types date/heure. Regardons-les. Permettez-moi tout de suite de faire une réserve : certaines caractéristiques comportementales deviennent claires si vous comprenez bien l'essence du travail avec les fuseaux horaires. Mais c'est aussi le sujet d'un article séparé.
Je ne comprends pas le vôtre
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.
Символ: 8Il semblerait qu'est-ce qui est incompréhensible ici ? Mais la base de données ne comprend toujours pas ce que nous mettons en premier ici : l’année ou le jour ? Et elle décide que nous sommes le 99 janvier 2008, ce qui la laisse perplexe. D'une manière générale, lors de la transmission de dates au format texte, vous devez vérifier très attentivement dans quelle mesure la base de données les a correctement reconnues (en particulier analyser le paramètre datestyle avec la commande SHOW datestyle), car les ambiguïtés en la matière peuvent coûter très cher.
D'où viens-tu?
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
Символ: 8Pourquoi la base de données ne peut-elle pas comprendre l'heure explicitement spécifiée ? Car le fuseau horaire n'a pas d'abréviation, mais un nom complet, qui n'a de sens que dans le contexte d'une date, puisqu'il prend en compte l'historique des changements de fuseau horaire, et sans date, il ne fonctionne pas. Et le libellé même de la chronologie soulève des questions : que voulait vraiment dire le programmeur ? Par conséquent, tout est logique ici, si vous y regardez bien.
Quel est le problème avec lui?
Imaginez la situation. Vous avez un champ dans votre table de type timestamptz. Vous souhaitez l'indexer. Mais vous comprenez que construire un index sur ce champ n'est pas toujours justifié en raison de sa grande 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 le type en date. Et vous obtenez une surprise :
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-состояние: 42P17Quel est le problème? Le fait est que pour convertir un type timestamptz en type date, la valeur du paramètre système TimeZone est utilisée, ce qui rend la fonction de conversion de type dépendante d'un paramètre personnalisé, c'est-à-dire volatil. De telles fonctions ne sont pas autorisées dans l'index. Dans ce cas, vous devez indiquer explicitement dans quel fuseau horaire le transtypage est effectué.
Quand maintenant ce n'est même pas du tout maintenant
Nous avons l'habitude de renvoyer now() la date/heure actuelle, en tenant compte du fuseau horaire. Mais regardez les requêtes suivantes :
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 date/heure est renvoyée de la même manière, quel que soit le temps écoulé depuis la demande précédente ! Quel est le problème? Le fait est que now() n'est pas l'heure actuelle, mais l'heure de début de la transaction en cours. Cela ne change donc pas au cours de la transaction. Toute requête lancée en dehors du cadre d'une transaction est implicitement enveloppée dans une transaction, c'est pourquoi nous ne remarquons pas que l'heure renvoyée par un simple SELECT now(); en fait, pas l'heure actuelle... Si vous souhaitez obtenir une heure actuelle honnête, vous devez utiliser la fonction clock_timestamp().
Dossier numéro cinq. peu
Un peu étrange
SELECT '111'::bit(4)
bit
bit(4)
------
1110De quel côté faut-il ajouter les bits en cas d'extension de type ? Il semble que ce soit à gauche. Mais seule la base a un avis différent sur cette question. Attention : si le nombre de chiffres ne correspond pas lors de la conversion d'un type, vous n'obtiendrez pas ce que vous vouliez. Cela s'applique à la fois à l'ajout de bits à droite et à la suppression de bits. A droite aussi....
Dossier numéro six. Tableaux
Même NULL n'a pas tiré
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}En tant que personnes normales élevées avec SQL, nous nous attendons à ce que le résultat de cette expression soit NULL. Mais ce n'était pas là. Un tableau est renvoyé. Pourquoi? Parce que dans ce cas, la base convertit NULL en un tableau entier et appelle implicitement la fonction array_cat. Mais on ne sait toujours pas pourquoi ce « chat de tableau » ne réinitialise pas le tableau. Ce comportement doit également être mémorisé.
Résumer. Il y a plein de choses étranges. Bien entendu, la plupart d’entre eux ne sont pas critiques au point de parler de comportements manifestement inappropriés. Et d'autres s'expliquent par la facilité d'utilisation ou la fréquence de leur applicabilité dans certaines situations. Mais en même temps, les surprises sont nombreuses. Par conséquent, vous devez les connaître. Si vous trouvez autre chose d'étrange ou d'inhabituel dans le comportement de tout type, écrivez dans les commentaires, je me ferai un plaisir de l'ajouter aux dossiers disponibles sur eux.
Source: habr.com