Ich ich die äußeren Erscheinungen nichts Verdächtiges. Im Gegenteil, sie kommen dir sogar gut und vertraut vor. Aber das ist nur so lange der Fall, bis du sie überprüfst. Hier offenbart sich ihre heimtückische Natur, und sie funktionieren ganz anders, als du erwartet hast. Manchmal machen sie sogar so etwas, dass dir einfach die Haare zu Berge stehen – zum Beispiel, wenn sie dir vertraute geheime Daten verlieren. Wenn du sie zur Rede stellst, behaupten sie, sich nicht zu kennen, während sie im Verborgenen fleißig unter einer Decke arbeiten. Es ist höchste Zeit, sie ans Licht zu bringen. Lass uns also diese verdächtigen Typen genauer unter die Lupe nehmen.
Die Datentypisierung in PostgreSQL bringt trotz ihrer Logik manchmal sehr merkwürdige Überraschungen. In diesem Artikel versuchen wir, einige ihrer Eigenheiten zu klären, die Ursache ihres seltsamen Verhaltens zu verstehen und zu lernen, wie man im Alltag keine Probleme bekommt. Um ehrlich zu sein, habe ich diesen Artikel auch als eine Art Nachschlagewerk für mich selbst erstellt, ein Nachschlagewerk, auf das ich in strittigen Fällen leicht zurückgreifen kann. Daher wird es weiterhin aktualisiert, sobald ich neue Überraschungen von verdächtigen Typen entdecke. Also, auf geht's, unermüdliche Datenbankspürnasen!
Akte Nummer eins. real/double precision/numeric/money
Es könnte scheinen, dass numerische Typen die wenigsten Probleme mit Überraschungen im Verhalten sind. Aber weit gefehlt. Deshalb fangen wir damit an. Also…
Wir haben das Rechnen verlernt
SELECT 0.1::real = 0.1
?column?
boolean
---------
fWas ist los? PostgreSQL konvertiert die untypisierte Konstante 0.1 in den Typ double precision und versucht, sie mit 0.1 vom Typ real zu vergleichen. Das sind völlig verschiedene Werte! Das liegt an der Darstellung reeller Zahlen im Arbeitsspeicher. Da 0.1 nicht als endliche binäre Bruchzahl dargestellt werden kann (das wird 0.0(0011) im binären Format sein), werden Zahlen mit unterschiedlicher Genauigkeit unterschiedlich sein, was das Ergebnis erklärt, dass sie ungleich sind. Im Allgemeinen ist dies ein Thema für einen eigenen Artikel, ich möchte 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)
^
********** Fehler **********
ERROR: syntax error at or near "("
SQL-Zustand: 42601
Zeichen: 24Wiele osób wie, że PostgreSQL pozwala na funkcjonalne rzutowanie typów. Można zatem zapisać nie tylko 1::int, ale również int(1), co jest równoważne. Jednakże, nie dotyczy to typów, których nazwy składają się z kilku słów! Dlatego, jeśli chcesz przekonwertować wartość liczbową na typ double precision w formie funkcjonalnej, użyj aliasu tego typu float8, czyli SELECT float8(1).
Co jest większe od nieskończoności?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tOto jak to wygląda! Okazuje się, że jest coś większego od nieskończoności, a jest to NaN! Przy tym dokumentacja PostgreSQL spogląda na nas uczciwie i twierdzi, że NaN jest z definicji większe od jakiejkolwiek innej liczby, a zatem od nieskończoności. To samo dotyczy -NaN. Cześć, miłośnicy analizy matematycznej! Ale trzeba pamiętać, że wszystko to działa w kontekście liczb rzeczywistych.
Okrąglone oczy
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Jeszcze jedno niespodziewane powitanie od bazy. I znowu trzeba zapamiętać, że dla typów double precision i numeric obowiązują różne zasady zaokrąglania. Dla numeric — normalne, kiedy 0,5 zaokrągla się w górę, a dla double precision — zaokrąglanie 0,5 odbywa się w kierunku najbliższej parzystej liczby całkowitej.
Pieniądze to coś wyjątkowego
SELECT '10'::money::float8
ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
^
********** Błąd **********
ERROR: cannot cast type money to double precision
Stan SQL: 42846
Symbol: 19Według PostgreSQL, pieniądze nie są liczbami rzeczywistymi. Według niektórych osób również. Musimy pamiętać, że konwersja typu money jest możliwa tylko na typ numeric, podobnie jak na typ money można przekonwertować tylko typ numeric. A już z nim można się bawić, jak dusza zapragnie. Ale to już nie będą te same pieniądze.
Smallint i generowanie sekwencji
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.
********** Błąd **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
Stan SQL: 42725
Podpowiedź: Could not choose a best candidate function. You might need to add explicit type casts.
Symbol: 18PostgreSQL nie lubi małych rzeczy. Jakie to sekwencje na podstawie smallint? int, przynajmniej! Dlatego przy próbie wykonania powyższego zapytania baza danych próbuje przekształcić smallint w inny typ całkowity i widzi, że takich przekształceń może być kilka. Które przekształcenie wybrać? To nie może zdecydować, dlatego kończy z błędem.
Dossier numer dwa. „char”/char/varchar/text
Istnieje również wiele dziwactw związanych z typami znakowymi. Poznajmy je również.
Co to za sztuczki?
SELECT 'PETYA'::"char"
, 'PETYA'::"char"::bytea
, 'PETYA'::char
, 'PETYA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fCzym jest ten typ „char”, co to za clown? Nie potrzebujemy tego... Ponieważ udaje zwykły char, mimo że jest w cudzysłowie. Różni się od zwykłego char, który jest bez cudzysłowu, tym, że wyświetla tylko pierwszy bajt reprezentacji ciągu, podczas gdy normalny char wyświetla pierwszy znak. W naszym przypadku pierwszy znak to litera P, która w reprezentacji unicode zajmuje 2 bajty, o czym świadczy konwersja wyniku do typu bytea. A typ „char” bierze tylko pierwszy bajt tej reprezentacji unicode. Po co ten typ jest potrzebny? Dokumentacja PostgreSQL mówi, że to specjalny typ używany do szczególnych potrzeb. Tak więc raczej nie będziemy go potrzebować. Ale spójrz mu w oczy i nie pomyl się, kiedy go spotkasz z jego szczególnym zachowaniem.
Zbędne spacje. Z oczu, z serca precz
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Spójrz na podany przykład. Specjalnie sprowadziłem wszystkie wyniki do typu bytea, aby było wyraźnie widać, co tam jest. Gdzie są końcowe spacje po przekształceniu do typu varchar(6)? Dokumentacja zwięźle stwierdza: „Przy przekształceniu wartości character na inny typ znakowy nadmiarowe spacje są odrzucane”. Tę niechęć należy zapamiętać. Zauważ, że jeśli stała tekstowa w cudzysłowie jest natychmiast przekształcana do typu varchar(6), końcowe spacje są zachowywane. Takie są cuda.
Dossier numer trzy. json/jsonb
JSON to oddzielna struktura, która prowadzi swoje życie. Dlatego jej byty i byty PostgreSQL nieco się różnią. Oto przykłady.
Johnson & Johnson. Poczuj różnicę
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fChodzi o to, że JSON ma swoją własną wartość null, która nie jest odpowiednikiem NULL w PostgreSQL. Jednocześnie sam obiekt JSON może mieć wartość NULL, dlatego wyrażenie SELECT null::jsonb IS NULL (zwróć uwagę na brak pojedynczych cudzysłowów) tym razem zwróci true.
Jedna litera zmienia wszystko
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]}Chodzi o to, że json i jsonb to zupełnie różne struktury. W json obiekt jest przechowywany tak, jak jest, a w jsonb jest przechowywany jako rozłożona, zaindeksowana struktura. Dlatego w drugim przypadku wartość obiektu pod kluczem 1 została zmieniona z [1, 2, 3] na [7, 8, 9], które przybyło do struktury na samym końcu z tym samym kluczem.
Z wody nie pić
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL w implementacji JSONB zmienia formatowanie liczb rzeczywistych, przekształcając je do klasycznego formatu. Dla typu JSON tak się nie dzieje. Trochę dziwne, ale jego prawo.
Dossier numer cztery. date/time/timestamp
Z typami dat/czasu również są pewne dziwności. Przyjrzyjmy im się. Od razu zaznaczam, że niektóre cechy zachowania stają się zrozumiałe, jeśli dobrze rozumie się istotę pracy z strefami czasowymi. Ale to również temat na osobny artykuł.
To nie moja wina, że nie rozumiesz
SELECT '08-Jan-99'::date
ERROR: data/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Może potrzebujesz innego ustawienia "datestyle".
********** Błąd **********
ERROR: wartość pola data/time poza zakresem: "08-Jan-99"
SQL-state: 22008
Podpowiedź: Może potrzebujesz innego ustawienia "datestyle".
Symbol: 8Wydawałoby się, że nie ma tu nic niezrozumiałego? Ale wciąż baza nie rozumie, co postawiliśmy na pierwszym miejscu — rok czy dzień? I decyduje, że to 99 stycznia 2008 roku, co całkowicie ją dezorientuje. Ogólnie rzecz biorąc, w przypadku przesyłania dat w formacie tekstowym należy bardzo dokładnie sprawdzić, jak baza je rozpoznała (w szczególności analizując parametr datestyle poleceniem SHOW datestyle), ponieważ niejednoznaczności w tej kwestii mogą kosztować bardzo drogo.
Skąd ty się wziąłeś?
SELECT '04:05 Europe/Moscow'::time
ERROR: nieprawidłowa składnia wejściowa dla typu time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
^
********** Błąd **********
ERROR: nieprawidłowa składnia wejściowa dla typu time: "04:05 Europe/Moscow"
Stan SQL: 22007
Symbol: 8Dlaczego baza danych nie może zrozumieć wyraźnie podanego czasu? Ponieważ dla strefy czasowej podano nie skrót, a pełną nazwę, która ma sens tylko w kontekście daty, ponieważ uwzględnia historię zmian stref czasowych, a ta bez daty nie działa. Sama formuła ciągu czasu również budzi wątpliwości — co tak naprawdę miał na myśli programista? Dlatego wszystko jest logiczne, jeśli się nad tym zastanowić.
Co mu się nie podoba?
Wyobraź sobie sytuację. Masz pole w tabeli o typie timestamptz. Chcesz je zaindeksować. Ale zdajesz sobie sprawę, że budowanie indeksu na tym polu nie zawsze ma sens z powodu jego wysokiej selektywności (niemal wszystkie wartości tego typu będą unikalne). Dlatego postanawiasz obniżyć selektywność indeksu, przekształcając ten typ na datę. I dostajesz niespodziankę:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
ERROR: funkcje w wyrażeniu indeksu muszą być oznaczone jako IMMUTABLE
********** Błąd **********
ERROR: funkcje w wyrażeniu indeksu muszą być oznaczone jako IMMUTABLE
Stan SQL: 42P17O co chodzi? O to, że do przekształcenia typu timestamptz na typ date używana jest wartość parametru systemowego TimeZone, co sprawia, że funkcja przekształcania typu jest zależna od konfigurowanego parametru, tzn. zmienna (volatile). Takie funkcje są niedozwolone w indeksie. W tym przypadku należy wyraźnie określić, w której strefie czasowej następuje przekształcenie typu.
Kiedy now wcale nie jest now
Przywykliśmy, że now() zwraca aktualną datę/czas z uwzględnieniem strefy czasowej. Ale spójrz na poniższe zapytania:
START TRANSACTION;
SELECT now();
now
znacznik czasu z strefą czasową
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
znacznik czasu z strefą czasową
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
znacznik czasu z strefą czasową
-----------------------------
2019-11-26 13:13:04.271419+03
COMMIT;Data/czas wracają takie same, niezależnie od tego, ile czasu minęło od poprzedniego zapytania! O co chodzi? Chodzi o to, że now() nie jest aktualnym czasem, a czasem rozpoczęcia bieżącej transakcji. Dlatego w ramach transakcji się nie zmienia. Każde zapytanie, które uruchamiane jest poza ramami transakcji, niejawnie opakowane jest w transakcję, dlatego nie zauważamy, że czas, zwracany przez proste zapytanie SELECT now(); w rzeczywistości nie jest aktualny… Jeśli chcesz uzyskać prawdziwy aktualny czas, musisz użyć funkcji clock_timestamp().
Akta numer pięć. bit
Trochę dziwne
SELECT '111'::bit(4)
bit
bit(4)
------
1110Z której strony należy dodawać bity w przypadku rozszerzania typu? Wydaje się, że z lewej. Ale baza ma na ten temat inną opinię. Uważaj: przy niezgodności liczby bitów podczas konwersji typu, otrzymasz zupełnie coś innego, niż chciałeś. Dotyczy to zarówno dodawania bitów z prawej, jak i obcinania bitów. Też z prawej…
Akta numer sześć. Tablice
Nawet NULL nie zadziałał
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Jak normalni ludzie, wychowani na SQL, spodziewamy się, że wynikiem tego wyrażenia będzie NULL. Ale jednak nie. Zwracana jest tablica. Dlaczego? Ponieważ w tym przypadku baza przekształca NULL na tablicę całkowitą i niejawnie wywołuje funkcję array_cat. Mimo to pozostaje niejasne, dlaczego ten „kot tablicowy” nie zeruje tablicy. Takie zachowanie również warto po prostu zapamiętać.
Podsumujmy. Jest wiele dziwności. Większość z nich nie jest na tyle krytyczna, aby mówić o rażąco nieadekwatnym zachowaniu. A inne tłumaczą się wygodą użytkowania lub częstotliwością występowania w różnych sytuacjach. Niemniej jednak jest wiele niespodzianek. Dlatego warto o nich wiedzieć. Jeśli znajdziesz jeszcze coś dziwnego lub nietypowego w zachowaniu jakichkolwiek typów, napisz w komentarzach, z przyjemnością uzupełnię istniejące akta.
Źródło: habr.com
