Obsługa wyjątków dla operacji CAST w SQL.

Problem

SQL (i ogólnie relacyjne bazy danych, ale nie tylko) jest językiem o silnym typowaniu. Kiedy definiujesz swoje tabele, definiujesz typ dla każdej kolumny, czy to INTEGER, VARCHAR, BOOLEAN czy inny.

Mówiąc to, większość obecnych silników baz danych ma opcję tzw. implicit casting, co w prostych słowach oznacza, że spróbuje dopasować to, co podałeś, do typu danych w wyjściu (kolumna lub rozwiązywanie zapytania).

    SELECT '100'::int, 100::text;

W tym prostym przykładzie wartość ‘100’ jest konwertowana na INT (pełna składnia powinna być CAST(… AS INT), użyłem operatora PostgreSQL :: dla wygody), a wartość 100 na TEXT.

Zostaje to zrobione bez żadnych problemów. Możesz przeczytać więcej o konwersjach typów w dokumentacji swojego silnika bazy danych. - Dokumentacja PostgreSQL.

Jednak w niektórych przypadkach wykonanie zapytania może zakończyć się niepowodzeniem z informacją, że podana wartość nie jest właściwa dla typu, który chcesz osiągnąć w wyniku.

    SELECT CAST('7492bd12-1fff-4d02-9355-da5678d2da' AS UUID) as id
         , 'test_ABC' as col_txt
         , 100 as col_int
      FROM public.casting_tst;
    
    -- [22P02] ERROR: invalid input syntax for type uuid: "7492bd12-1fff-4d02-9355-da5678d2da"

ERROR: invalid input syntax for type XYZ

Taka sytuacja jest ani dobra, ani zła. Baza danych zrobiła to, co powinna, sprawdziła, czy to, co próbujesz zrobić, jest możliwe i zwróciła błąd mówiący, że w tym przypadku to nie zadziała.

Problem zaczyna się, gdy przesyłasz ten błąd dalej w górę, na przykład do swojego serwera backendowego lub aplikacji frontendowej. Czy powinno to być zrobione w ten sposób, czy może chciałbyś mieć kontrolę nad tym, co baza danych ci zwraca?

Bardziej filozoficzna dyskusja znajduje się na końcu tego wpisu, tutaj skupmy się na opcjach rozwiązania tego problemu.

Rozwiązanie

Jeśli twój kod używa proceduralnego SQL, takiego jak PL/pgSQL w przypadku PostgreSQL, możesz przechwycić ten wyjątek i coś z nim zrobić.

    DO $$
    BEGIN
     INSERT INTO public.casting_tst(id, col_txt, col_int)
          SELECT CAST('7492bd12-1fff-4d02-9355-da5678d2da' AS UUID) as id
               , 'test_ABC' as col_txt
               , 100 as col_int
            FROM public.casting_tst;
    EXCEPTION
        WHEN invalid_text_representation THEN
            RAISE NOTICE 'caught invalid type conversion';
    END $$;

Ale tutaj wchodzimy w obszar proceduralnego SQL, którego chcielibyśmy uniknąć.

Czy sam SQL da rade?

Jest ale będziesz do tego potrzebować … wyrażeń regularnych.

    with validated_uuids as (
      select value,
             value ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' as is_valid
      from (values ('7492bd12-1fff-4d02-9355-da5678d2da'), -- not valid UUID
                   ('7492bd12-1fff-4d02-9355-da5678d2da46') -- valid UUID
      ) as t(value)
    )
    select case when is_valid then value::uuid end
      from validated_uuids;

Tutaj w klauzuli WITH (CTE) najpierw oceniamy, czy wartość może być typu UUID, a następnie w SELECT próbujemy ją rzutować na ten typ.

UWAGA
Jeśli spróbujesz wykonać to jako pojedyncze zapytanie SQL bez CTE (WITH), to zakończy się niepowodzeniem z powodu kolejności wykonania. Nawet jeśli CASE WHEN będzie FALSE, silnik spróbuje najpierw sparsować THEN, ponieważ operacja CAST jest wykonywana jako pierwsza.

Inne Opcje

Czy są jakieś opcje, aby obsługiwać takie przypadki w prostszy sposób?

Oczywiście, sprawdź swoją bazę danych, czy ma jakieś wbudowane funkcje wspierające sprawdzanie typów.

• MySQL posiada IS_UUID - DOKUMENTACJA
• SQL Server posiada TRY_CONVERT - DOKUMENTACJA

Jednak jeśli nie możesz nic znaleźć, zawsze możesz napisać własną funkcję używającą konkretnego wyrażenia regularnego, aby nie kopiować i wklejać tego wszędzie w swoim kodzie.

    CREATE OR REPLACE FUNCTION is_valid_uuid(text)
    RETURNS boolean
    LANGUAGE plpgsql
    IMMUTABLE
    PARALLEL SAFE
    AS $$
    BEGIN
        RETURN $1 ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';
    EXCEPTION
        WHEN OTHERS THEN
            RETURN false;
    END;
    $$;

Zacznij Tutaj

Zanim zamkniesz kartę, Drogi Czytelniku, zatrzymaj się na chwilę. Weź głęboki oddech i pomyśl, czy powinienem w ogóle tak robić … CO?

Dokładnie, to ważne pytanie. Kiedy spróbujesz o tym pomyśleć więcej, możesz zacząć zadawać sobie pytania dodatkowe:

• Dlaczego ja (a dokładniej moja baza danych) w ogóle otrzymuje coś, co nie jest oczekiwane? Czy coś zmieniło się w logice biznesowej?
• Czy baza danych powinna obsługiwać takie walidacje? Jeśli nie, gdzie powinny być obsługiwane (wyższe warstwy - backend, frontend)?
• Czy taka walidacja powinna być częścią SQL? Czy może być częścią definicji kolumny DDL (ograniczenie)?
• Jakie są konsekwencje używania tego kodu? Jak można go skutecznie utrzymywać? Jakie są implikacje wydajnościowe?

Może po odpowiedzeniu na niektóre z powyższych przykładów dojdziesz do wniosku, że to nie jest właściwy sposób w Twoim przypadku.

Dzięki za poświęcony czas.

Chcesz być na bieżąco z nowymi postami?
Skorzystaj z poniższego formularza, aby dołączyć do newslettera Data Craze Weekly!