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:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
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.