🥇Podejrzane typy

W ich wyglądzie nie ma nic podejrzanego. Co więcej, wydają ci się nawet znajome dobrze i przez długi czas. Ale to tylko do czasu, aż ich sprawdzisz. Tutaj pokazują swoją podstępną naturę, działając zupełnie inaczej, niż się spodziewałeś. A czasami robią coś, od czego jeżą się włosy – na przykład tracą powierzone im tajne dane. Kiedy się z nimi skonfrontujesz, twierdzą, że się nie znają, chociaż w cieniu ciężko pracują pod tą samą maską. Czas w końcu doprowadzić je do czystej wody. Zajmijmy się także tymi podejrzanymi typami.

Wpisywanie danych w PostgreSQL, pomimo całej swojej logiki, czasami powoduje bardzo dziwne niespodzianki. W tym artykule postaramy się wyjaśnić niektóre z ich dziwactw, zrozumieć przyczynę ich dziwnego zachowania i dowiedzieć się, jak nie natrafiać na problemy w codziennej praktyce. Prawdę mówiąc, przygotowałem ten artykuł także jako swego rodzaju podręcznik dla siebie, podręcznik, do którego można łatwo sięgnąć w kontrowersyjnych sprawach. Dlatego będzie uzupełniany w miarę odkrycia nowych niespodzianek od podejrzanych typów. A więc do dzieła, niestrudzeni tropiciele baz danych!

Dokumentacja numer jeden. rzeczywista/podwójna precyzja/numeryczna/pieniądze

Wydawać by się mogło, że typy numeryczne sprawiają najmniej problemów, jeśli chodzi o niespodzianki w zachowaniu. Ale nieważne, jak to jest. Zacznijmy więc od nich. Więc…

Zapomniałem, jak liczyć

SELECT 0.1::real = 0.1

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

O co chodzi? Problem polega na tym, że PostgreSQL konwertuje nietypowaną stałą 0.1 na podwójną precyzję i próbuje ją porównać z 0.1 typu rzeczywistego. A to są zupełnie inne znaczenia! Chodzi o to, aby reprezentować liczby rzeczywiste w pamięci maszyny. Ponieważ 0.1 nie można przedstawić jako skończonego ułamka binarnego (w systemie binarnym byłoby to 0.0(0011)), liczby o różnej głębi bitowej będą różne, stąd wynik będzie taki, że nie będą równe. Generalnie jest to temat na osobny artykuł, nie będę tu pisać szerzej.

Skąd bierze się błąd?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24

Wiele osób wie, że PostgreSQL umożliwia notację funkcjonalną do rzutowania typów. Oznacza to, że możesz napisać nie tylko 1::int, ale także int(1), co będzie równoważne. Ale nie dla typów, których nazwa składa się z kilku słów! Dlatego jeśli chcesz rzutować wartość liczbową na typ o podwójnej precyzji 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
---------
t

Zobacz jak to jest! Okazuje się, że istnieje coś większego niż nieskończoność i jest to NaN! Jednocześnie dokumentacja PostgreSQL patrzy na nas uczciwymi oczami i twierdzi, że NaN jest oczywiście większe niż jakakolwiek inna liczba, a zatem nieskończoność. Odwrotnie jest również w przypadku -NaN. Witam miłośników matematyki! Musimy jednak pamiętać, że wszystko to działa w kontekście liczb rzeczywistych.

Zaokrąglenie oczu

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

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

Kolejne nieoczekiwane powitanie z bazy. Ponownie pamiętaj, że typy podwójnej precyzji i typy numeryczne mają różne efekty zaokrąglania. Dla liczb - zwykle, gdy 0,5 zaokrągla się w górę, a dla podwójnej precyzji - 0,5 zaokrągla się do 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
                          ^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19

Według PostgreSQL pieniądze nie są liczbą rzeczywistą. Zdaniem niektórych osób także. Musimy pamiętać, że rzutowanie typu pieniężnego jest możliwe tylko na typ numeryczny, tak samo jak tylko typ liczbowy można rzucać na typ pieniężny. Ale teraz możesz się nim bawić, jak dusza zapragnie. Ale to 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.
********** Ошибка **********
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.
Символ: 18

PostgreSQL nie lubi tracić czasu na drobiazgi. Jakie są te sekwencje oparte na smallint? nie mniej! Dlatego też, próbując wykonać powyższe zapytanie, baza danych próbuje rzutować smallint na inny typ całkowity i widzi, że może być kilka takich rzutowań. Którą obsadę wybrać? Nie może tego zdecydować i dlatego zawiesza się z błędem.

Plik numer dwa. „char”/char/varchar/tekst

W typach postaci występuje również wiele osobliwości. Poznajmy ich i my.

Co to za triki?

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

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

Co to za typ „char”, co to za klaun? Nie potrzebujemy ich... Ponieważ udaje zwykłego znaku, mimo że jest w cudzysłowie. Różni się od zwykłego znaku bez cudzysłowów tym, że wyświetla tylko pierwszy bajt ciągu znaków, podczas gdy normalny znak wyświetla pierwszy znak. W naszym przypadku pierwszym znakiem jest litera P, która w reprezentacji unicode zajmuje 2 bajty, o czym świadczy konwersja wyniku na typ bajtowy. A typ „char” zajmuje tylko pierwszy bajt tej reprezentacji Unicode. Dlaczego więc ten typ jest potrzebny? Dokumentacja PostgreSQL mówi, że jest to specjalny typ używany do specjalnych potrzeb. Więc raczej nie będziemy go potrzebować. Ale spójrz mu w oczy, a nie pomylisz się, gdy spotkasz go z jego szczególnym zachowaniem.

Dodatkowe spacje. Co z oczu to z serca

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

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

Spójrz na podany przykład. Specjalnie przekonwertowałem wszystkie wyniki na typ bajtowy, żeby było wyraźnie widać, co tam jest. Gdzie są końcowe spacje po rzuceniu na varchar(6)? Dokumentacja zwięźle stwierdza: „Podczas rzutowania wartości znaku na inny typ znaku, końcowe białe znaki są odrzucane”. Trzeba pamiętać o tej niechęci. I zauważ, że jeśli cytowana stała łańcuchowa zostanie rzutowana bezpośrednio na typ varchar(6), końcowe spacje zostaną zachowane. Takie są cuda.

Plik numer trzy. json/jsonb

JSON to osobna struktura, która żyje własnym życiem. Dlatego jego byty i PostgreSQL są nieco inne. Oto przykłady.

Johnsona i Johnsona. Poczuj różnicę

SELECT 'null'::jsonb IS NULL

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

Rzecz w tym, że JSON ma własną encję zerową, która nie jest odpowiednikiem NULL w PostgreSQL. Jednocześnie sam obiekt JSON może mieć wartość NULL, zatem wyrażenie SELECT null::jsonb IS NULL (zwróć uwagę na brak pojedynczych cudzysłowów) tym razem zwróci wartość 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]}

Rzecz w tym, że json i jsonb to zupełnie różne struktury. W json obiekt jest przechowywany w niezmienionej postaci, a w jsonb jest już przechowywany w postaci przeanalizowanej, zindeksowanej struktury. Dlatego w drugim przypadku wartość obiektu przy kluczu 1 została zastąpiona z [1, 2, 3] na [7, 8, 9], który wszedł do konstrukcji na samym końcu tym samym kluczem.

Nie pij wody z twarzy

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

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

PostgreSQL w swojej implementacji JSONB zmienia formatowanie liczb rzeczywistych, sprowadzając je do klasycznej postaci. Nie dzieje się tak w przypadku typu JSON. Trochę dziwne, ale ma rację.

Plik numer cztery. data/godzina/znacznik czasu

Istnieją również pewne dziwactwa związane z typami daty/godziny. Przyjrzyjmy się im. Od razu zastrzegam, że niektóre cechy behawioralne staną się jasne, jeśli dobrze zrozumie się istotę pracy ze strefami czasowymi. Ale to też temat na osobny artykuł.

Mój nie rozumie twojego

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.
Символ: 8

Wydawałoby się, że co tu jest niezrozumiałego? Ale baza danych nadal nie rozumie, co stawiamy tutaj na pierwszym miejscu – rok czy dzień? I stwierdza, że jest 99 stycznia 2008 roku, co ją zadziwia. Ogólnie rzecz biorąc, przesyłając daty w formacie tekstowym, trzeba bardzo dokładnie sprawdzić, jak poprawnie rozpoznała je baza danych (w szczególności przeanalizować parametr datestyle poleceniem SHOW datestyle), gdyż niejasności w tej kwestii mogą być bardzo kosztowne.

Skąd się tu wziąłeś?

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
Символ: 8

Dlaczego baza danych nie może zrozumieć jawnie określonego czasu? Bo strefa czasowa nie ma skrótu, tylko pełną nazwę, co ma sens tylko w kontekście daty, bo uwzględnia historię zmian stref czasowych, a bez daty się nie obejdzie. Już samo sformułowanie osi czasu rodzi pytania – co tak naprawdę programista miał na myśli? Dlatego wszystko jest tutaj logiczne, jeśli na to spojrzeć.

Co z nim nie tak?

Wyobraź sobie sytuację. Masz w tabeli pole z typem timestamptz. Chcesz to zaindeksować. Ale rozumiesz, że budowanie indeksu na tym polu nie zawsze jest uzasadnione ze względu na jego dużą selektywność (prawie wszystkie wartości tego typu będą unikalne). Decydujesz się więc zmniejszyć selektywność indeksu, rzutując typ na datę. I dostajesz niespodziankę:

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-состояние: 42P17

O co chodzi? Faktem jest, że do rzutowania typu timestamptz na typ daty wykorzystywana jest wartość parametru systemowego TimeZone, co uzależnia funkcję konwersji typu od niestandardowego parametru, tj. lotny. Takie funkcje nie są dozwolone w indeksie. W takim przypadku należy wyraźnie wskazać, w której strefie czasowej wykonywane jest rzutowanie typu.

Kiedy teraz, wcale nie jest nawet teraz

Przyzwyczailiśmy się, że now() zwraca bieżącą datę/godzinę, biorąc pod uwagę strefę czasową. Ale spójrz na następujące zapytania:

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;

Data/godzina zwracana jest taka sama, niezależnie od tego, ile czasu minęło od poprzedniego żądania! O co chodzi? Faktem jest, że now() nie jest czasem bieżącym, ale czasem rozpoczęcia bieżącej transakcji. Nie zmienia się zatem w ramach transakcji. Każde zapytanie uruchomione poza zakresem transakcji jest domyślnie owijane w transakcję, dlatego nie zauważamy, że czas zwrócony przez proste SELECT now(); właściwie nie aktualny... Jeśli chcesz uzyskać rzetelny aktualny czas, musisz skorzystać z funkcji clock_timestamp().

Plik numer pięć. fragment

Trochę dziwne

SELECT '111'::bit(4)

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

Po której stronie należy dodać bity w przypadku rozszerzenia typu? Wydaje się, że jest po lewej stronie. Ale tylko baza ma odmienne zdanie w tej sprawie. Bądź ostrożny: jeśli liczba cyfr nie będzie się zgadzać podczas rzutowania typu, nie otrzymasz tego, czego chciałeś. Dotyczy to zarówno dodawania bitów po prawej stronie, jak i przycinania bitów. Również po prawej stronie...

Akta numer sześć. Tablice

Nawet NULL nie zadziałał

SELECT ARRAY[1, 2] || NULL

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

Jako zwykli ludzie wychowani na SQL, spodziewamy się, że wynikiem tego wyrażenia będzie NULL. Ale tego tam nie było. Zwracana jest tablica. Dlaczego? Ponieważ w tym przypadku baza rzuca NULL na tablicę liczb całkowitych i niejawnie wywołuje funkcję array_cat. Jednak nadal nie jest jasne, dlaczego ten „kot tablicowy” nie resetuje tablicy. To zachowanie również należy po prostu zapamiętać.

Podsumować. Jest mnóstwo dziwnych rzeczy. Większość z nich oczywiście nie jest na tyle krytyczna, aby mówić o rażąco niewłaściwym zachowaniu. Inne tłumaczy się łatwością użycia lub częstotliwością ich zastosowania w określonych sytuacjach. Ale jednocześnie jest wiele niespodzianek. Dlatego warto o nich wiedzieć. Jeśli zauważysz coś jeszcze dziwnego lub niezwykłego w zachowaniu któregokolwiek typu, napisz w komentarzach, chętnie dodam do dostępnej na nich dokumentacji.

Źródło: www.habr.com

Podejrzane typy