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

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(at)manngo(dot)net <mailto:mark(at)comparity(dot)net>
web:http://www.manngo.net

Resume:http://mark.manngo.net