Suggestion: optionally return default value instead of error on failed cast

currently a failed cast throws an error. It would be useful to have a
way to get a default value instead.

I've recently encountered situations where this would have been helpful.
Recently I came across some client code:

CREATE OR REPLACE FUNCTION is_valid_json(str text) RETURNS boolean LANGUAGE
PLPGSQL
AS $$
DECLARE
j json;
BEGIN
j := str::json;
return true;
EXCEPTION WHEN OTHERS THEN return false;
END
$$;

This is a double-bummer. First, the function discards the value so we have
to recompute it, and secondly, the exception block prevents the query from
being parallelized.

T-SQL has try_cast [1]

I'd be more in favor of this if we learn that there's no work (current or
proposed) in the SQL standard.

Oracle has CAST(... AS .. DEFAULT ... ON CONVERSION ERROR) [2]

If the SQL group has suggested anything, I'd bet it looks a lot like this.

The DEFAULT ... ON CONVERSION ERROR syntax seems like it could be
implemented in PostgreSQL. Even if only DEFAULT NULL was supported (at
first) that would already help.

The short syntax could be extended for the DEFAULT NULL case, too:

SELECT '...'::type -- throws error
SELECT '...':::type -- returns NULL

I think I'm against adding a ::: operator, because too many people are
going to type (or omit) the third : by accident, and that would be a really
subtle bug. The CAST/TRY_CAST syntax is wordy but it makes it very clear
that you expected janky input and have specified a contingency plan.

The TypeCast node seems like it wouldn't need too much modification to
allow for this. The big lift, from what I can tell, is either creating
versions of every $foo_in() function to return NULL instead of raising an
error, and then effectively wrapping that inside a coalesce() to process
the default. Alternatively, we could add an extra boolean parameter
("nullOnFailure"? "suppressErrors"?) to the existing $foo_in() functions, a
boolean to return null instead of raising an error, and the default would
be handled in coerce_to_target_type(). Either of those would create a fair
amount of work for extensions that add types, but I think the value would
be worth it.

I do remember when I proposed the "void"/"black hole"/"meh" datatype (all
values map to NULL) I ran into a fairly fundamental rule that types must
map any not-null input to a not-null output, and this could potentially
violate that, but I'm not sure.

Does anyone know if the SQL standard has anything to say on this subject?

On 1/4/22 22:17, Corey Huinker wrote:

currently a failed cast throws an error. It would be useful to have a
way to get a default value instead.

I've recently encountered situations where this would have been
helpful. Recently I came across some client code:

CREATE OR REPLACE FUNCTION is_valid_json(str text) RETURNS boolean
LANGUAGE PLPGSQL
AS $$
DECLARE
    j json;
BEGIN
    j := str::json;
    return true;
EXCEPTION WHEN OTHERS THEN return false;
END
$$;

This is a double-bummer. First, the function discards the value so we
have to recompute it, and secondly, the exception block prevents the
query from being parallelized.

This particular case is catered for in the SQL/JSON patches which
several people are currently reviewing:

andrew=# select 'foo' is json;
 ?column?
----------
 f
(1 row)

andrew=# select '"foo"' is json;
 ?column?
----------
 t
(1 row)

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Thu, Jan 6, 2022 at 12:18 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 1/4/22 22:17, Corey Huinker wrote:

currently a failed cast throws an error. It would be useful to have a
way to get a default value instead.

I've recently encountered situations where this would have been
helpful. Recently I came across some client code:

CREATE OR REPLACE FUNCTION is_valid_json(str text) RETURNS boolean
LANGUAGE PLPGSQL
AS $$
DECLARE
j json;
BEGIN
j := str::json;
return true;
EXCEPTION WHEN OTHERS THEN return false;
END
$$;

This is a double-bummer. First, the function discards the value so we
have to recompute it, and secondly, the exception block prevents the
query from being parallelized.

This particular case is catered for in the SQL/JSON patches which
several people are currently reviewing:

That's great to know, but it would still be parsing the json twice, once to
learn that it is legit json, and once to get the casted value.

Also, I had a similar issue with type numeric, so having generic "x is a
type_y" support would essentially do everything that a try_catch()-ish
construct would need to do, and be more generic.

PostgreSQL is the only popular DBMS (define popular?) which doesn’t have
a friendly alternative. I asked about it on Stack
(https://dba.stackexchange.com/questions/203934/postgresql-alternative-to-sql-server-s-try-cast-function/311980#311980),
and ended up with the following:

    DROP FUNCTION IF EXISTS cast_int;     CREATE FUNCTION
cast_int(string varchar, planB int default null) RETURNS INT AS $$   
     BEGIN             RETURN floor(cast(string as numeric));        
EXCEPTION             WHEN OTHERS THEN return planB;         END     $$
LANGUAGE plpgsql;

Obviously this is type-specific, but the point is that it’s not hard.

Best Regards,

Mark
On 12/12/2020 8:13 pm, Wolfgang Walther wrote:

Hi,

currently a failed cast throws an error. It would be useful to have a
way to get a default value instead.

T-SQL has try_cast [1]
Oracle has CAST(... AS .. DEFAULT ... ON CONVERSION ERROR) [2]

The DEFAULT ... ON CONVERSION ERROR syntax seems like it could be
implemented in PostgreSQL. Even if only DEFAULT NULL was supported (at
first) that would already help.

The short syntax could be extended for the DEFAULT NULL case, too:

SELECT '...'::type -- throws error
SELECT '...':::type -- returns NULL

I couldn't find any previous discussion on this, please advise in case
I just missed it.

Thoughts?

Best

Wolfgang

[1]:
https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql
[2]:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CAST.html

--

Mark Simon

Manngo Net Pty Ltd

mobile:0411 246 672

email:mark@manngo.net <mailto:mark@comparity.net>
web:http://www.manngo.net

Resume:http://mark.manngo.net