Verdächtige Typen

In ihrem äußeren Erscheinungsbild ruft nichts Verdacht hervor. Mehr noch, sie erscheinen dir sogar gut und lange bekannt. Aber das ist nur, bis du sie überprüfst. Genau hier zeigen sie ihr niederträchtiges Wesen und verhalten sich ganz anders als du erwartet hast. Manchmal liefern sie sogar Dinge, bei denen dir die Haare zu Berge stehen – beispielsweise wenn sie vertrauliche, ihnen anvertraute Daten verlieren. Wenn du sie direkt konfrontierst, behaupten sie, sich nicht zu kennen, während sie im Schatten fleißig unter demselben Dach arbeiten. Es wird Zeit, sie endlich zu entlarven. Lass uns auch mit diesen verdächtigen Typen aufräumen.

Die Datentypisierung in PostgreSQL hält trotz aller Logik manchmal sehr seltsame Überraschungen bereit. In diesem Artikel werden wir versuchen, einige ihrer Eigenheiten zu klären, die Gründe für ihr seltsames Verhalten zu verstehen und herauszufinden, wie man im Alltag nicht auf Probleme stößt. Um ehrlich zu sein, habe ich diesen Artikel auch als eine Art Nachschlagewerk für mich selbst erstellt, ein Nachschlagewerk, auf das man bei strittigen Fällen leicht zurückgreifen kann. Daher wird es im Laufe der Zeit mit neuen Überraschungen von diesen verdächtigen Typen ergänzt. Also, auf geht's, unermüdliche Datenbank-Pioniere!

Aktennotiz Nummer eins. real/double precision/numeric/money

Auf den ersten Blick scheinen die numerischen Typen die unproblematischsten zu sein, wenn es um Überraschungen im Verhalten geht. Aber weit gefehlt. Deshalb fangen wir damit an. Also…

Es wurde das Zählen verlernt

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

Was ist das Problem? Das Problem ist, dass PostgreSQL die typisierte Konstante 0.1 zum Typ double precision umwandelt und versucht, sie mit 0.1 vom Typ real zu vergleichen. Und das sind absolut unterschiedliche Werte! Der Grund liegt in der Darstellung von Gleitkommazahlen im Arbeitsspeicher. Da 0.1 nicht als endlicher binärer Bruch dargestellt werden kann (das wäre 0.0(0011) in der binären Darstellung), unterscheiden sich die Zahlen mit unterschiedlicher Genauigkeit, was zu dem Ergebnis führt, dass sie ungleich sind. Tatsächlich ist das ein Thema für einen separaten Artikel, darüber werde ich hier nicht weiter schreiben.

Woher kommt der Fehler?

SELECT double precision(1)

ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Fehler **********
ERROR: syntax error at or near "("
SQL-State: 42601
Symbol: 24

Viele wissen, dass PostgreSQL eine funktionale Typumwandlung zulässt. Das bedeutet, man kann nicht nur 1::int schreiben, sondern auch int(1), was gleichwertig ist. Aber das gilt nicht für Typen, deren Bezeichnung aus mehreren Wörtern besteht! Daher, wenn Sie einen numerischen Wert in einen Datentyp doppelte Genauigkeit umwandeln möchten, verwenden Sie den Alias dieses Typs float8, also SELECT float8(1).

Was gibt es Größeres als Unendlichkeit?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

So ist das! Es stellt sich heraus, dass es etwas gibt, das größer ist als Unendlichkeit, und das ist NaN! Dabei schaut die PostgreSQL-Dokumentation mit ehrlichen Augen auf uns und behauptet, dass NaN eindeutig größer ist als jede andere Zahl und somit auch als Unendlichkeit. Umgekehrt gilt das auch für -NaN. Hallo, Liebhaber der Analysis! Aber man muss sich daran erinnern, dass all dies im Kontext der reellen Zahlen gilt.

Augenrundung

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

Ein weiterer unerwarteter Gruß von der Datenbank. Und man muss sich erneut merken, dass für die Typen double precision und numeric unterschiedliche Rundungen gelten. Für numeric gilt die Standardregel, dass 0,5 aufgerundet wird, während bei double precision die Rundung von 0,5 zur nächstgelegenen geraden Zahl erfolgt.

Geld ist etwas Besonderes

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Fehler **********
ERROR: cannot cast type money to double precision
SQL-Zustand: 42846
Symbol: 19

Laut PostgreSQL ist Geld keine reelle Zahl. Manche Individuen denken das auch. Wir müssen uns jedoch daran erinnern, dass eine Umwandlung des Typs money nur in den Typ numeric möglich ist, ebenso kann der Typ money nur in den Typ numeric umgewandelt werden. Mit letzterem kann man dann nach Belieben spielen. Aber das werden dann nicht mehr die gleichen Geldbeträge sein.

Smallint und die Generierung von Sequenzen

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.
********** Fehler **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-Zustand: 42725
Hinweis: Could not choose a best candidate function. You might need to add explicit type casts.
Symbol: 18

PostgreSQL ist nicht für Kleinigkeiten zu haben. Welche Sequenzen basierend auf smallint? int, nicht weniger! Daher versucht die Datenbank bei der Ausführung der obigen Abfrage, smallint in einen anderen Ganzzahltyp zu konvertieren, und sieht, dass es mehrere Konvertierungen geben könnte. Welche Konvertierung soll gewählt werden? Das kann sie nicht entscheiden, und deshalb schlägt es mit einem Fehler fehl.

Aktenzeichen zwei. „char“/char/varchar/text

Es gibt auch bei den Zeichentypen einige Merkwürdigkeiten. Lassen Sie uns auch damit vertraut machen.

Was sind das für Tricks?

SELECT 'PETJA'::"char"
     , 'PETJA'::"char"::bytea
     , 'PETJA'::char
     , 'PETJA'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

Was ist dieser Typ „char“, was ist dieser Clown? Solche brauchen wir nicht… Denn er gibt sich als gewöhnlicher char aus, nur weil er in Anführungszeichen steht. Er unterscheidet sich von einem normalen char, der ohne Anführungszeichen ist, dadurch, dass er nur das erste Byte der Zeichenfolge ausgibt, während ein normaler char das erste Zeichen ausgibt. In unserem Fall ist das erste Zeichen der Buchstabe П, der in der Unicode-Darstellung 2 Bytes belegt, was die Umwandlung des Ergebnisses in den Typ bytea zeigt. Der Typ „char“ nimmt nur das erste Byte dieser Unicode-Darstellung. Wozu brauchen wir diesen Typ? Die PostgreSQL-Dokumentation sagt, dass es sich um einen speziellen Typ handelt, der für besondere Zwecke verwendet wird. Daher wird er uns wahrscheinlich nicht benötigen. Aber schauen Sie ihm ins Gesicht und machen Sie keinen Fehler, wenn Sie ihm mit seinem besonderen Verhalten begegnen.

Überflüssige Leerzeichen. Aus den Augen, aus dem Sinn.

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

Schauen Sie sich das gegebene Beispiel an. Ich habe absichtlich alle Ergebnisse in den Typ bytea umgewandelt, damit deutlich sichtbar ist, was dort liegen bleibt. Wo sind die Endleerzeichen nach der Umwandlung in den Typ varchar(6)? Die Dokumentation besagt kurz und bündig: „Bei der Umwandlung eines Wertes vom Typ character in einen anderen Zeichentyp werden die auffüllenden Leerzeichen verworfen.“ Diese Abneigung sollten Sie sich merken. Und beachten Sie, dass, wenn eine String-Konstante in Anführungszeichen sofort in den Typ varchar(6) umgewandelt wird, die Endleerzeichen erhalten bleiben. So geschehen Wunder.

Aktenzeichen drei. json/jsonb

JSON ist eine eigene Struktur, die ihr eigenes Leben führt. Daher sind ihre Entitäten und die von PostgreSQL etwas unterschiedlich. Hier sind Beispiele.

Johnson & Johnson. Fühlen Sie den Unterschied

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

Das Problem ist, dass JSON eine eigene Entität null hat, die nicht das Äquivalent von NULL in PostgreSQL ist. Gleichzeitig kann das JSON-Objekt jedoch durchaus einen Wert NULL haben, weshalb der Ausdruck SELECT null::jsonb IS NULL (bitte beachten Sie die fehlenden einfachen Anführungszeichen) diesmal true zurückgibt.

Ein Buchstabe ändert alles

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]}

Das Problem ist, dass json und jsonb völlig unterschiedliche Strukturen sind. Im json wird das Objekt so gespeichert, wie es ist, während es im jsonb in einer analysierten, indizierten Struktur gespeichert wird. Daher wurde im zweiten Fall der Wert des Objekts unter dem Schlüssel 1 von [1, 2, 3] auf [7, 8, 9] ersetzt, welcher zuletzt mit demselben Schlüssel in die Struktur kam.

Man kann nicht aus Wasser trinken

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL ändert bei der JSONB-Implementierung die Formatierung von Gleitkommazahlen und bringt sie in das klassische Format. Bei Typ JSON passiert dies nicht. Es ist etwas seltsam, aber es ist sein Recht.

Akte Nummer vier. Datum/Zeit/Zeitstempel

Mit Datums-/Zeitformaten gibt es ebenfalls einige Merkwürdigkeiten. Schauen wir uns diese an. Ich möchte gleich vorweg sagen, dass einige der Verhaltensmerkmale verständlich werden, wenn man die Funktionsweise von Zeitzonen gut versteht. Aber das ist auch ein Thema für einen eigenen Artikel.

Ich verstehe dich nicht

SELECT '08-Jan-99'::date

ERROR:  Datum/Zeit-Wert liegt außerhalb des zulässigen Bereichs: "08-Jan-99"
ZEILE 1: SELECT '08-Jan-99'::date
               ^
HINWEIS:  Vielleicht benötigen Sie eine andere "datestyle"-Einstellung.
********** Fehler **********
ERROR: Datum/Zeit-Wert liegt außerhalb des zulässigen Bereichs: "08-Jan-99"
SQL-Zustand: 22008
Hinweis: Vielleicht benötigen Sie eine andere "datestyle"-Einstellung.
Symbol: 8

Was könnte daran unverständlich sein? Dennoch erkennt die Datenbank nicht, ob wir hier zuerst das Jahr oder den Tag setzen. Und sie entscheidet, dass es der 99. Januar 2008 ist, was sie überfordert. Allgemein gesagt, sollte man beim Übertragen von Daten im Textformat sehr genau überprüfen, wie korrekt die Datenbank diese erkannt hat (insbesondere den Parameter datestyle mittels SHOW datestyle analysieren), da Mehrdeutigkeiten in diesem Bereich sehr teuer sein können.

Woher kommst du?

SELECT '04:05 Europa/Moskau'::time

FEHLER:  ungültige Eingabesyntax für den Typ time: "04:05 Europa/Moskau"
ZEILE 1: SELECT '04:05 Europa/Moskau'::time
               ^
********** Fehler **********
FEHLER: ungültige Eingabesyntax für den Typ time: "04:05 Europa/Moskau"
SQL-Zustand: 22007
Symbol: 8

Warum kann die Datenbank die eindeutig angegebene Zeit nicht verstehen? Weil anstelle einer Abkürzung für die Zeitzone der vollständige Name angegeben ist, der nur im Kontext eines Datums sinnvoll ist, da er die Geschichte der Änderungen der Zeitzonen berücksichtigt, und dies funktioniert ohne Datum nicht. Und auch die Formulierung der Zeitangabe wirft Fragen auf – was meinte der Programmierer wirklich? Daher ist alles hier logisch, wenn man es sich genau ansieht.

Was ist sein Problem?

Stellen Sie sich folgende Situation vor. In Ihrer Tabelle gibt es ein Feld des Typs timestamptz. Sie möchten dieses indexieren. Aber Sie verstehen, dass es nicht immer sinnvoll ist, nach diesem Feld einen Index zu erstellen, aufgrund seiner hohen Selektivität (fast alle Werte dieses Typs werden einzigartig sein). Deshalb entscheiden Sie sich, die Selektivität des Index zu verringern, indem Sie diesen Typ in ein Datum umwandeln. Und Sie erhalten eine Überraschung:

CREATE INDEX "iIdent-DateLastUpdate"
  ON public."Ident" USING btree
  (("DTLastUpdate"::date));

FEHLER:  Funktionen im Indexausdruck müssen als IMMUTABLE gekennzeichnet sein
********** Fehler **********
FEHLER: Funktionen im Indexausdruck müssen als IMMUTABLE gekennzeichnet sein
SQL-Zustand: 42P17

Was ist das Problem? Es liegt daran, dass zur Umwandlung des Typs timestamptz in den Typ date der Wert des Systemparameters TimeZone verwendet wird, was die Umwandlungsfunktion von einem konfigurierbaren Parameter abhängig macht, d.h. veränderlich (volatile). Solche Funktionen sind im Index nicht zulässig. In diesem Fall muss man ausdrücklich angeben, in welcher Zeitzone die Typumwandlung erfolgt.

Wenn jetzt überhaupt nicht jetzt ist

Wir sind es gewohnt, dass now() das aktuelle Datum/Zeit unter Berücksichtigung der Zeitzone zurückgibt. Aber schauen Sie sich die folgenden Abfragen an:

START TRANSAKTION;
SELECT now();

            now
  timestamp mit Zeitzone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp mit Zeitzone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp mit Zeitzone
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

Das Datum/Zeit wird immer gleich zurückgegeben, unabhängig davon, wie viel Zeit seit der letzten Anfrage vergangen ist! Was ist los? Es liegt daran, dass now() nicht die aktuelle Zeit ist, sondern die Startzeit der aktuellen Transaktion. Daher ändert es sich im Rahmen der Transaktion nicht. Jede Anfrage, die außerhalb des Rahmens der Transaktion ausgeführt wird, wird implizit in eine Transaktion gewickelt, weshalb wir nicht bemerken, dass die von einer einfachen Anfrage SELECT now(); ausgegebene Zeit tatsächlich nicht die aktuelle ist. Wenn Sie die echte aktuelle Zeit erhalten möchten, sollten Sie die Funktion clock_timestamp() verwenden.

Aktenzeichen Nummer fünf. bit

Ein wenig seltsam

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

Von welcher Seite sollten Bits beim Type Extension hinzugefügt werden? Es scheint, als sei es links. Aber die Datenbank hat in dieser Hinsicht eine andere Meinung. Seien Sie vorsichtig: Wenn die Anzahl der Bits bei der Typkonvertierung nicht übereinstimmt, erhalten Sie genau das Gegenteil dessen, was Sie wollten. Dies gilt sowohl für das Hinzufügen von Bits rechts als auch für das Abschneiden von Bits. Auch rechts…

Aktenzeichen Nummer sechs. Arrays

Nicht einmal NULL hat geschossen

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

Wie normale Menschen, die mit SQL erzogen wurden, erwarten wir, dass das Ergebnis dieses Ausdrucks NULL ist. Aber dem ist nicht so. Ein Array wird zurückgegeben. Warum? Weil die Datenbank in diesem Fall NULL in ein Ganzzahl-Array konvertiert und implizit die Funktion array_cat aufruft. Aber es bleibt unklar, warum dieser „Array-Kater“ das Array nicht zurücksetzt. Dieses Verhalten sollte man sich ebenfalls einfach merken.

Fassen wir zusammen. Es gibt zahlreiche Merkwürdigkeiten. Die meisten sind natürlich nicht so kritisch, dass man von einem eklatant unangemessenen Verhalten sprechen könnte. Andere lassen sich durch Benutzerfreundlichkeit oder die Häufigkeit ihrer Anwendbarkeit in bestimmten Situationen erklären. Aber gleichzeitig gibt es viele Überraschungen. Daher ist es wichtig, darüber Bescheid zu wissen. Wenn Sie noch etwas Seltsames oder Ungewöhnliches im Verhalten bestimmter Typen finden, schreiben Sie in die Kommentare, ich werde die vorhandenen Akten gerne ergänzen.

Quelle: habr.com