Title: On 9th of December 2022, Tom Lane committed patch:

Add test scaffolding for soft error reporting from input functions.
 
pg_input_is_valid() returns boolean, while pg_input_error_message()
returns the primary error message if the input is bad, or NULL
if the input is OK.  The main reason for having two functions is
so that we can test both the details-wanted and the no-details-wanted
code paths.
 
Although these are primarily designed with testing in mind,
it could well be that they'll be useful to end users as well.
 
This patch is mostly by me, but it owes very substantial debt to
earlier work by Nikita Glukhov, Andrew Dunstan, and Amul Sul.
Thanks to Andres Freund for review.
 
Discussion: https://postgr.es/m/3bbbb0df-7382-bf87-9737-340ba096e034@postgrespro.ru

So, this is actually pretty cool.

Lots of times people were asking about checking if casting would work, without raising error.

For example:

$ SELECT u::int4 FROM unnest('{a,123}'::text[]) AS u;
ERROR:  invalid INPUT syntax FOR TYPE INTEGER: "a"

We can validate, sure, with regexps, for example, but it isn't really nice solution.

Now, thanks to this commit we get very nice function pg_input_is_valid, we can easily:

$ SELECT u::int4 FROM unnest('{a,123}'::text[]) AS u
WHERE pg_input_is_valid(u, 'integer');
  u
─────
 123
(1 ROW)

In this commit we just got basic function for validation (and reporting). Additional work had to be done for each datatype so that it will allow this exception-less casting. And it's been done:

• for: bool, int2, int4, int8, float4, float8, numeric, and cube.
• for: date, time, timetz, timestamp, timestamptz, and interval.
• for: json and jsonb.
• for: domains.

So you can now validate all the values in these datatypes, and, if you really want to, get error message without the error:

$ SELECT pg_input_error_message('123.324', 'numeric(2,1)');
 pg_input_error_message
────────────────────────
 NUMERIC FIELD overflow
(1 ROW)
 
$ SELECT pg_input_error_message('', 'numeric(2,1)');
          pg_input_error_message
───────────────────────────────────────────
 invalid INPUT syntax FOR TYPE NUMERIC: ""
(1 ROW)

Pretty awesome. Thanks to all involved.