Exception handling for CAST operation in plain SQL.

Prefer Video?

The Problem

SQL (and overall relational databases but not only) is strongly typed language. When you are defining your tables, you do define the type for each column, be it integer, varchar, boolean or any other.

That being said majority of current database engines have an option to do so called implicit casting which in plain english means, that it will try to match what you’ve provided with type data type in the output (column or query resolution).

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

In this simple example value ‘100’ is converted to INT (full syntax should be CAST(… AS INT) just used PostgreSQL :: operator for simplicity) and value 100 to text.

This is done without raising an issues.

You can read more about type conversions in your database engines documentation - PostgreSQL DOCS.

However, in some cases query execution with rightly fail with information that provided input is not valid for the type you want to achieve in the result.

    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

Now that’s neither good nor bad. Database has done what it should, it has checked if what you are trying to do is possible and gave you back an error saying that in this case it’s not going to work.

The problem start when you propagate this error further up the stack, like your backend server or frontend application. Should it be done that way or maybe you would want to have control over what DB is giving you back?

More philosophical discussion is at the end of this post, here let’s focus on option for solving it.

The Solution

If your code is using procedural SQL like PL/pgSQL in case of PostgreSQL you can catch that exception and do something with it.

    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 $$;

But here were are entering the field of procedural SQL which we would like to avoid.

Is there a way to do it in plain SQL?

There is but not without regrets … caugh … regex conditions.

    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;

Here in WITH clause (CTE) we first evaluate if value can be of type UUID and then in SELECT we try to cast it to it.

WARNING
If you will try to execute that as a single SQL without CTE (WITH) it will fail due to order of execution. Even though CASE WHEN will be evaluated to FALSE engine will try to parse THEN first as CAST operation is executed first.

Other Options

Are there any options to gently handly such cases?

Sure, check your database of choice if it has some out of the box functions to support type checking.

• MySQL for example has IS_UUID - DOCUMENTATION
• SQL Server has TRY_CONVERT - DOCUMENTATION

However, if you are not able to find anything you can always write your own function using a specific regex to not copy-paste it all over your code.

    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;
    $$;

Consider This First

Before you close the tab my Dear Reader, stop for a second. Take a deep breath and think, should I even use it … WHAT?

Exactly, that’s a valid question to answer. When you will try to think about it more you may start asking follow-up questions:

• Why am I (or more specific my database) even getting something that is not expected? Did something change in business logic?
• Should database handle such validations? If not, where it should be handled (backend service, frontend form)?
• Should such validation be part of SQL? Can it be part of column definition DDL (constraint)?
• What are the implications of me using this code? How can it be effectively maintained? What are the performance implications?

Maybe after answering some of the examples above you will get to the conclusion that this is not the right way in your case.

Thanks for your time.

Want to be up-to-date with new posts?
Use below form to join Data Craze Weekly Newsletter!