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:m...@manngo.net <mailto:m...@comparity.net>
web:http://www.manngo.net
Resume:http://mark.manngo.net
