Auf den ersten Blick wirken sie völlig unschuldig und vertraut. Doch das ändert sich, sobald du sie unter die Lupe nimmst. Hier zeigt sich ihr wahres, tückisches Gesicht, das nicht dem entspricht, was du erwartet hast. Manchmal entblößen sie sogar das Unvorstellbare — etwa den Verlust sensibler, ihnen anvertrauter Daten. Wenn du sie direkt konfrontierst, behaupten sie, einander nicht zu kennen, obwohl sie heimlich und emsig unter einem Dach arbeiten. Es ist an der Zeit, endlich Licht ins Dunkel zu bringen. Lassen Sie uns gemeinsam diese verdächtigen Typen näher unter die Lupe nehmen.
Die Datentypisierung in PostgreSQL mag zwar logisch erscheinen, aber sie hält oft sehr merkwürdige Überraschungen bereit. In diesem Artikel werden wir versuchen, einige ihrer Eigenheiten zu klären, die Ursachen ihres seltsamen Verhaltens zu verstehen und herauszufinden, wie man im Alltag nicht auf Probleme stößt. Ehrlich gesagt habe ich diesen Artikel auch als eine Art Nachschlagewerk für mich selbst erstellt, auf das ich in strittigen Fällen leicht zurückgreifen kann. Daher wird er mit der Zeit ergänzt, wenn ich neue Überraschungen von suspekten Typen entdecke. Also, auf geht's, unermüdliche Datenbankforscher!
Dossier Nummer eins. real/double precision/numeric/money
Zunächst scheinen numerische Typen am wenigsten problematisch in Bezug auf überraschendes Verhalten zu sein. Aber das ist nicht der Fall. Deshalb beginnen wir hier.
Wir haben das Zählen verlernt
SELECT 0.1::real = 0.1
?column?
boolean
---------
fDas Problem ist, dass PostgreSQL die nicht typisierte Konstante 0.1 in den Datentyp double precision umwandelt und versucht, sie mit 0.1 vom Typ real zu vergleichen. Das sind jedoch ganz unterschiedliche Werte! Das liegt an der Darstellung von Gleitkommazahlen im Arbeitsspeicher. Da 0.1 nicht als endlicher binärer Bruch dargestellt werden kann (es würde als 0.0(0011) im binären Format erscheinen), unterscheiden sich die Zahlen mit unterschiedlichen Genauigkeiten, und das Ergebnis ist, dass sie nicht gleich sind. Das ist übrigens ein Thema für einen eigenen Artikel; hier möchte ich nicht näher darauf eingehen.
Woher kommt der Fehler?
SELECT double precision(1)
FEHLER: Syntaxfehler in der Nähe von "("
ZEILE 1: SELECT double precision(1)
^
********** Fehler **********
FEHLER: Syntaxfehler in der Nähe von "("
SQL-Zustand: 42601
Symbol: 24Viele wissen, dass PostgreSQL eine funktionale Schreibweise für Typumwandlungen zulässt. Man kann also nicht nur 1::int schreiben, sondern auch int(1), was dasselbe bedeutet. Aber das gilt nicht für Typen, deren Namen aus mehreren Wörtern bestehen! Wenn Sie also einen numerischen Wert funktional in den Typ double precision umwandeln möchten, verwenden Sie das Alias dieses Typs, float8, also SELECT float8(1).
Was ist größer als Unendlichkeit?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tSo ist das! Es gibt tatsächlich etwas, das größer ist als Unendlichkeit, und das ist NaN! Dabei schaut die PostgreSQL-Dokumentation uns mit ehrlichen Augen an und behauptet, dass NaN per Definition größer ist als jede andere Zahl und damit auch größer als Unendlichkeit. Das Gleiche gilt umgekehrt für -NaN. Hallo, Liebhaber der Mathematikanalyse! Aber man muss daran denken, dass das alles im Kontext der reellen Zahlen gilt.
Augen zusammenkneifen
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Ein weiterer unerwarteter Gruß von der Datenbank. Und wieder muss man sich merken, dass für die Typen double precision und numeric unterschiedliche Rundungsregeln gelten. Bei numeric gilt die übliche Regel, dass 0,5 aufgerundet wird, während bei double precision 0,5 in Richtung der nächstgelegenen geraden Zahl gerundet wird.
Geld ist etwas Besonderes.
SELECT '10'::money::float8
ERROR: kann Typ money nicht in double precision umwandeln
LINE 1: SELECT '10'::money::float8
^
********** Fehler **********
ERROR: kann Typ money nicht in double precision umwandeln
SQL-Zustand: 42846
Symbol: 19Laut PostgreSQL ist Geld keine reelle Zahl. Einige Menschen sehen das ebenso. Wir müssen jedoch bedenken, dass eine Typumwandlung von money nur in den Typ numeric möglich ist, genauso wie man den Typ numeric nur in den Typ money umwandeln kann. An diesen Typen kann man dann nach Belieben arbeiten. Aber das wären dann nicht mehr die gleichen Geldwerte.
Smallint und die Generierung von Sequenzen
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
FEHLER: Funktion generate_series(smallint, smallint, smallint) ist nicht eindeutig
ZEILE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
HINWEIS: Es konnte keine beste Kandidatenfunktion ausgewählt werden. Möglicherweise müssen Sie explizite Typumwandlungen hinzufügen.
********** Fehler **********
FEHLER: Funktion generate_series(smallint, smallint, smallint) ist nicht eindeutig
SQL-Zustand: 42725
Hinweis: Es konnte keine beste Kandidatenfunktion ausgewählt werden. Möglicherweise müssen Sie explizite Typumwandlungen hinzufügen.
Symbol: 18PostgreSQL geht es nicht um Kleinigkeiten. Welche Sequenzen basieren auf smallint? Int, nicht weniger! Daher versucht die Datenbank bei dem Versuch, die vorherige Anfrage auszuführen, smallint in einen anderen Ganzzahltyp zu konvertieren und sieht, dass es mehrere Umwandlungen geben kann. Welche Umwandlung zu wählen ist? Das kann sie nicht entscheiden, und deshalb schlägt die Anfrage fehl.
Aktenzeichen Nummer zwei. «char»/char/varchar/text
Es gibt auch einige Merkwürdigkeiten bei den Zeichentypen. Lassen Sie uns auch mit ihnen vertraut machen.
Was soll das für ein Zauber sein?
SELECT 'PETJA'::"char"
, 'PETJA'::"char"::bytea
, 'PETJA'::char
, 'PETJA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fWas ist dieser Typ „char“, was für ein Scherzbold? Solche brauchen wir nicht… Denn er gibt sich als normales char aus, nur weil es in Anführungszeichen steht. Er unterscheidet sich von einem normalen char ohne Anführungszeichen dadurch, dass er nur das erste Byte der string-Darstellung ausgibt, während ein normales char das erste Zeichen ausgibt. In unserem Fall ist das erste Zeichen der Buchstabe П, der in der Unicode-Darstellung 2 Bytes benötigt, was die Umwandlung des Ergebnisses in den Typ bytea zeigt. Der Typ „char“ nimmt nur das erste Byte dieser Unicode-Darstellung. Wozu also braucht man diesen Typ? Die PostgreSQL-Dokumentation sagt, dass dies ein spezieller Typ ist, der für besondere Bedürfnisse verwendet wird. Also werden wir ihn wahrscheinlich nicht benötigen. Aber schauen Sie ihm ins Auge 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 | x616263202020Sehen Sie sich das folgende Beispiel an. Ich habe absichtlich alle Ergebnisse im Typ bytea dargestellt, damit deutlich wird, was darin enthalten ist. Wo sind die nachfolgenden Leerzeichen nach der Umwandlung in den Typ varchar(6)? Die Dokumentation sagt prägnant: „Bei der Umwandlung eines Zeichentyps in einen anderen werden die ergänzenden Leerzeichen entfernt.“ Diese Abneigung sollten Sie sich merken. Und beachten Sie, dass, wenn die Zeichenkonstante in Anführungszeichen sofort in den Typ varchar(6) umgewandelt wird, die abschließenden Leerzeichen erhalten bleiben. So etwas.
Aktenzeichen Nummer drei. json/jsonb
JSON ist eine eigenständige Struktur, die ihr eigenes Leben führt. Daher unterscheiden sich ihre Entitäten ein wenig von denen von PostgreSQL. Hier sind einige Beispiele.
Johnson und Johnson. Spüren Sie den Unterschied.
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fEs liegt daran, dass JSON eine eigene Null-Eigenschaft hat, die nicht das Äquivalent zu NULL in PostgreSQL ist. Gleichzeitig kann das JSON-Objekt jedoch den Wert NULL haben, weshalb der Ausdruck SELECT null::jsonb IS NULL (achten Sie auf das Fehlen von einfachen Anführungszeichen) diesmal true zurückgibt.
Ein Buchstabe verä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]}Der Unterschied ist, dass json und jsonb völlig verschiedene Strukturen sind. In json wird das Objekt so gespeichert, wie es ist, während es in jsonb bereits als analysierte indexierte Struktur vorliegt. Daher wurde im letzten Fall der Wert des Objekts für den Schlüssel 1 von [1, 2, 3] auf [7, 8, 9] geändert, das am Ende mit demselben Schlüssel in die Struktur kam.
Man kann vom Wasser kein Gesicht 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 Implementierung von JSONB die Formatierung von Gleitkommazahlen und bringt sie in das klassische Format. Bei dem Typ JSON passiert dies nicht. Seltsamerweise, aber es ist sein Recht.
Aktennummer vier. date/time/timestamp
Bei den Datums- und Zeitformaten gibt es ebenfalls einige Besonderheiten. Schauen wir uns diese näher an. Ich möchte gleich bemerken, dass einige der Verhaltensweisen 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: Datums-/Zeitfeldwert außerhalb des zulässigen Bereichs: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Vielleicht benötigen Sie eine andere "datestyle"-Einstellung.
********** Fehler **********
ERROR: Datums-/Zeitfeldwert außerhalb des zulässigen Bereichs: "08-Jan-99"
SQL-Zustand: 22008
Hinweis: Vielleicht benötigen Sie eine andere "datestyle"-Einstellung.
Zeichen: 8Es scheint, dass hier nichts unklar ist? Aber die Datenbank versteht nicht, was wir hier an erster Stelle gesetzt haben – das Jahr oder den Tag? Und sie entscheidet, dass das der 99. Januar 2008 ist, was sie überfordert. Im Allgemeinen sollte man beim Übergeben von Daten im Textformat sehr genau überprüfen, wie gut die Datenbank diese erkannt hat (insbesondere den Parameter datestyle mit dem Befehl SHOW datestyle analysieren), da Unklarheiten in diesem Bereich sehr kostspielig werden können.
Woher kommst du?
SELECT '04:05 Europe/Moscow'::time
FEHLER: ungültige Eingabesyntax für den Typ time: "04:05 Europe/Moscow"
ZEILE 1: SELECT '04:05 Europe/Moscow'::time
^
********** Fehler **********
FEHLER: ungültige Eingabesyntax für den Typ time: "04:05 Europe/Moscow"
SQL-Zustand: 22007
Zeichen: 8Warum kann die Datenbank die eindeutig angegebene Zeit nicht verstehen? Weil anstelle einer Abkürzung für die Zeitzone der volle Name angegeben ist, der nur im Kontext eines Datums Sinn macht, da er die Geschichte der Zeitzonenänderungen berücksichtigt. Ohne Datum funktioniert das nicht. Und auch die Formulierung der Zeitzeichenfolge wirft Fragen auf — was wollte der Programmierer tatsächlich sagen? Daher ist es logisch, wenn man sich damit auseinander setzt.
Was ist falsch mit ihm?
Stellen Sie sich die Situation vor. In Ihrer Tabelle gibt es ein Feld vom Typ timestamptz. Sie möchten es indizieren. Aber Sie verstehen, dass es nicht immer sinnvoll ist, einen Index für dieses Feld zu erstellen, da es eine hohe Selektivität aufweist (fast alle Werte dieses Typs werden einzigartig sein). Daher beschließen Sie, die Selektivität des Index zu verringern, indem Sie diesen Typ auf ein Datum bringen. 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: 42P17Das Problem liegt darin, dass beim Konvertieren des Typs timestamptz in den Typ date der Wert des Systemparameters TimeZone verwendet wird, was die Typkonvertierungsfunktion von einem konfigurierbaren Parameter abhängig macht, also veränderlich (volatile) ist. Solche Funktionen sind im Index nicht zulässig. In diesem Fall muss der verwendete Zeitzonen-Konvertierungsbereich explizit angegeben werden.
Wenn now überhaupt nicht now ist
Wir sind daran gewöhnt, dass now() das aktuelle Datum/Zeit unter Berücksichtigung der Zeitzone zurückgibt. Aber schauen Sie sich die folgenden Abfragen an:
START TRANSACTION;
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 bleibt unabhängig davon, wie viel Zeit seit der letzten Anfrage vergangen ist, gleich! Was bedeutet das? Dass now() nicht die aktuelle Zeit ist, sondern die Zeit, die zu Beginn der aktuellen Transaktion festgelegt wurde. Daher ändert es sich innerhalb einer Transaktion nicht. Jede Anfrage, die außerhalb einer Transaktion ausgeführt wird, wird implizit in eine Transaktion gewickelt, weshalb wir nicht bemerken, dass die Zeit, die durch eine einfache Anfrage wie SELECT now(); zurückgegeben wird, tatsächlich nicht die aktuelle Zeit ist... Wenn Sie die echte aktuelle Zeit erhalten möchten, sollten Sie die Funktion clock_timestamp() verwenden.
Dossier Nummer fünf. bit
Ein wenig seltsam
SELECT '111'::bit(4)
bit
bit(4)
------
1110Von welcher Seite sollten Bits bei einer Typvergrößerung hinzugefügt werden? Es scheint, als ob es von links sein sollte. Aber die Datenbank hat diesbezüglich eine andere Meinung. Seien Sie vorsichtig: Bei einer Diskrepanz in der Anzahl der Bits während des Typs wird das Ergebnis ganz anders sein, als Sie dachten. Dies gilt sowohl für das Hinzufügen von Bits rechts als auch für das Kürzen von Bits. Auch rechts...
Dossier Nummer sechs. Arrays
Selbst NULL kam nicht durch
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Wie es sich für erfahrene SQL-Nutzer gehört, erwarten wir, dass das Ergebnis dieses Ausdrucks NULL sein wird. Aber dem ist nicht so. Es wird ein Array zurückgegeben. Warum? Weil die Datenbank in diesem Fall NULL in ein ganzzahliges Array umwandelt und die Funktion array_cat implizit aufruft. Dennoch bleibt unklar, warum dieser "Array-Kater" das Array nicht leert. Solches Verhalten sollte man einfach im Hinterkopf behalten.
Fassen wir zusammen. Es gibt genügend Merkwürdigkeiten. Die meisten davon sind zwar nicht so gravierend, dass man von völlig unangemessenem Verhalten sprechen müsste. Andere lassen sich durch Benutzerfreundlichkeit oder die Häufigkeit ihres Auftretens in bestimmten Situationen erklären. Gleichzeitig gibt es jedoch viele Überraschungen. Daher ist es wichtig, darüber Bescheid zu wissen. Wenn Ihnen noch etwas Seltsames oder Ungewöhnliches im Verhalten bestimmter Typen auffällt, lassen Sie es mich in den Kommentaren wissen, ich werde die bestehenden Informationen gerne ergänzen.
Quelle: habr.com
