An ihrem Aussehen ist nichts Verdächtiges. Darüber hinaus kommen sie einem sogar schon lange und gut bekannt vor. Aber das ist nur so lange, bis Sie sie überprüfen. Hier zeigen sie ihre heimtückische Natur und funktionieren völlig anders als erwartet. Und manchmal tun sie etwas, das einem die Haare zu Berge stehen lässt – zum Beispiel verlieren sie ihnen anvertraute geheime Daten. Wenn man sie zur Rede stellt, behaupten sie, dass sie sich nicht kennen, obwohl sie im Verborgenen unter der gleichen Haube hart arbeiten. Es ist Zeit, sie endlich an sauberes Wasser zu bringen. Beschäftigen wir uns auch mit diesen verdächtigen Typen.
Die Dateneingabe in PostgreSQL birgt trotz aller Logik manchmal sehr seltsame Überraschungen. In diesem Artikel werden wir versuchen, einige ihrer Macken zu klären, den Grund für ihr seltsames Verhalten zu verstehen und zu verstehen, wie wir in der täglichen Praxis nicht auf Probleme stoßen. Ehrlich gesagt habe ich diesen Artikel auch als eine Art Nachschlagewerk für mich selbst zusammengestellt, ein Nachschlagewerk, auf das man in kontroversen Fällen leicht zurückgreifen kann. Daher wird es wieder aufgefüllt, sobald neue Überraschungen verdächtiger Typen entdeckt werden. Also, nichts wie los, ihr unermüdlichen Datenbank-Tracker!
Dossier Nummer eins. Real/doppelte Genauigkeit/numerisch/Geld
Es scheint, dass numerische Typen im Hinblick auf Verhaltensüberraschungen am wenigsten problematisch sind. Aber egal wie es ist. Beginnen wir also mit ihnen. Also…
Ich habe vergessen, wie man zählt
SELECT 0.1::real = 0.1
?column?
boolean
---------
fWas ist los? Das Problem besteht darin, dass PostgreSQL die untypisierte Konstante 0.1 in doppelte Genauigkeit konvertiert und versucht, sie mit 0.1 des realen Typs zu vergleichen. Und das sind völlig unterschiedliche Bedeutungen! Die Idee besteht darin, reelle Zahlen im Maschinenspeicher darzustellen. Da 0.1 nicht als endlicher binärer Bruch dargestellt werden kann (binär wäre es 0.0(0011), sind Zahlen mit unterschiedlichen Bittiefen unterschiedlich, was zur Folge hat, dass sie nicht gleich sind. Im Allgemeinen ist dies ein Thema für einen separaten Artikel; ich werde hier nicht näher darauf eingehen.
Woher kommt der Fehler?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Viele Leute wissen, dass PostgreSQL eine funktionale Notation für die Typumwandlung ermöglicht. Das heißt, Sie können nicht nur 1::int, sondern auch int(1) schreiben, was äquivalent ist. Allerdings nicht für Typen, deren Namen aus mehreren Wörtern bestehen! Wenn Sie daher einen numerischen Wert in funktionaler Form in einen Typ mit doppelter Genauigkeit umwandeln möchten, verwenden Sie den Alias dieses Typs float8, d. h. SELECT float8(1).
Was ist größer als die Unendlichkeit?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tSchauen Sie, wie es ist! Es stellt sich heraus, dass es etwas Größeres als die Unendlichkeit gibt, und zwar NaN! Gleichzeitig blickt uns die PostgreSQL-Dokumentation mit ehrlichen Augen an und behauptet, dass NaN offensichtlich größer als jede andere Zahl und daher unendlich sei. Das Gegenteil gilt auch für -NaN. Hallo, Mathe-Liebhaber! Wir müssen jedoch bedenken, dass dies alles im Zusammenhang mit reellen Zahlen funktioniert.
Augenrundung
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Ein weiterer unerwarteter Gruß von der Basis. Denken Sie auch hier daran, dass doppelte Genauigkeit und numerische Typen unterschiedliche Rundungseffekte haben. Bei numerischen Werten ist es üblich, dass 0,5 aufgerundet wird, und bei doppelter Genauigkeit wird 0,5 auf die nächste gerade ganze Zahl gerundet.
Geld ist etwas Besonderes
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
Символ: 19Laut PostgreSQL ist Geld keine reelle Zahl. Auch nach Meinung einiger Personen. Wir müssen bedenken, dass die Umwandlung des Geldtyps nur in den numerischen Typ möglich ist, genauso wie nur der numerische Typ in den Geldtyp umgewandelt werden kann. Aber jetzt können Sie damit spielen, wie Ihr Herz begehrt. Aber es wird nicht das gleiche Geld sein.
Smallint- und Sequenzgenerierung
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 verschwendet keine Zeit mit Kleinigkeiten. Was sind diese Sequenzen, die auf Smallint basieren? int, nicht weniger! Daher versucht die Datenbank beim Versuch, die obige Abfrage auszuführen, smallint in einen anderen Ganzzahltyp umzuwandeln, und stellt fest, dass es möglicherweise mehrere solcher Umwandlungen gibt. Welche Besetzung wählen? Sie kann dies nicht entscheiden und stürzt daher mit einem Fehler ab.
Akte Nummer zwei. „char“/char/varchar/text
Auch bei den Charaktertypen gibt es eine Reihe von Kuriositäten. Lernen wir sie auch kennen.
Was sind das für Tricks?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fWas ist das für ein „Char“, was ist das für ein Clown? Die brauchen wir nicht ... Weil es vorgibt, ein gewöhnliches Zeichen zu sein, obwohl es in Anführungszeichen steht. Und es unterscheidet sich von einem regulären char, das ohne Anführungszeichen ist, darin, dass es 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 P, der in der Unicode-Darstellung 2 Bytes einnimmt, wie durch die Konvertierung des Ergebnisses in den Bytea-Typ nachgewiesen wird. Und der Typ „char“ benötigt nur das erste Byte dieser Unicode-Darstellung. Warum wird dieser Typ dann benötigt? In der PostgreSQL-Dokumentation heißt es, dass es sich hierbei um einen speziellen Typ handelt, der für besondere Anforderungen verwendet wird. Es ist also unwahrscheinlich, dass wir es brauchen. Aber schauen Sie ihm in die Augen und Sie werden sich nicht täuschen, wenn Sie ihm mit seinem besonderen Verhalten begegnen.
Zusätzliche Leerzeichen. Aus dem Auge, 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 | x616263202020Schauen Sie sich das gegebene Beispiel an. Ich habe alle Ergebnisse speziell in den Bytea-Typ konvertiert, damit deutlich sichtbar war, was da war. Wo sind die nachgestellten Leerzeichen nach der Umwandlung in varchar(6)? In der Dokumentation heißt es kurz und bündig: „Beim Umwandeln des Zeichenwerts in einen anderen Zeichentyp werden nachgestellte Leerzeichen verworfen.“ Diese Abneigung muss in Erinnerung bleiben. Und beachten Sie, dass die nachfolgenden Leerzeichen erhalten bleiben, wenn eine in Anführungszeichen gesetzte Zeichenfolgenkonstante direkt in den Typ varchar(6) umgewandelt wird. Das sind die Wunder.
Akte Nummer drei. json/jsonb
JSON ist eine separate Struktur, die ihr eigenes Leben führt. Daher unterscheiden sich seine Entitäten und die von PostgreSQL geringfügig. Hier sind Beispiele.
Johnson und Johnson. fühle den Unterschied
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fDie Sache ist, dass JSON eine eigene Null-Entität hat, die nicht das Analogon von NULL in PostgreSQL ist. Gleichzeitig kann das JSON-Objekt selbst durchaus den Wert NULL haben, sodass der Ausdruck SELECT null::jsonb IS NULL (beachten Sie das Fehlen einfacher 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]}Die Sache ist, dass JSON und JSONB völlig unterschiedliche Strukturen sind. In JSON wird das Objekt so gespeichert, wie es ist, und in JSONB ist es bereits in Form einer analysierten, indizierten Struktur gespeichert. Deshalb wurde im zweiten Fall der Wert des Objekts durch Schlüssel 1 von [1, 2, 3] auf [7, 8, 9] ersetzt, der ganz am Ende mit demselben Schlüssel in die Struktur kam.
Trinken Sie kein Wasser aus Ihrem Gesicht
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL ändert in seiner JSONB-Implementierung die Formatierung reeller Zahlen und bringt sie in die klassische Form. Dies ist beim JSON-Typ nicht der Fall. Etwas seltsam, aber er hat recht.
Akte Nummer vier. Datum/Uhrzeit/Zeitstempel
Es gibt auch einige Kuriositäten bei Datums-/Uhrzeittypen. Schauen wir sie uns an. Lassen Sie mich gleich einen Vorbehalt anbringen, dass einige Verhaltensmerkmale deutlich werden, wenn Sie die Essenz der Arbeit mit Zeitzonen gut verstehen. Dies ist aber auch ein Thema für einen separaten Artikel.
Du bist nicht zu verstehen
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.
Символ: 8Es scheint, was ist hier unverständlich? Aber die Datenbank versteht immer noch nicht, was wir hier an erster Stelle setzen – das Jahr oder den Tag? Und sie beschließt, dass es der 99. Januar 2008 ist, was sie umgehauen hat. Im Allgemeinen muss bei der Übertragung von Daten im Textformat sehr sorgfältig geprüft werden, wie korrekt die Datenbank sie erkannt hat (insbesondere den Parameter datestyle mit dem Befehl SHOW datestyle analysieren), da Unklarheiten in dieser Angelegenheit sehr teuer sein können.
Wo kommst du her
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
Символ: 8Warum kann die Datenbank die explizit angegebene Zeit nicht verstehen? Denn die Zeitzone hat keine Abkürzung, sondern einen vollständigen Namen, der nur im Kontext eines Datums Sinn macht, da er den Verlauf der Zeitzonenänderungen berücksichtigt, und ohne Datum funktioniert es nicht. Und schon der Wortlaut der Zeitleiste wirft Fragen auf: Was meinte der Programmierer wirklich? Daher ist hier alles logisch, wenn man es betrachtet.
Was stimmt nicht mit ihm?
Stellen Sie sich die Situation vor. Sie haben in Ihrer Tabelle ein Feld vom Typ timestamptz. Sie möchten es indizieren. Sie verstehen jedoch, dass die Erstellung eines Index für dieses Feld aufgrund seiner hohen Selektivität nicht immer gerechtfertigt ist (fast alle Werte dieses Typs sind eindeutig). Sie beschließen also, die Selektivität des Index zu verringern, indem Sie den Typ in ein Datum umwandeln. Und Sie erleben eine Überraschung:
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-состояние: 42P17Was ist los? Tatsache ist, dass zum Umwandeln eines Timestamptz-Typs in einen Datumstyp der Wert des TimeZone-Systemparameters verwendet wird, wodurch die Typkonvertierungsfunktion von einem benutzerdefinierten Parameter abhängig wird, d. h. flüchtig. Solche Funktionen sind im Index nicht erlaubt. In diesem Fall müssen Sie explizit angeben, in welcher Zeitzone die Typumwandlung durchgeführt wird.
Wenn jetzt überhaupt nicht einmal jetzt ist
Wir sind es gewohnt, dass now() das aktuelle Datum/die aktuelle Uhrzeit unter Berücksichtigung der Zeitzone zurückgibt. Aber schauen Sie sich die folgenden Abfragen an:
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;Das Datum/die Uhrzeit wird unverändert zurückgegeben, unabhängig davon, wie viel Zeit seit der vorherigen Anfrage vergangen ist! Was ist los? Tatsache ist, dass now() nicht die aktuelle Zeit ist, sondern die Startzeit der aktuellen Transaktion. Daher ändert es sich innerhalb der Transaktion nicht. Jede außerhalb des Bereichs einer Transaktion gestartete Abfrage wird implizit in eine Transaktion eingeschlossen, weshalb wir nicht bemerken, dass die von einem einfachen SELECT now(); Tatsächlich nicht die aktuelle ... Wenn Sie eine ehrliche aktuelle Uhrzeit erhalten möchten, müssen Sie die Funktion clock_timestamp() verwenden.
Akte Nummer fünf. bisschen
Ein bisschen seltsam
SELECT '111'::bit(4)
bit
bit(4)
------
1110Auf welcher Seite sollen die Bits bei einer Typerweiterung hinzugefügt werden? Es scheint auf der linken Seite zu sein. Aber nur die Basis ist zu diesem Thema anderer Meinung. Seien Sie vorsichtig: Wenn die Anzahl der Ziffern beim Umwandeln eines Typs nicht übereinstimmt, erhalten Sie nicht das, was Sie wollten. Dies gilt sowohl für das Hinzufügen von Bits nach rechts als auch für das Trimmen von Bits. Auch rechts...
Akte Nummer sechs. Arrays
Selbst NULL wurde nicht ausgelöst
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Als normale Leute, die mit SQL aufgewachsen sind, erwarten wir, dass das Ergebnis dieses Ausdrucks NULL ist. Aber es war nicht da. Es wird ein Array zurückgegeben. Warum? Denn in diesem Fall wandelt die Basis NULL in ein ganzzahliges Array um und ruft implizit die Funktion array_cat auf. Es bleibt jedoch weiterhin unklar, warum diese „Array-Katze“ das Array nicht zurücksetzt. Auch dieses Verhalten muss man sich merken.
Zusammenfassen. Es gibt viele seltsame Dinge. Die meisten von ihnen sind natürlich nicht so kritisch, dass sie von offensichtlich unangemessenem Verhalten sprechen. Und andere werden durch die Benutzerfreundlichkeit oder die Häufigkeit ihrer Anwendbarkeit in bestimmten Situationen erklärt. Aber gleichzeitig gibt es viele Überraschungen. Daher müssen Sie über sie Bescheid wissen. Wenn Sie sonst noch etwas Merkwürdiges oder Ungewöhnliches am Verhalten irgendeiner Art finden, schreiben Sie es in die Kommentare, ich werde gerne die dazu verfügbaren Dossiers ergänzen.
Source: habr.com