As the development of PostgreSQL moves on, more and more new features get committed. A recently commit patch added two new functions, which can be used to validate if a cast will work and what error message would be returned if the cast fails.

Before looking at the new functions in PostgreSQL 16, lets have a look at what happens in version before 16 if a cast fails:

postgres=# select 'aaaa'::int;
ERROR:  invalid input syntax for type integer: "aaaa"
LINE 1: select 'aaaa'::int;

Not really a surprise, this fails. The downside with this is, that it will also abort the transaction it is failing in:

postgres=# begin;
BEGIN
postgres=*# select 1;
 ?column? 
----------
        1
(1 row)

postgres=*# select 'aaaa'::int;
ERROR:  invalid input syntax for type integer: "aaaa"
LINE 1: select 'aaaa'::int;
               ^
postgres=!# select 1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=!# end;
ROLLBACK
postgres=#

With PostgreSQL 16 this can be done more convenient. The first new function is called pg_input_is_valid and can be used to test if a cast will succeed or fail:

postgres=# select pg_input_is_valid('aaa','text');
 pg_input_is_valid 
-------------------
 t
(1 row)

postgres=# select pg_input_is_valid('aaa','int');
 pg_input_is_valid 
-------------------
 f
(1 row)

This gives you more control if you are in a transaction, as you now can test before you actually execute the cast:

postgres=# begin;
BEGIN
postgres=*# select 1;
 ?column? 
----------
        1
(1 row)

postgres=*# select pg_input_is_valid('aaa','int');
 pg_input_is_valid 
-------------------
 f
(1 row)

postgres=*# select pg_input_is_valid('aaa','text');
 pg_input_is_valid 
-------------------
 t
(1 row)

postgres=*# end;
COMMIT

The second new function is called pg_input_error_message and will give you the error message you would get, if a cast fails:

postgres=# select pg_input_error_message('aaaa','int');
            pg_input_error_message             
-----------------------------------------------
 invalid input syntax for type integer: "aaaa"
(1 row)

postgres=# select pg_input_error_message('22.22','int');
             pg_input_error_message             
------------------------------------------------
 invalid input syntax for type integer: "22.22"
(1 row)

postgres=#

Nice.

Post Views: 2,339
Thumbnail [60x60]
by
Daniel Westermann