public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Corey Huinker <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Amul Sul <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: Isaac Morland <[email protected]>
Cc: [email protected]
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
Date: Tue, 23 Jun 2026 10:50:25 +0800
Message-ID: <CACJufxGqn5fQCPkUeRC4FQLMSFZ9vBMqq65zXmsRnp5OAMOkaA@mail.gmail.com> (raw)
In-Reply-To: <CADkLM=eyXFXm=kd1gbJK8ajVhDNV3OvQLWpTFj=bYahvCK8k=A@mail.gmail.com>
References: <CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com>
<CADkLM=cmv_bmxBe8KmZd6rEgiqSdoDfHnJa63u7rdRuAsqOwDA@mail.gmail.com>
<CACJufxGbw9iNT8QVm4QD9cPFKnDnvDBQp7AGxkoqDa-JjzVXmg@mail.gmail.com>
<CACJufxFkLLuX1VJ-J3fppCr37PHtxkvwyd_e4zNd+VYK0v0gnQ@mail.gmail.com>
<[email protected]>
<CACJufxHx-UfprE6P4_ZB_cOYktHd4pLMNx=jWJFOGGGFj2YZWQ@mail.gmail.com>
<CACJufxH_1EtEBMb0JvxaM3Gmnt33HYrS37m5eYjJ_OfBkMVFJg@mail.gmail.com>
<CACJufxHGox47X4zNtVeNw7H=SZ7ATAAD5SEYpvxa2o5mPV0XCA@mail.gmail.com>
<[email protected]>
<CAAJ_b959dfJsorK+_vPdVa8F3QYPdOnb-HzJY8oQQR91N9Tq2A@mail.gmail.com>
<CACJufxFOQkmH9KeQNTD0qASbL3i03cLeoyVZdcZ8Fm0rk6eJ5Q@mail.gmail.com>
<CACJufxEUr33CoQMsZdEehNU7wj_QOiyk8VW=W+aB03PF2J_qKQ@mail.gmail.com>
<CACJufxGx_XDHq1btNj=ZrBjwd0skeWFJQLMGqSR-pHw-kuYRnQ@mail.gmail.com>
<CADkLM=cXsprF2GvcmqOG+ME_N2iyHNr_--xW-6ZaKd32yF_NzA@mail.gmail.com>
<[email protected]>
<CACJufxFpyVfbUfWWTA4osVSy7fhNC4H+LzxRLFS65Z+9na4thg@mail.gmail.com>
<CADkLM=eyXFXm=kd1gbJK8ajVhDNV3OvQLWpTFj=bYahvCK8k=A@mail.gmail.com>
Hi,
In subquery_planner -> preprocess_expression ->
eval_const_expressions, we attempt
to evaluate certain constant expressions in an error-safe manner by introducing
an ErrorSaveContext in eval_const_expressions_context. However, this may
introduce consistency issues, since not all functions are error-safe.
Consider the following two queries:
SELECT CAST(65536 AS int2 DEFAULT NULL ON CONVERSION ERROR);
SELECT CAST(65536/0 AS int2 DEFAULT NULL ON CONVERSION ERROR);
ERROR: division by zero
The first query returns NULL, while the second throws an error. Should we accept
this inconsistency, or make the first query error out too?
Similarly, should we reject uncastable CASTs, like `CAST(NULL::date AS int2)`?
More broadly, the patch involved significant effort (refactoring) in
parsing (transformTypeCast),
planning (eval_const_expressions) to ensure expressions do not fail.
These efforts cannot ensure that all operations are error-safe,
therefore there will be consistency issues.
----------------------------------------------------------------
[1] explains why SQL-language functions are inherently not error-safe.
This raises the question: how should we handle user-defined cast functions in
CAST(expr AS target_type DEFAULT defexpr ON CONVERSION ERROR)?
There are three options:
Option 1: Disallow them entirely.
Reject any cast expression backed by a user-defined function at parse time.
Option 2: Allow them with documented caveats.
Permit user-defined cast functions, but explicitly document the limitations:
- SQL-language functions are inherently incapable of error-safe execution.
- C-language functions must ensure that all their subroutines are also
error-safe.
- It is the user's responsibility to ensure the cast function is error-safe; if
it is not, errors will propagate as normal rather than being caught by the
DEFAULT clause.
Option 2 means that CAST(expr AS target_type DEFAULT defexpr ON
CONVERSION ERROR) does not check if the underlying cast function
actually supports error-safe operations.
Option 3: Disallow SQL-language functions, but allow C-language and
Internal-language functions.
Also document that you must ensure the C function is error-safe if you
want it to catch the error.
Currently, we are using Option 1.
Anyway, I rebased the patch and added comments in a few places that I
realized were necessary in hindsight.
[1]: https://postgr.es/m/CACJufxHM2e3DQmbRdDZvWyG3ZCLyOg6XFifvOz_TGy1tGw7NHw@mail.gmail.com
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v30-0001-error-safe-for-casting-text-to-other-types-per-pg_cast.patch (9.2K, 2-v30-0001-error-safe-for-casting-text-to-other-types-per-pg_cast.patch)
download | inline diff:
From 88967acc1618f79485532f2531c593e7116b8811 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 18 Jun 2026 20:09:01 +0800
Subject: [PATCH v30 1/2] error safe for casting text to other types per
pg_cast
select castsource::regtype, casttarget::regtype, castfunc,
castcontext,castmethod, pp.prosrc, pp.proname from pg_cast pc join pg_proc pp
on pp.oid = pc.castfunc and pc.castfunc > 0
and castsource::regtype = 'text'::regtype
order by castsource::regtype;
castsource | casttarget | castfunc | castcontext | castmethod | prosrc | proname
------------+------------+----------+-------------+------------+---------------+----------
text | regclass | 1079 | i | f | text_regclass | regclass
text | "char" | 944 | a | f | text_char | char
text | name | 407 | i | f | text_name | name
text | xml | 2896 | e | f | texttoxml | xml
(4 rows)
Already error safe: text_name, text_char, texttoxml.
Author: jian he <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Amul Sul <[email protected]>
Reviewed-by: Corey Huinker <[email protected]>
Discussion: https://postgr.es/m/CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com
Commitfest: https://commitfest.postgresql.org/patch/5941
---
src/backend/catalog/namespace.c | 46 ++++++++++++++++++++++++++-------
src/backend/utils/adt/regproc.c | 13 +++++++---
src/backend/utils/adt/varlena.c | 10 +++++--
src/include/catalog/namespace.h | 6 +++++
src/include/utils/varlena.h | 1 +
5 files changed, 62 insertions(+), 14 deletions(-)
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index 56b87d878e8..0f63c826e80 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -442,6 +442,16 @@ Oid
RangeVarGetRelidExtended(const RangeVar *relation, LOCKMODE lockmode,
uint32 flags,
RangeVarGetRelidCallback callback, void *callback_arg)
+{
+ return RangeVarGetRelidExtendedSafe(relation, lockmode, flags,
+ callback, callback_arg,
+ NULL);
+}
+
+Oid
+RangeVarGetRelidExtendedSafe(const RangeVar *relation, LOCKMODE lockmode, uint32 flags,
+ RangeVarGetRelidCallback callback, void *callback_arg,
+ Node *escontext)
{
uint64 inval_count;
Oid relId;
@@ -458,7 +468,7 @@ RangeVarGetRelidExtended(const RangeVar *relation, LOCKMODE lockmode,
if (relation->catalogname)
{
if (strcmp(relation->catalogname, get_database_name(MyDatabaseId)) != 0)
- ereport(ERROR,
+ ereturn(escontext, InvalidOid,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cross-database references are not implemented: \"%s.%s.%s\"",
relation->catalogname, relation->schemaname,
@@ -515,7 +525,7 @@ RangeVarGetRelidExtended(const RangeVar *relation, LOCKMODE lockmode,
* return InvalidOid.
*/
if (namespaceId != myTempNamespace)
- ereport(ERROR,
+ ereturn(escontext, InvalidOid,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("temporary tables cannot specify a schema name")));
}
@@ -595,14 +605,20 @@ RangeVarGetRelidExtended(const RangeVar *relation, LOCKMODE lockmode,
{
int elevel = (flags & RVR_SKIP_LOCKED) ? DEBUG1 : ERROR;
- if (relation->schemaname)
+ if (escontext == NULL)
ereport(elevel,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ relation->schemaname ?
errmsg("could not obtain lock on relation \"%s.%s\"",
- relation->schemaname, relation->relname)));
+ relation->schemaname, relation->relname) :
+ errmsg("could not obtain lock on relation \"%s\"",
+ relation->relname)));
else
- ereport(elevel,
+ ereturn(escontext, InvalidOid,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ relation->schemaname ?
+ errmsg("could not obtain lock on relation \"%s.%s\"",
+ relation->schemaname, relation->relname) :
errmsg("could not obtain lock on relation \"%s\"",
relation->relname)));
@@ -628,14 +644,20 @@ RangeVarGetRelidExtended(const RangeVar *relation, LOCKMODE lockmode,
{
int elevel = missing_ok ? DEBUG1 : ERROR;
- if (relation->schemaname)
+ if (escontext == NULL)
ereport(elevel,
(errcode(ERRCODE_UNDEFINED_TABLE),
+ relation->schemaname ?
errmsg("relation \"%s.%s\" does not exist",
- relation->schemaname, relation->relname)));
+ relation->schemaname, relation->relname) :
+ errmsg("relation \"%s\" does not exist",
+ relation->relname)));
else
- ereport(elevel,
+ ereturn(escontext, InvalidOid,
(errcode(ERRCODE_UNDEFINED_TABLE),
+ relation->schemaname ?
+ errmsg("relation \"%s.%s\" does not exist",
+ relation->schemaname, relation->relname) :
errmsg("relation \"%s\" does not exist",
relation->relname)));
}
@@ -3624,6 +3646,12 @@ get_namespace_oid(const char *nspname, bool missing_ok)
*/
RangeVar *
makeRangeVarFromNameList(const List *names)
+{
+ return makeRangeVarFromNameListSafe(names, NULL);
+}
+
+RangeVar *
+makeRangeVarFromNameListSafe(const List *names, Node *escontext)
{
RangeVar *rel = makeRangeVar(NULL, NULL, -1);
@@ -3642,7 +3670,7 @@ makeRangeVarFromNameList(const List *names)
rel->relname = strVal(lthird(names));
break;
default:
- ereport(ERROR,
+ ereturn(escontext, NULL,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("improper relation name (too many dotted names): %s",
NameListToString(names))));
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 64f293f4e98..ad7a4d52d28 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -1901,12 +1901,19 @@ text_regclass(PG_FUNCTION_ARGS)
text *relname = PG_GETARG_TEXT_PP(0);
Oid result;
RangeVar *rv;
+ List *namelist;
- rv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ namelist = textToQualifiedNameListSafe(relname, fcinfo->context);
+ if (SOFT_ERROR_OCCURRED(fcinfo->context))
+ PG_RETURN_NULL();
+
+ rv = makeRangeVarFromNameListSafe(namelist, fcinfo->context);
+ if (SOFT_ERROR_OCCURRED(fcinfo->context))
+ PG_RETURN_NULL();
/* We might not even have permissions on this relation; don't lock it. */
- result = RangeVarGetRelid(rv, NoLock, false);
-
+ result = RangeVarGetRelidExtendedSafe(rv, NoLock, 0, NULL, NULL,
+ fcinfo->context);
PG_RETURN_OID(result);
}
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 0c6d3ba4d22..c3263ad994c 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -2720,6 +2720,12 @@ name_text(PG_FUNCTION_ARGS)
*/
List *
textToQualifiedNameList(text *textval)
+{
+ return textToQualifiedNameListSafe(textval, NULL);
+}
+
+List *
+textToQualifiedNameListSafe(text *textval, Node *escontext)
{
char *rawname;
List *result = NIL;
@@ -2731,12 +2737,12 @@ textToQualifiedNameList(text *textval)
rawname = text_to_cstring(textval);
if (!SplitIdentifierString(rawname, '.', &namelist))
- ereport(ERROR,
+ ereturn(escontext, NIL,
(errcode(ERRCODE_INVALID_NAME),
errmsg("invalid name syntax")));
if (namelist == NIL)
- ereport(ERROR,
+ ereturn(escontext, NIL,
(errcode(ERRCODE_INVALID_NAME),
errmsg("invalid name syntax")));
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index 9453a3e4932..7f3141ba27d 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -103,6 +103,11 @@ extern Oid RangeVarGetRelidExtended(const RangeVar *relation,
LOCKMODE lockmode, uint32 flags,
RangeVarGetRelidCallback callback,
void *callback_arg);
+extern Oid RangeVarGetRelidExtendedSafe(const RangeVar *relation,
+ LOCKMODE lockmode, uint32 flags,
+ RangeVarGetRelidCallback callback,
+ void *callback_arg,
+ Node *escontext);
extern Oid RangeVarGetCreationNamespace(const RangeVar *newRelation);
extern Oid RangeVarGetAndCheckCreationNamespace(RangeVar *relation,
LOCKMODE lockmode,
@@ -168,6 +173,7 @@ extern Oid LookupCreationNamespace(const char *nspname);
extern void CheckSetNamespace(Oid oldNspOid, Oid nspOid);
extern Oid QualifiedNameGetCreationNamespace(const List *names, char **objname_p);
extern RangeVar *makeRangeVarFromNameList(const List *names);
+extern RangeVar *makeRangeVarFromNameListSafe(const List *names, Node *escontext);
extern char *NameListToString(const List *names);
extern char *NameListToQuotedString(const List *names);
diff --git a/src/include/utils/varlena.h b/src/include/utils/varlena.h
index fe8d8a58952..c4d4292eb1c 100644
--- a/src/include/utils/varlena.h
+++ b/src/include/utils/varlena.h
@@ -27,6 +27,7 @@ extern int varstr_levenshtein_less_equal(const char *source, int slen,
int ins_c, int del_c, int sub_c,
int max_d, bool trusted);
extern List *textToQualifiedNameList(text *textval);
+extern List *textToQualifiedNameListSafe(text *textval, Node *escontext);
extern char *scan_quoted_identifier(char **endp, char **nextp);
extern char *scan_identifier(char **endp, char **nextp, char separator,
bool downcase_unquoted);
--
2.34.1
[text/x-patch] v30-0002-CAST-expr-AS-newtype-DEFAULT-expr-ON-CONVERSION-ERROR.patch (147.4K, 3-v30-0002-CAST-expr-AS-newtype-DEFAULT-expr-ON-CONVERSION-ERROR.patch)
download | inline diff:
From 270d4e9533ef21324dded6136f7e27959d3a4c52 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 23 Jun 2026 10:41:20 +0800
Subject: [PATCH v30 2/2] CAST(expr AS newtype DEFAULT expr ON CONVERSION
ERROR)
Introduce SQL-standard safe type cast syntax:
CAST(expr AS newtype DEFAULT expr ON CONVERSION ERROR)
This allow users to specify a default fallback expression if a type conversion
fails, rather than raising an error.
With this patchset, almost all of the cast functions in pg_cast.castfunc are now
error-safe. CoerceViaIO and CoerceToDomain were already error-safe in the HEAD,
see [0], this patch extends error-safe behavior to ArrayCoerceExpr.
# Bumps catversion required
[0]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274b
Author: jian he <[email protected]>
Reviewed-by: Amul Sul <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Corey Huinker <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Discussion: https://postgr.es/m/CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com
Commitfest: https://commitfest.postgresql.org/patch/5941
---
contrib/citext/expected/citext.out | 5 +
contrib/citext/expected/citext_1.out | 5 +
contrib/citext/sql/citext.sql | 2 +
.../pg_stat_statements/expected/select.out | 24 +-
contrib/pg_stat_statements/sql/select.sql | 5 +
doc/src/sgml/syntax.sgml | 39 +
src/backend/executor/execExpr.c | 97 +-
src/backend/executor/execExprInterp.c | 35 +
src/backend/jit/llvm/llvmjit_expr.c | 56 +
src/backend/nodes/nodeFuncs.c | 53 +
src/backend/optimizer/prep/prepagg.c | 15 +
src/backend/optimizer/util/clauses.c | 101 +-
src/backend/parser/gram.y | 21 +
src/backend/parser/parse_agg.c | 9 +
src/backend/parser/parse_coerce.c | 142 +-
src/backend/parser/parse_expr.c | 370 ++++-
src/backend/parser/parse_func.c | 3 +
src/backend/parser/parse_target.c | 3 +-
src/backend/parser/parse_type.c | 22 +
src/backend/parser/parse_utilcmd.c | 2 +-
src/backend/utils/adt/arrayfuncs.c | 9 +
src/backend/utils/adt/ruleutils.c | 25 +
src/include/executor/execExpr.h | 7 +
src/include/executor/executor.h | 2 +
src/include/nodes/execnodes.h | 21 +
src/include/nodes/parsenodes.h | 1 +
src/include/nodes/primnodes.h | 34 +
src/include/optimizer/optimizer.h | 2 +-
src/include/parser/parse_coerce.h | 16 +-
src/include/parser/parse_node.h | 2 +
src/include/parser/parse_type.h | 2 +
src/test/regress/expected/cast.out | 1185 +++++++++++++++++
src/test/regress/expected/create_cast.out | 5 +
src/test/regress/expected/equivclass.out | 19 +
src/test/regress/expected/xml.out | 6 +
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/cast.sql | 453 +++++++
src/test/regress/sql/create_cast.sql | 1 +
src/test/regress/sql/equivclass.sql | 5 +
src/test/regress/sql/xml.sql | 2 +
src/tools/pgindent/typedefs.list | 2 +
41 files changed, 2715 insertions(+), 95 deletions(-)
create mode 100644 src/test/regress/expected/cast.out
create mode 100644 src/test/regress/sql/cast.sql
diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out
index 8c0bf54f0f3..69649c2c5e2 100644
--- a/contrib/citext/expected/citext.out
+++ b/contrib/citext/expected/citext.out
@@ -10,6 +10,11 @@ WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
--------+---------
(0 rows)
+SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSION ERROR); -- error
+ERROR: cannot cast type character to citext with DEFAULT expression in CAST ... ON CONVERSION ERROR
+LINE 1: SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSI...
+ ^
+DETAIL: Safe type casts for user-defined types are not yet supported.
-- Test the operators and indexing functions
-- Test = and <>.
SELECT 'a'::citext = 'a'::citext AS t;
diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out
index c5e5f180f2b..896939d35ce 100644
--- a/contrib/citext/expected/citext_1.out
+++ b/contrib/citext/expected/citext_1.out
@@ -10,6 +10,11 @@ WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
--------+---------
(0 rows)
+SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSION ERROR); -- error
+ERROR: cannot cast type character to citext with DEFAULT expression in CAST ... ON CONVERSION ERROR
+LINE 1: SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSI...
+ ^
+DETAIL: Safe type casts for user-defined types are not yet supported.
-- Test the operators and indexing functions
-- Test = and <>.
SELECT 'a'::citext = 'a'::citext AS t;
diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql
index aa1cf9abd5c..c820b0bd4d9 100644
--- a/contrib/citext/sql/citext.sql
+++ b/contrib/citext/sql/citext.sql
@@ -9,6 +9,8 @@ SELECT amname, opcname
FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
+SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSION ERROR); -- error
+
-- Test the operators and indexing functions
-- Test = and <>.
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
index a069119c790..84f10810f72 100644
--- a/contrib/pg_stat_statements/expected/select.out
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -73,6 +73,25 @@ SELECT 1 AS "int" OFFSET 2 FETCH FIRST 3 ROW ONLY;
-----
(0 rows)
+-- error safe type cast
+SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR);
+ int4
+------
+ 2
+(1 row)
+
+SELECT CAST('12' AS numeric DEFAULT 2 ON CONVERSION ERROR);
+ numeric
+---------
+ 12
+(1 row)
+
+SELECT CAST('12' AS numeric(10) DEFAULT 2 ON CONVERSION ERROR);
+ numeric
+---------
+ 12
+(1 row)
+
-- DISTINCT and ORDER BY patterns
-- Try some query permutations which once produced identical query IDs
SELECT DISTINCT 1 AS "int";
@@ -222,6 +241,9 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
2 | 2 | SELECT $1 AS "int" ORDER BY 1
1 | 2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i
1 | 1 | SELECT $1 || $2
+ 1 | 1 | SELECT CAST($1 AS int DEFAULT $2 ON CONVERSION ERROR)
+ 1 | 1 | SELECT CAST($1 AS numeric DEFAULT $2 ON CONVERSION ERROR)
+ 1 | 1 | SELECT CAST($1 AS numeric(10) DEFAULT $2 ON CONVERSION ERROR)
2 | 2 | SELECT DISTINCT $1 AS "int"
0 | 0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
@@ -230,7 +252,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
| | ) +
| | SELECT f FROM t ORDER BY f
1 | 1 | select $1::jsonb ? $2
-(17 rows)
+(20 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
index a10d618c034..4be97977ba6 100644
--- a/contrib/pg_stat_statements/sql/select.sql
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -25,6 +25,11 @@ SELECT 1 AS "int" LIMIT 3 OFFSET 3;
SELECT 1 AS "int" OFFSET 1 FETCH FIRST 2 ROW ONLY;
SELECT 1 AS "int" OFFSET 2 FETCH FIRST 3 ROW ONLY;
+-- error safe type cast
+SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR);
+SELECT CAST('12' AS numeric DEFAULT 2 ON CONVERSION ERROR);
+SELECT CAST('12' AS numeric(10) DEFAULT 2 ON CONVERSION ERROR);
+
-- DISTINCT and ORDER BY patterns
-- Try some query permutations which once produced identical query IDs
SELECT DISTINCT 1 AS "int";
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 67482996861..86ca9384962 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -2125,6 +2125,45 @@ CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable>
<xref linkend="sql-createcast"/>.
</para>
</note>
+
+ <sect3 id="sql-syntax-type-casts-safe">
+ <title>Safe Type Casts</title>
+
+ <para>
+ A type cast may occasionally fail at run time. To guard against such
+ failures, you can provide an <literal>ON CONVERSION ERROR</literal>
+ clause to control what happens when a cast fails. The available forms
+ are:
+<synopsis>
+CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> DEFAULT <replaceable>expression</replaceable> ON CONVERSION ERROR )
+CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> ERROR ON CONVERSION ERROR )
+</synopsis>
+ With <literal>DEFAULT <replaceable>expression</replaceable> ON CONVERSION ERROR</literal>,
+ the default <replaceable>expression</replaceable> is evaluated and
+ returned instead. The <literal>ERROR ON CONVERSION ERROR</literal>
+ form raises an error on failure, which is also the default behavior
+ when no <literal>ON CONVERSION ERROR</literal> clause is specified.
+ </para>
+
+ <para>
+ At present, this only supports built-in type casts listed in
+ <xref linkend="catalog-pg-cast"/>. User-defined type casts created
+ with <link linkend="sql-createcast">CREATE CAST</link> are not
+ supported.
+ </para>
+
+ <para>
+ Some examples:
+<screen>
+SELECT CAST('not-a-date' AS date DEFAULT NULL ON CONVERSION ERROR);
+<lineannotation>Result: </lineannotation><computeroutput>NULL</computeroutput>
+SELECT CAST('not-a-date' AS date DEFAULT '1970-01-01' ON CONVERSION ERROR);
+<lineannotation>Result: </lineannotation><computeroutput>1970-01-01</computeroutput>
+SELECT CAST(TEXT 'error' AS integer DEFAULT 3 ON CONVERSION ERROR);
+<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
+</screen>
+ </para>
+ </sect3>
</sect2>
<sect2 id="sql-syntax-collate-exprs">
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index cfea7e160c2..327cb54444c 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -99,6 +99,9 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
static void ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
Datum *resv, bool *resnull,
ExprEvalStep *scratch);
+static void ExecInitSafeTypeCastExpr(SafeTypeCastExpr *stcexpr, ExprState *state,
+ Datum *resv, bool *resnull,
+ ExprEvalStep *scratch);
static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
ErrorSaveContext *escontext, bool omit_quotes,
bool exists_coerce,
@@ -141,6 +144,26 @@ static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
*/
ExprState *
ExecInitExpr(Expr *node, PlanState *parent)
+{
+ return ExecInitExprWithContext(node, parent, NULL);
+}
+
+/*
+ * ExecInitExprWithContext: same as ExecInitExpr, but with an optional
+ * ErrorSaveContext for soft error handling.
+ *
+ * When 'escontext' is non-NULL, expression nodes that support soft errors
+ * (currently CoerceToDomain's NOT NULL and CHECK constraint steps) will use
+ * errsave() instead of ereport(), allowing the caller to detect and handle
+ * failures without a transaction abort.
+ *
+ * The escontext must be provided at initialization time (not after), because
+ * it is copied into per-step data during expression compilation.
+ *
+ * Not all expression node types support soft errors. If in doubt, pass NULL.
+ */
+ExprState *
+ExecInitExprWithContext(Expr *node, PlanState *parent, Node *escontext)
{
ExprState *state;
ExprEvalStep scratch = {0};
@@ -154,6 +177,7 @@ ExecInitExpr(Expr *node, PlanState *parent)
state->expr = node;
state->parent = parent;
state->ext_params = NULL;
+ state->escontext = (ErrorSaveContext *) escontext;
/* Insert setup steps as needed */
ExecCreateExprSetupSteps(state, (Node *) node);
@@ -763,6 +787,18 @@ ExecBuildUpdateProjection(List *targetList,
*/
ExprState *
ExecPrepareExpr(Expr *node, EState *estate)
+{
+ return ExecPrepareExprWithContext(node, estate, NULL);
+}
+
+/*
+ * ExecPrepareExprWithContext: same as ExecPrepareExpr, but with an optional
+ * ErrorSaveContext for soft error handling.
+ *
+ * See ExecInitExprWithContext for details on the escontext parameter.
+ */
+ExprState *
+ExecPrepareExprWithContext(Expr *node, EState *estate, Node *escontext)
{
ExprState *result;
MemoryContext oldcontext;
@@ -771,7 +807,7 @@ ExecPrepareExpr(Expr *node, EState *estate)
node = expression_planner(node);
- result = ExecInitExpr(node, NULL);
+ result = ExecInitExprWithContext(node, NULL, escontext);
MemoryContextSwitchTo(oldcontext);
@@ -1701,6 +1737,7 @@ ExecInitExprRec(Expr *node, ExprState *state,
elemstate->innermost_caseval = palloc_object(Datum);
elemstate->innermost_casenull = palloc_object(bool);
+ elemstate->escontext = state->escontext;
ExecInitExprRec(acoerce->elemexpr, elemstate,
&elemstate->resvalue, &elemstate->resnull);
@@ -2176,6 +2213,16 @@ ExecInitExprRec(Expr *node, ExprState *state,
break;
}
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node);
+
+ ExecInitSafeTypeCastExpr(stcexpr, state, resv, resnull,
+ &scratch);
+
+ break;
+ }
+
case T_CoalesceExpr:
{
CoalesceExpr *coalesce = (CoalesceExpr *) node;
@@ -2736,7 +2783,8 @@ ExecInitFunc(ExprEvalStep *scratch, Expr *node, List *args, Oid funcid,
/* Initialize function call parameter structure too */
InitFunctionCallInfoData(*fcinfo, flinfo,
- nargs, inputcollid, NULL, NULL);
+ nargs, inputcollid,
+ (Node *) state->escontext, NULL);
/* Keep extra copies of this info to save an indirection at runtime */
scratch->d.func.fn_addr = flinfo->fn_addr;
@@ -4737,6 +4785,51 @@ ExecBuildParamSetEqual(TupleDesc desc,
return state;
}
+/*
+ * Push steps to evaluate a SafeTypeCastExpr and its various subsidiary
+ * expressions.
+ */
+static void
+ExecInitSafeTypeCastExpr(SafeTypeCastExpr *stcexpr, ExprState *state,
+ Datum *resv, bool *resnull,
+ ExprEvalStep *scratch)
+{
+ /*
+ * If there's no cast expression (castexpr == NULL), only the DEFAULT
+ * expression needs to be evaluated; set it up and return early.
+ */
+ if (stcexpr->castexpr == NULL)
+ {
+ ExecInitExprRec(stcexpr->defexpr, state, resv, resnull);
+
+ return;
+ }
+ else
+ {
+ SafeTypeCastState *stcstate = palloc0_object(SafeTypeCastState);
+ ErrorSaveContext *saved_escontext = state->escontext;
+
+ stcstate->stcexpr = stcexpr;
+ stcstate->escontext.type = T_ErrorSaveContext;
+ stcstate->escontext.error_occurred = false;
+ stcstate->escontext.details_wanted = false;
+ stcstate->escontext.error_data = NULL;
+ state->escontext = &stcstate->escontext;
+
+ /* evaluate argument expression into step's result area */
+ ExecInitExprRec(stcexpr->castexpr, state, resv, resnull);
+ scratch->opcode = EEOP_SAFETYPE_CAST;
+ scratch->d.stcexpr.stcstate = stcstate;
+ ExprEvalPushStep(state, scratch);
+
+ /* evaluate DEFAULT expression using the prior state->escontext */
+ state->escontext = saved_escontext;
+ ExecInitExprRec(stcstate->stcexpr->defexpr, state, resv, resnull);
+
+ stcstate->jump_end = state->steps_len;
+ }
+}
+
/*
* Push steps to evaluate a JsonExpr and its various subsidiary expressions.
*/
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 0634af964a9..ed231d737c9 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -578,6 +578,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_XMLEXPR,
&&CASE_EEOP_JSON_CONSTRUCTOR,
&&CASE_EEOP_IS_JSON,
+ &&CASE_EEOP_SAFETYPE_CAST,
&&CASE_EEOP_JSONEXPR_PATH,
&&CASE_EEOP_JSONEXPR_COERCION,
&&CASE_EEOP_JSONEXPR_COERCION_FINISH,
@@ -1936,6 +1937,28 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_SAFETYPE_CAST)
+ {
+ SafeTypeCastState *stcstate = op->d.stcexpr.stcstate;
+
+ if (!SOFT_ERROR_OCCURRED(&stcstate->escontext))
+ EEO_JUMP(stcstate->jump_end);
+ else
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+
+ /*
+ * Type cast error occurred. Reset the ErrorSaveContext so
+ * it's ready for the next coercion evaluation attempt.
+ */
+ stcstate->escontext.error_occurred = false;
+ stcstate->escontext.details_wanted = false;
+
+ EEO_NEXT();
+ }
+ }
+
EEO_CASE(EEOP_JSONEXPR_PATH)
{
/* too complex for an inline implementation */
@@ -3654,6 +3677,18 @@ ExecEvalArrayCoerce(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
econtext,
op->d.arraycoerce.resultelemtype,
op->d.arraycoerce.amstate);
+
+ if (SOFT_ERROR_OCCURRED(op->d.arraycoerce.elemexprstate->escontext))
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+
+ /*
+ * A soft error occurred. The caller may need to reset
+ * ExprState.ErrorSaveContext.error_occurred for the next evaluation.
+ * Currently, EEOP_SAFETYPE_CAST will do that.
+ */
+ }
}
/*
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 0e160b8502c..af93dd084ab 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2258,6 +2258,62 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+ case EEOP_SAFETYPE_CAST:
+ {
+ SafeTypeCastState *stcstate = op->d.stcexpr.stcstate;
+ LLVMBasicBlockRef b_noerror;
+ LLVMBasicBlockRef b_error;
+ LLVMValueRef v_error_occurred_p;
+ LLVMValueRef v_details_wanted_p;
+ LLVMValueRef v_error_occurred;
+
+ b_noerror = l_bb_before_v(opblocks[opno + 1],
+ "op.%d.noerror", opno);
+ b_error = l_bb_before_v(opblocks[opno + 1],
+ "op.%d.error", opno);
+
+ /* Get pointer to error_occurred field */
+ v_error_occurred_p = l_ptr_const(&stcstate->escontext.error_occurred,
+ l_ptr(TypeStorageBool));
+
+ /* Load error_occurred at runtime */
+ v_error_occurred = l_load(b, TypeStorageBool, v_error_occurred_p, "");
+
+ /* Branch based on error_occurred: no error -> jump_end */
+ LLVMBuildCondBr(b,
+ LLVMBuildICmp(b, LLVMIntEQ, v_error_occurred,
+ l_sbool_const(0), ""),
+ b_noerror,
+ b_error);
+
+ /* No error: jump to end */
+ LLVMPositionBuilderAtEnd(b, b_noerror);
+ LLVMBuildBr(b, opblocks[stcstate->jump_end]);
+
+ /* Error occurred: set null, reset flags, evaluate default */
+ LLVMPositionBuilderAtEnd(b, b_error);
+
+ /* set resnull to true */
+ LLVMBuildStore(b, l_sbool_const(1), v_resnullp);
+
+ /* reset resvalue */
+ LLVMBuildStore(b, l_datum_const(0), v_resvaluep);
+
+ /*
+ * Reset for next use such as for catching errors when
+ * coercing a expression.
+ */
+ LLVMBuildStore(b, l_sbool_const(0), v_error_occurred_p);
+
+ v_details_wanted_p = l_ptr_const(&stcstate->escontext.details_wanted,
+ l_ptr(TypeStorageBool));
+ LLVMBuildStore(b, l_sbool_const(0), v_details_wanted_p);
+
+ LLVMBuildBr(b, opblocks[opno + 1]);
+
+ break;
+ }
+
case EEOP_JSONEXPR_PATH:
{
JsonExprState *jsestate = op->d.jsonexpr.jsestate;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 2a2e00b372e..d46ef1a87e4 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -206,6 +206,9 @@ exprType(const Node *expr)
case T_RowCompareExpr:
type = BOOLOID;
break;
+ case T_SafeTypeCastExpr:
+ type = ((const SafeTypeCastExpr *) expr)->resulttype;
+ break;
case T_CoalesceExpr:
type = ((const CoalesceExpr *) expr)->coalescetype;
break;
@@ -453,6 +456,8 @@ exprTypmod(const Node *expr)
return typmod;
}
break;
+ case T_SafeTypeCastExpr:
+ return ((const SafeTypeCastExpr *) expr)->resulttypmod;
case T_CoalesceExpr:
{
/*
@@ -970,6 +975,9 @@ exprCollation(const Node *expr)
/* RowCompareExpr's result is boolean ... */
coll = InvalidOid; /* ... so it has no collation */
break;
+ case T_SafeTypeCastExpr:
+ coll = ((const SafeTypeCastExpr *) expr)->resultcollid;
+ break;
case T_CoalesceExpr:
coll = ((const CoalesceExpr *) expr)->coalescecollid;
break;
@@ -1248,6 +1256,9 @@ exprSetCollation(Node *expr, Oid collation)
/* RowCompareExpr's result is boolean ... */
Assert(!OidIsValid(collation)); /* ... so never set a collation */
break;
+ case T_SafeTypeCastExpr:
+ ((SafeTypeCastExpr *) expr)->resultcollid = collation;
+ break;
case T_CoalesceExpr:
((CoalesceExpr *) expr)->coalescecollid = collation;
break;
@@ -1569,6 +1580,9 @@ exprLocation(const Node *expr)
/* just use leftmost argument's location */
loc = exprLocation((Node *) ((const RowCompareExpr *) expr)->largs);
break;
+ case T_SafeTypeCastExpr:
+ loc = ((const SafeTypeCastExpr *) expr)->location;
+ break;
case T_CoalesceExpr:
/* COALESCE keyword should always be the first thing */
loc = ((const CoalesceExpr *) expr)->location;
@@ -2345,6 +2359,18 @@ expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node);
+
+ if (WALK(stcexpr->source))
+ return true;
+ if (WALK(stcexpr->castexpr))
+ return true;
+ if (WALK(stcexpr->defexpr))
+ return true;
+ }
+ break;
case T_CoalesceExpr:
return WALK(((CoalesceExpr *) node)->args);
case T_MinMaxExpr:
@@ -3404,6 +3430,19 @@ expression_tree_mutator_impl(Node *node,
return (Node *) newnode;
}
break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node);
+ SafeTypeCastExpr *newnode;
+
+ FLATCOPY(newnode, stcexpr, SafeTypeCastExpr);
+ MUTATE(newnode->source, stcexpr->source, Expr *);
+ MUTATE(newnode->castexpr, stcexpr->castexpr, Expr *);
+ MUTATE(newnode->defexpr, stcexpr->defexpr, Expr *);
+
+ return (Node *) newnode;
+ }
+ break;
case T_CoalesceExpr:
{
CoalesceExpr *coalesceexpr = (CoalesceExpr *) node;
@@ -4581,6 +4620,20 @@ raw_expression_tree_walker_impl(Node *node,
return true;
if (WALK(tc->typeName))
return true;
+ if (WALK(tc->defexpr))
+ return true;
+ }
+ break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node);
+
+ if (WALK(stcexpr->source))
+ return true;
+ if (WALK(stcexpr->castexpr))
+ return true;
+ if (WALK(stcexpr->defexpr))
+ return true;
}
break;
case T_CollateClause:
diff --git a/src/backend/optimizer/prep/prepagg.c b/src/backend/optimizer/prep/prepagg.c
index 3737cc15ba1..beec75112db 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -358,6 +358,21 @@ preprocess_aggrefs_walker(Node *node, PlannerInfo *root)
*/
return false;
}
+ if (IsA(node, SafeTypeCastExpr))
+ {
+ SafeTypeCastExpr *castexpr = (SafeTypeCastExpr *) node;
+
+ /*
+ * SafeTypeCastExpr->source may also contain an Aggref node, but since
+ * it is kept only for deparsing purposes, we must not recurse into it
+ * when processing aggregate nodes here.
+ *
+ * We already prevent defexpr from containing aggregate functions.
+ */
+ return expression_tree_walker((Node *) castexpr->castexpr,
+ preprocess_aggrefs_walker,
+ root);
+ }
Assert(!IsA(node, SubLink));
return expression_tree_walker(node, preprocess_aggrefs_walker, root);
}
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 01997e22266..390205db2ea 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -71,6 +71,7 @@ typedef struct
List *active_fns;
Node *case_val;
bool estimate;
+ ErrorSaveContext *escontext;
} eval_const_expressions_context;
typedef struct
@@ -2494,6 +2495,10 @@ rowtype_field_matches(Oid rowtypeid, int fieldnum,
* NOTE: another critical effect is that any function calls that require
* default arguments will be expanded, and named-argument calls will be
* converted to positional notation. The executor won't handle either.
+ *
+ * NOTE: If eval_const_expressions_context->escontext is not NULL, the
+ * expression is evaluated in an error-safe manner. In case of failure, it
+ * may return NULL instead of throwing an error.
*--------------------
*/
Node *
@@ -2509,6 +2514,7 @@ eval_const_expressions(PlannerInfo *root, Node *node)
context.active_fns = NIL; /* nothing being recursively simplified */
context.case_val = NULL; /* no CASE being examined */
context.estimate = false; /* safe transformations only */
+ context.escontext = NULL; /* for error-safe expression evaluation */
return eval_const_expressions_mutator(node, &context);
}
@@ -2651,6 +2657,7 @@ estimate_expression_value(PlannerInfo *root, Node *node)
context.active_fns = NIL; /* nothing being recursively simplified */
context.case_val = NULL; /* no CASE being examined */
context.estimate = true; /* unsafe transformations OK */
+ context.escontext = NULL; /* for error-safe expression evaluation */
return eval_const_expressions_mutator(node, &context);
}
@@ -2676,11 +2683,12 @@ estimate_expression_value(PlannerInfo *root, Node *node)
(!expression_tree_walker((Node *) (node), contain_non_const_walker, NULL))
/* Generic macro for applying evaluate_expr */
-#define ece_evaluate_expr(node) \
+#define ece_evaluate_expr(node, escontext) \
((Node *) evaluate_expr((Expr *) (node), \
exprType((Node *) (node)), \
exprTypmod((Node *) (node)), \
- exprCollation((Node *) (node))))
+ exprCollation((Node *) (node)), \
+ (Node *) escontext))
/*
* Recursive guts of eval_const_expressions/estimate_expression_value
@@ -3141,7 +3149,7 @@ eval_const_expressions_mutator(Node *node,
if (!has_nonconst_input &&
ece_function_is_safe(expr->opfuncid, context))
- return ece_evaluate_expr(expr);
+ return ece_evaluate_expr(expr, context->escontext);
return (Node *) expr;
}
@@ -3161,7 +3169,7 @@ eval_const_expressions_mutator(Node *node,
*/
if (ece_all_arguments_const(saop) &&
ece_function_is_safe(saop->opfuncid, context))
- return ece_evaluate_expr(saop);
+ return ece_evaluate_expr(saop, context->escontext);
return (Node *) saop;
}
case T_BoolExpr:
@@ -3285,6 +3293,42 @@ eval_const_expressions_mutator(Node *node,
context);
}
break;
+
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stc = castNode(SafeTypeCastExpr, node);
+ Node *castexpr = (Node *) stc->castexpr;
+ Node *defexpr = (Node *) stc->defexpr;
+ SafeTypeCastExpr *newexpr = makeNode(SafeTypeCastExpr);
+
+ context->escontext = makeNode(ErrorSaveContext);
+ context->escontext->type = T_ErrorSaveContext;
+ context->escontext->error_occurred = false;
+
+ castexpr = eval_const_expressions_mutator(castexpr,
+ context);
+ context->escontext = NULL;
+
+ defexpr = eval_const_expressions_mutator(defexpr,
+ context);
+
+ /*
+ * No need to fold "source" to a constant. The executor does
+ * not use it, see ExecInitSafeTypeCastExpr. Additionally,
+ * castexpr expression tree may already contain the "source"
+ * node.
+ */
+ newexpr->source = stc->source;
+ newexpr->castexpr = (Expr *) castexpr;
+ newexpr->defexpr = (Expr *) defexpr;
+ newexpr->resulttype = stc->resulttype;
+ newexpr->resulttypmod = stc->resulttypmod;
+ newexpr->resultcollid = stc->resultcollid;
+ newexpr->location = stc->location;
+
+ return (Node *) newexpr;
+ }
+
case T_SubPlan:
case T_AlternativeSubPlan:
@@ -3402,6 +3446,7 @@ eval_const_expressions_mutator(Node *node,
{
ArrayCoerceExpr *ac = makeNode(ArrayCoerceExpr);
Node *save_case_val;
+ Expr *simple = NULL;
/*
* Copy the node and const-simplify its arguments. We can't
@@ -3409,9 +3454,10 @@ eval_const_expressions_mutator(Node *node,
* with case_val only while processing the elemexpr.
*/
memcpy(ac, node, sizeof(ArrayCoerceExpr));
- ac->arg = (Expr *)
- eval_const_expressions_mutator((Node *) ac->arg,
- context);
+ simple = (Expr *) eval_const_expressions_mutator((Node *) ac->arg,
+ context);
+ if (simple)
+ ac->arg = simple;
/*
* Set up for the CaseTestExpr node contained in the elemexpr.
@@ -3420,9 +3466,10 @@ eval_const_expressions_mutator(Node *node,
save_case_val = context->case_val;
context->case_val = NULL;
- ac->elemexpr = (Expr *)
- eval_const_expressions_mutator((Node *) ac->elemexpr,
- context);
+ simple = (Expr *) eval_const_expressions_mutator((Node *) ac->elemexpr,
+ context);
+ if (simple)
+ ac->elemexpr = simple;
context->case_val = save_case_val;
@@ -3437,7 +3484,12 @@ eval_const_expressions_mutator(Node *node,
if (ac->arg && IsA(ac->arg, Const) &&
ac->elemexpr && !IsA(ac->elemexpr, CoerceToDomain) &&
!contain_mutable_functions((Node *) ac->elemexpr))
- return ece_evaluate_expr(ac);
+ {
+ simple = (Expr *) ece_evaluate_expr(ac, context->escontext);
+
+ if (simple)
+ return (Node *) simple;
+ }
return (Node *) ac;
}
@@ -3633,7 +3685,7 @@ eval_const_expressions_mutator(Node *node,
node = ece_generic_processing(node);
/* If all arguments are Consts, we can fold to a constant */
if (ece_all_arguments_const(node))
- return ece_evaluate_expr(node);
+ return ece_evaluate_expr(node, context->escontext);
return node;
}
case T_CoalesceExpr:
@@ -3716,7 +3768,8 @@ eval_const_expressions_mutator(Node *node,
return (Node *) evaluate_expr((Expr *) svf,
svf->type,
svf->typmod,
- InvalidOid);
+ InvalidOid,
+ NULL);
else
return copyObject((Node *) svf);
}
@@ -3813,7 +3866,7 @@ eval_const_expressions_mutator(Node *node,
newfselect->resulttype,
newfselect->resulttypmod,
newfselect->resultcollid))
- return ece_evaluate_expr(newfselect);
+ return ece_evaluate_expr(newfselect, context->escontext);
}
return (Node *) newfselect;
}
@@ -4143,7 +4196,7 @@ eval_const_expressions_mutator(Node *node,
newcre->arg = (Expr *) arg;
if (arg != NULL && IsA(arg, Const))
- return ece_evaluate_expr((Node *) newcre);
+ return ece_evaluate_expr((Node *) newcre, context->escontext);
return (Node *) newcre;
}
default:
@@ -4543,6 +4596,8 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
args, funcvariadic,
func_tuple, context);
+ Assert(!(SOFT_ERROR_OCCURRED(context->escontext) && newexpr != NULL));
+
if (!newexpr && allow_non_const && OidIsValid(func_form->prosupport))
{
/*
@@ -5268,7 +5323,7 @@ evaluate_function(Oid funcid, Oid result_type, int32 result_typmod,
newexpr->location = -1;
return evaluate_expr((Expr *) newexpr, result_type, result_typmod,
- result_collid);
+ result_collid, (Node *) context->escontext);
}
/*
@@ -5722,10 +5777,13 @@ sql_inline_error_callback(void *arg)
*
* We use the executor's routine ExecEvalExpr() to avoid duplication of
* code and ensure we get the same result as the executor would get.
+ *
+ * When escontext is non-NULL, safely evaluates the constant expression.
+ * Returns NULL on failure rather than throwing an error.
*/
Expr *
evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
- Oid result_collation)
+ Oid result_collation, Node *escontext)
{
EState *estate;
ExprState *exprstate;
@@ -5750,7 +5808,7 @@ evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
* Prepare expr for execution. (Note: we can't use ExecPrepareExpr
* because it'd result in recursively invoking eval_const_expressions.)
*/
- exprstate = ExecInitExpr(expr, NULL);
+ exprstate = ExecInitExprWithContext(expr, NULL, escontext);
/*
* And evaluate it.
@@ -5770,6 +5828,13 @@ evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
/* Get back to outer memory context */
MemoryContextSwitchTo(oldcontext);
+ if (SOFT_ERROR_OCCURRED(exprstate->escontext))
+ {
+ FreeExecutorState(estate);
+
+ return NULL;
+ }
+
/*
* Must copy result out of sub-context used by expression eval.
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..47f81ced4c6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -158,6 +158,8 @@ static void updateRawStmtEnd(RawStmt *rs, int end_location);
static Node *makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner);
static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
+static Node *makeTypeCastWithDefault(Node *arg, TypeName *typename,
+ Node *raw_default, int location);
static Node *makeStringConstCast(char *str, int location, TypeName *typename);
static Node *makeIntConst(int val, int location);
static Node *makeFloatConst(char *str, int location);
@@ -16787,6 +16789,10 @@ func_expr_common_subexpr:
}
| CAST '(' a_expr AS Typename ')'
{ $$ = makeTypeCast($3, $5, @1); }
+ | CAST '(' a_expr AS Typename ERROR_P ON CONVERSION_P ERROR_P ')'
+ { $$ = makeTypeCast($3, $5, @1); }
+ | CAST '(' a_expr AS Typename DEFAULT a_expr ON CONVERSION_P ERROR_P ')'
+ { $$ = makeTypeCastWithDefault($3, $5, $7, @1); }
| EXTRACT '(' extract_list ')'
{
$$ = (Node *) makeFuncCall(SystemFuncName("extract"),
@@ -19950,10 +19956,25 @@ makeTypeCast(Node *arg, TypeName *typename, int location)
n->arg = arg;
n->typeName = typename;
+ n->defexpr = NULL;
n->location = location;
return (Node *) n;
}
+static Node *
+makeTypeCastWithDefault(Node *arg, TypeName *typename, Node *defexpr,
+ int location)
+{
+ TypeCast *n = makeNode(TypeCast);
+
+ n->arg = arg;
+ n->typeName = typename;
+ n->defexpr = defexpr;
+ n->location = location;
+
+ return (Node *) n;
+}
+
static Node *
makeStringConstCast(char *str, int location, TypeName *typename)
{
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index acb933392de..7c0d490bff2 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -491,6 +491,12 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
err = _("grouping operations are not allowed in check constraints");
break;
+ case EXPR_KIND_CAST_DEFAULT:
+ if (isAgg)
+ err = _("aggregate functions are not allowed in CAST DEFAULT expressions");
+ else
+ err = _("grouping operations are not allowed in CAST DEFAULT expressions");
+ break;
case EXPR_KIND_COLUMN_DEFAULT:
case EXPR_KIND_FUNCTION_DEFAULT:
@@ -999,6 +1005,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_DOMAIN_CHECK:
err = _("window functions are not allowed in check constraints");
break;
+ case EXPR_KIND_CAST_DEFAULT:
+ err = _("window functions are not allowed in CAST DEFAULT expressions");
+ break;
case EXPR_KIND_COLUMN_DEFAULT:
case EXPR_KIND_FUNCTION_DEFAULT:
err = _("window functions are not allowed in DEFAULT expressions");
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 3b92ae2a920..adef374c764 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -37,14 +37,16 @@ static Node *coerce_type_typmod(Node *node,
Oid targetTypeId, int32 targetTypMod,
CoercionContext ccontext, CoercionForm cformat,
int location,
- bool hideInputCoercion);
+ bool hideInputCoercion,
+ Node *escontext);
static void hide_coercion_node(Node *node);
static Node *build_coercion_expression(Node *node,
CoercionPathType pathtype,
Oid funcId,
Oid targetTypeId, int32 targetTypMod,
CoercionContext ccontext, CoercionForm cformat,
- int location);
+ int location,
+ Node *escontext);
static Node *coerce_record_to_complex(ParseState *pstate, Node *node,
Oid targetTypeId,
CoercionContext ccontext,
@@ -81,6 +83,28 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype,
CoercionContext ccontext,
CoercionForm cformat,
int location)
+{
+ return coerce_to_target_type_extended(pstate, expr, exprtype,
+ targettype, targettypmod,
+ ccontext,
+ cformat,
+ location,
+ NULL);
+}
+
+/*
+ * escontext: If non-NULL, safely coerces 'expr' to the target type
+ * without raising an error. Returns NULL if the coercion fails.
+ *
+ * See 'coerce_to_target_type' above for details on other parameters.
+ */
+Node *
+coerce_to_target_type_extended(ParseState *pstate, Node *expr, Oid exprtype,
+ Oid targettype, int32 targettypmod,
+ CoercionContext ccontext,
+ CoercionForm cformat,
+ int location,
+ Node *escontext)
{
Node *result;
Node *origexpr;
@@ -102,9 +126,15 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype,
while (expr && IsA(expr, CollateExpr))
expr = (Node *) ((CollateExpr *) expr)->arg;
- result = coerce_type(pstate, expr, exprtype,
- targettype, targettypmod,
- ccontext, cformat, location);
+ result = coerce_type_extended(pstate, expr, exprtype,
+ targettype, targettypmod,
+ ccontext,
+ cformat,
+ location,
+ escontext);
+
+ if (SOFT_ERROR_OCCURRED(escontext))
+ return NULL;
/*
* If the target is a fixed-length type, it may need a length coercion as
@@ -114,7 +144,11 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype,
result = coerce_type_typmod(result,
targettype, targettypmod,
ccontext, cformat, location,
- (result != expr && !IsA(result, Const)));
+ (result != expr && !IsA(result, Const)),
+ escontext);
+
+ if (result == NULL) /* shouldn't happen */
+ elog(ERROR, "failed to coerce type modifier as expected");
if (expr != origexpr && type_is_collatable(targettype))
{
@@ -158,6 +192,18 @@ Node *
coerce_type(ParseState *pstate, Node *node,
Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
CoercionContext ccontext, CoercionForm cformat, int location)
+{
+ return coerce_type_extended(pstate, node,
+ inputTypeId, targetTypeId, targetTypeMod,
+ ccontext, cformat, location,
+ NULL);
+}
+
+Node *
+coerce_type_extended(ParseState *pstate, Node *node,
+ Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
+ CoercionContext ccontext, CoercionForm cformat, int location,
+ Node *escontext)
{
Node *result;
CoercionPathType pathtype;
@@ -256,6 +302,7 @@ coerce_type(ParseState *pstate, Node *node,
int32 inputTypeMod;
Type baseType;
ParseCallbackState pcbstate;
+ char *string = NULL;
/*
* If the target type is a domain, we want to call its base type's
@@ -309,13 +356,21 @@ coerce_type(ParseState *pstate, Node *node,
* as CSTRING.
*/
if (!con->constisnull)
- newcon->constvalue = stringTypeDatum(baseType,
- DatumGetCString(con->constvalue),
- inputTypeMod);
- else
- newcon->constvalue = stringTypeDatum(baseType,
- NULL,
- inputTypeMod);
+ string = DatumGetCString(con->constvalue);
+
+ if (!stringTypeDatumSafe(baseType,
+ string,
+ inputTypeMod,
+ escontext,
+ &newcon->constvalue))
+ {
+ /* UNKNOWN Const cannot coerce to targetType, exit now */
+ cancel_parser_errposition_callback(&pcbstate);
+
+ ReleaseSysCache(baseType);
+
+ return NULL;
+ }
/*
* If it's a varlena value, force it to be in non-expanded
@@ -364,7 +419,8 @@ coerce_type(ParseState *pstate, Node *node,
baseTypeId, baseTypeMod,
targetTypeId,
ccontext, cformat, location,
- false);
+ false,
+ escontext);
ReleaseSysCache(baseType);
@@ -397,9 +453,11 @@ coerce_type(ParseState *pstate, Node *node,
*/
CollateExpr *coll = (CollateExpr *) node;
- result = coerce_type(pstate, (Node *) coll->arg,
- inputTypeId, targetTypeId, targetTypeMod,
- ccontext, cformat, location);
+ result = coerce_type_extended(pstate, (Node *) coll->arg,
+ inputTypeId, targetTypeId, targetTypeMod,
+ ccontext, cformat, location,
+ escontext);
+
if (type_is_collatable(targetTypeId))
{
CollateExpr *newcoll = makeNode(CollateExpr);
@@ -432,7 +490,8 @@ coerce_type(ParseState *pstate, Node *node,
*/
result = build_coercion_expression(node, pathtype, funcId,
baseTypeId, baseTypeMod,
- ccontext, cformat, location);
+ ccontext, cformat, location,
+ escontext);
/*
* If domain, coerce to the domain type and relabel with domain
@@ -442,7 +501,8 @@ coerce_type(ParseState *pstate, Node *node,
result = coerce_to_domain(result, baseTypeId, baseTypeMod,
targetTypeId,
ccontext, cformat, location,
- true);
+ true,
+ escontext);
}
else
{
@@ -458,7 +518,9 @@ coerce_type(ParseState *pstate, Node *node,
result = coerce_to_domain(node, baseTypeId, baseTypeMod,
targetTypeId,
ccontext, cformat, location,
- false);
+ false,
+ escontext);
+
if (result == node)
{
/*
@@ -675,7 +737,8 @@ can_coerce_type(int nargs, const Oid *input_typeids, const Oid *target_typeids,
Node *
coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod, Oid typeId,
CoercionContext ccontext, CoercionForm cformat, int location,
- bool hideInputCoercion)
+ bool hideInputCoercion,
+ Node *escontext)
{
CoerceToDomain *result;
@@ -705,7 +768,10 @@ coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod, Oid typeId,
*/
arg = coerce_type_typmod(arg, baseTypeId, baseTypeMod,
ccontext, COERCE_IMPLICIT_CAST, location,
- false);
+ false,
+ escontext);
+ if (arg == NULL) /* shouldn't happen */
+ elog(ERROR, "failed to coerce type modifier as expected");
/*
* Now build the domain coercion node. This represents run-time checking
@@ -752,7 +818,8 @@ static Node *
coerce_type_typmod(Node *node, Oid targetTypeId, int32 targetTypMod,
CoercionContext ccontext, CoercionForm cformat,
int location,
- bool hideInputCoercion)
+ bool hideInputCoercion,
+ Node *escontext)
{
CoercionPathType pathtype;
Oid funcId;
@@ -779,7 +846,8 @@ coerce_type_typmod(Node *node, Oid targetTypeId, int32 targetTypMod,
{
node = build_coercion_expression(node, pathtype, funcId,
targetTypeId, targetTypMod,
- ccontext, cformat, location);
+ ccontext, cformat, location,
+ escontext);
}
else
{
@@ -840,7 +908,8 @@ build_coercion_expression(Node *node,
Oid funcId,
Oid targetTypeId, int32 targetTypMod,
CoercionContext ccontext, CoercionForm cformat,
- int location)
+ int location,
+ Node *escontext)
{
int nargs = 0;
@@ -950,14 +1019,15 @@ build_coercion_expression(Node *node,
targetElementType = get_element_type(targetTypeId);
Assert(OidIsValid(targetElementType));
- elemexpr = coerce_to_target_type(NULL,
- (Node *) ctest,
- ctest->typeId,
- targetElementType,
- targetTypMod,
- ccontext,
- cformat,
- location);
+ elemexpr = coerce_to_target_type_extended(NULL,
+ (Node *) ctest,
+ ctest->typeId,
+ targetElementType,
+ targetTypMod,
+ ccontext,
+ cformat,
+ location,
+ escontext);
if (elemexpr == NULL) /* shouldn't happen */
elog(ERROR, "failed to coerce array element type as expected");
@@ -1139,7 +1209,8 @@ coerce_record_to_complex(ParseState *pstate, Node *node,
baseTypeId, baseTypeMod,
targetTypeId,
ccontext, cformat, location,
- false);
+ false,
+ NULL);
}
return (Node *) rowexpr;
@@ -1294,7 +1365,8 @@ coerce_null_to_domain(Oid typid, int32 typmod, Oid collation,
COERCION_IMPLICIT,
COERCE_IMPLICIT_CAST,
-1,
- false);
+ false,
+ NULL);
return result;
}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9adc9d4c0f6..af175b0e8bf 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_cast.h"
#include "catalog/pg_type.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
@@ -38,6 +39,7 @@
#include "utils/date.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "utils/typcache.h"
#include "utils/xml.h"
@@ -62,7 +64,8 @@ static Node *transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref);
static Node *transformCaseExpr(ParseState *pstate, CaseExpr *c);
static Node *transformSubLink(ParseState *pstate, SubLink *sublink);
static Node *transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
- Oid array_type, Oid element_type, int32 typmod);
+ Oid array_type, Oid element_type, int32 typmod,
+ Node *escontext);
static Node *transformRowExpr(ParseState *pstate, RowExpr *r, bool allowDefault);
static Node *transformCoalesceExpr(ParseState *pstate, CoalesceExpr *c);
static Node *transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m);
@@ -78,6 +81,11 @@ static Node *transformWholeRowRef(ParseState *pstate,
int sublevels_up, int location);
static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
+static void CoercionErrorSafeCheck(ParseState *pstate, Node *castexpr,
+ Node *source, Oid inputType, Oid targetType);
+static void CoercionErrorSafe_Internal(Oid inputType, Oid targetType,
+ bool *errorsafe_coercion,
+ bool *userdefined);
static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
static Node *transformJsonObjectConstructor(ParseState *pstate,
JsonObjectConstructor *ctor);
@@ -166,7 +174,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
case T_A_ArrayExpr:
result = transformArrayExpr(pstate, (A_ArrayExpr *) expr,
- InvalidOid, InvalidOid, -1);
+ InvalidOid, InvalidOid, -1, NULL);
break;
case T_TypeCast:
@@ -566,6 +574,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_VALUES_SINGLE:
case EXPR_KIND_CHECK_CONSTRAINT:
case EXPR_KIND_DOMAIN_CHECK:
+ case EXPR_KIND_CAST_DEFAULT:
case EXPR_KIND_FUNCTION_DEFAULT:
case EXPR_KIND_INDEX_EXPRESSION:
case EXPR_KIND_INDEX_PREDICATE:
@@ -1849,6 +1858,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_DOMAIN_CHECK:
err = _("cannot use subquery in check constraint");
break;
+ case EXPR_KIND_CAST_DEFAULT:
+ err = _("cannot use subquery in CAST DEFAULT expression");
+ break;
case EXPR_KIND_COLUMN_DEFAULT:
case EXPR_KIND_FUNCTION_DEFAULT:
err = _("cannot use subquery in DEFAULT expression");
@@ -2042,10 +2054,15 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
* If the caller specifies the target type, the resulting array will
* be of exactly that type. Otherwise we try to infer a common type
* for the elements using select_common_type().
+ *
+ * escontext (ErrorSaveContext *) is typically NULL, except during parse analysis for
+ * CAST(... DEFAULT ... ON CONVERSION ERROR). When provided,
+ * the error_occurred field should be initialized to false. It is set
+ * to true if coercing array elements fails.
*/
static Node *
transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
- Oid array_type, Oid element_type, int32 typmod)
+ Oid array_type, Oid element_type, int32 typmod, Node *escontext)
{
ArrayExpr *newa = makeNode(ArrayExpr);
List *newelems = NIL;
@@ -2076,9 +2093,10 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
(A_ArrayExpr *) e,
array_type,
element_type,
- typmod);
+ typmod,
+ escontext);
/* we certainly have an array here */
- Assert(array_type == InvalidOid || array_type == exprType(newe));
+ Assert(escontext || array_type == InvalidOid || array_type == exprType(newe));
newa->multidims = true;
}
else
@@ -2119,6 +2137,8 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
}
else
{
+ Assert(escontext == NULL);
+
/* Can't handle an empty array without a target type */
if (newelems == NIL)
ereport(ERROR,
@@ -2169,24 +2189,55 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
foreach(element, newelems)
{
Node *e = (Node *) lfirst(element);
- Node *newe;
+ Node *newe = NULL;
if (coerce_hard)
{
- newe = coerce_to_target_type(pstate, e,
- exprType(e),
- coerce_type,
- typmod,
- COERCION_EXPLICIT,
- COERCE_EXPLICIT_CAST,
- -1);
- if (newe == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_CANNOT_COERCE),
- errmsg("cannot cast type %s to %s",
- format_type_be(exprType(e)),
- format_type_be(coerce_type)),
- parser_errposition(pstate, exprLocation(e))));
+ /*
+ * Cannot coerce, just append the transformed element expression
+ * to the list.
+ */
+ if (SOFT_ERROR_OCCURRED(escontext))
+ newe = e;
+ else
+ {
+ Node *ecopy = NULL;
+
+ if (escontext)
+ ecopy = copyObject(e);
+
+ newe = coerce_to_target_type_extended(pstate, e,
+ exprType(e),
+ coerce_type,
+ typmod,
+ COERCION_EXPLICIT,
+ COERCE_EXPLICIT_CAST,
+ -1,
+ escontext);
+ if (newe == NULL)
+ {
+ /*
+ * Cannot coerce. Raise an error or append the transformed
+ * element to the list. Also set error_occurred to true
+ * (in case coerce_to_target_type_extended didn't) so
+ * caller will notice array coercion failed.
+ */
+ if (!escontext)
+ ereport(ERROR,
+ (errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s",
+ format_type_be(exprType(e)),
+ format_type_be(coerce_type)),
+ parser_errposition(pstate, exprLocation(e))));
+ else
+ {
+ ErrorSaveContext *context = castNode(ErrorSaveContext, escontext);
+
+ newe = ecopy;
+ context->error_occurred = true;
+ }
+ }
+ }
}
else
newe = coerce_to_common_type(pstate, e,
@@ -2735,17 +2786,67 @@ transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem,
static Node *
transformTypeCast(ParseState *pstate, TypeCast *tc)
{
- Node *result;
+ SafeTypeCastExpr *stc;
+ Node *castexpr = NULL;
+ Node *defexpr = NULL;
Node *arg = tc->arg;
+ Node *source;
Node *expr;
Oid inputType;
Oid targetType;
+ Oid targetTypecoll;
int32 targetTypmod;
int location;
+ ErrorSaveContext *escontext = NULL;
/* Look up the type name first */
typenameTypeIdAndMod(pstate, tc->typeName, &targetType, &targetTypmod);
+ targetTypecoll = get_typcollation(targetType);
+
+ /* looking at DEFAULT expression */
+ if (tc->defexpr)
+ {
+ Oid defColl;
+
+ escontext = makeNode(ErrorSaveContext);
+ escontext->type = T_ErrorSaveContext;
+ escontext->error_occurred = false;
+ escontext->details_wanted = false;
+
+ defexpr = transformExpr(pstate, tc->defexpr, EXPR_KIND_CAST_DEFAULT);
+
+ defexpr = coerce_to_target_type(pstate, defexpr, exprType(defexpr),
+ targetType, targetTypmod,
+ COERCION_EXPLICIT, COERCE_EXPLICIT_CAST,
+ exprLocation(defexpr));
+
+ if (defexpr == NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot coerce %s expression to type %s",
+ "CAST DEFAULT",
+ format_type_be(targetType)),
+ parser_coercion_errposition(pstate, exprLocation(tc->defexpr), defexpr));
+
+ assign_expr_collations(pstate, defexpr);
+
+ /*
+ * The collation of DEFAULT expression must match the collation of the
+ * target type.
+ */
+ defColl = exprCollation(defexpr);
+
+ if (targetTypecoll != defColl)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("collation of CAST DEFAULT expression conflicts with target type collation"),
+ errdetail("\"%s\" versus \"%s\"",
+ get_collation_name(defColl),
+ get_collation_name(targetTypecoll)),
+ parser_errposition(pstate, exprLocation(defexpr)));
+ }
+
/*
* If the subject of the typecast is an ARRAY[] construct and the target
* type is an array type, we invoke transformArrayExpr() directly so that
@@ -2774,7 +2875,8 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
(A_ArrayExpr *) arg,
targetBaseType,
elementType,
- targetBaseTypmod);
+ targetBaseTypmod,
+ (Node *) escontext);
}
else
expr = transformExprRecurse(pstate, arg);
@@ -2795,20 +2897,218 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
if (location < 0)
location = tc->typeName->location;
- result = coerce_to_target_type(pstate, expr, inputType,
- targetType, targetTypmod,
- COERCION_EXPLICIT,
- COERCE_EXPLICIT_CAST,
- location);
- if (result == NULL)
+ /*
+ * coerce_to_target_type_extended may modify the source expression, so we
+ * still create a copy beforehand. This allows SafeTypeCastExpr to receive
+ * the transformed source expression unchanged.
+ */
+ source = defexpr ? copyObject(expr) : expr;
+
+ if (!SOFT_ERROR_OCCURRED(escontext))
+ {
+ castexpr = coerce_to_target_type_extended(pstate, expr, inputType,
+ targetType, targetTypmod,
+ COERCION_EXPLICIT,
+ COERCE_EXPLICIT_CAST,
+ location,
+ (Node *) escontext);
+
+ /*
+ * No DEFAULT expression, exit now or error out in case of coercion
+ * failure
+ */
+ if (!defexpr)
+ {
+ if (castexpr)
+ return castexpr;
+
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s",
+ format_type_be(inputType),
+ format_type_be(targetType)),
+ parser_coercion_errposition(pstate, location, expr));
+ }
+ }
+
+ /* Further check for CAST(... DEFAULT ... ON CONVERSION ERROR) */
+ CoercionErrorSafeCheck(pstate, castexpr, source, inputType,
+ targetType);
+
+ stc = makeNode(SafeTypeCastExpr);
+ stc->source = (Expr *) source;
+ stc->castexpr = (Expr *) castexpr;
+ stc->defexpr = (Expr *) defexpr;
+ stc->resulttype = targetType;
+ stc->resulttypmod = targetTypmod;
+ stc->resultcollid = targetTypecoll;
+ stc->location = location;
+
+ return (Node *) stc;
+}
+
+/*
+ * Check whether a type coercion is error-safe. If not, report an error.
+ */
+static void
+CoercionErrorSafeCheck(ParseState *pstate, Node *castexpr, Node *source,
+ Oid inputType, Oid targetType)
+{
+ bool errorsafe_coercion = true;
+ bool userdefined = false;
+
+ /*
+ * Binary coercion cast is error-safe, CoerceViaIO can also be evaluated
+ * in an error-safe manner. Skip these cases.
+ */
+ if (castexpr == NULL ||
+ IsBinaryCoercible(inputType, targetType) ||
+ IsA(castexpr, CoerceViaIO))
+ return;
+
+ CoercionErrorSafe_Internal(inputType, targetType,
+ &errorsafe_coercion,
+ &userdefined);
+
+ if (!errorsafe_coercion)
ereport(ERROR,
- (errcode(ERRCODE_CANNOT_COERCE),
- errmsg("cannot cast type %s to %s",
- format_type_be(inputType),
- format_type_be(targetType)),
- parser_coercion_errposition(pstate, location, expr)));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot cast type %s to %s with DEFAULT expression in CAST ... ON CONVERSION ERROR",
+ format_type_be(inputType),
+ format_type_be(targetType)),
+ userdefined
+ ? errdetail("Safe type casts for user-defined types are not yet supported.")
+ : errdetail("Explicit cast is defined but definition is not error safe."),
+ parser_errposition(pstate, exprLocation(source)));
+}
- return result;
+/*
+ * Recursion is required here because type casts may involve arrays over domains
+ * or domains over arrays, so we need resurse to the base element type for both
+ * the input and target types.
+ */
+static void
+CoercionErrorSafe_Internal(Oid inputType, Oid targetType,
+ bool *errorsafe_coercion, bool *userdefined)
+{
+ char input_typtype = get_typtype(inputType);
+ char target_typtype = get_typtype(targetType);
+ HeapTuple tuple;
+
+ Assert(errorsafe_coercion != NULL);
+ Assert(userdefined != NULL);
+
+ if (!(*errorsafe_coercion))
+ return;
+
+ if (input_typtype == TYPTYPE_DOMAIN &&
+ target_typtype == TYPTYPE_DOMAIN)
+ {
+ CoercionErrorSafe_Internal(getBaseType(inputType),
+ getBaseType(targetType),
+ errorsafe_coercion,
+ userdefined);
+ return;
+ }
+ else if (input_typtype == TYPTYPE_DOMAIN)
+ {
+ CoercionErrorSafe_Internal(getBaseType(inputType),
+ targetType,
+ errorsafe_coercion,
+ userdefined);
+ return;
+ }
+ else if (target_typtype == TYPTYPE_DOMAIN)
+ {
+ CoercionErrorSafe_Internal(inputType,
+ getBaseType(targetType),
+ errorsafe_coercion,
+ userdefined);
+ return;
+ }
+ else if ((input_typtype != TYPTYPE_BASE && inputType > FirstUnpinnedObjectId)
+ || (target_typtype != TYPTYPE_BASE && targetType > FirstUnpinnedObjectId))
+ {
+ /*
+ * Composite-to-composite casting is not implemented, so error-safe
+ * casting between composite types is not possible and is forbidden
+ * here.
+ *
+ * Additionally, our type system does not automatically cast a
+ * user-defined range type to a built-in range type, even when their
+ * base element types match. Due to potential edge cases, error-safe
+ * casting for such types is disallowed.
+ */
+ *errorsafe_coercion = false;
+
+ return;
+ }
+ else
+ {
+ Oid input_typelem = get_element_type(inputType);
+ Oid target_typelem = get_element_type(targetType);
+
+ if (OidIsValid(input_typelem) && OidIsValid(target_typelem))
+ {
+ /* Recurse into the array element types. */
+ CoercionErrorSafe_Internal(input_typelem,
+ target_typelem,
+ errorsafe_coercion,
+ userdefined);
+ return;
+ }
+
+ /*
+ * It is unlikely that an array can be coerced to a non-array, or vice
+ * versa. Currently, this only occurs when the target type is text,
+ * resulting in a CoerceViaIO cast expression—which is already
+ * handled by CoercionErrorSafeCheck.
+ */
+ }
+
+ /*
+ * Casts from MONEY source type are not error safe.
+ */
+ if (inputType == MONEYOID)
+ {
+ *errorsafe_coercion = false;
+
+ return;
+ }
+
+ /*
+ * In case preivous logic didn't figure out that the real element base
+ * type is identitcal then obvously, they are error safe.
+ */
+ if (inputType == targetType)
+ return;
+ else
+ {
+ tuple = SearchSysCache2(CASTSOURCETARGET,
+ ObjectIdGetDatum(inputType),
+ ObjectIdGetDatum(targetType));
+
+ /*
+ * A pg_cast entry might not exist for this specific cast; for
+ * example, when using CoerceViaIO.
+ */
+ if (HeapTupleIsValid(tuple))
+ {
+ Form_pg_cast castForm = (Form_pg_cast) GETSTRUCT(tuple);
+
+ if (castForm->castfunc > FirstUnpinnedObjectId)
+ {
+ *errorsafe_coercion = false;
+ *userdefined = true;
+ }
+ ReleaseSysCache(tuple);
+ }
+ else if (inputType > FirstUnpinnedObjectId || targetType > FirstUnpinnedObjectId)
+ {
+ *errorsafe_coercion = false;
+ *userdefined = true;
+ }
+ }
}
/*
@@ -3224,6 +3524,8 @@ ParseExprKindName(ParseExprKind exprKind)
case EXPR_KIND_CHECK_CONSTRAINT:
case EXPR_KIND_DOMAIN_CHECK:
return "CHECK";
+ case EXPR_KIND_CAST_DEFAULT:
+ return "CAST DEFAULT";
case EXPR_KIND_COLUMN_DEFAULT:
case EXPR_KIND_FUNCTION_DEFAULT:
return "DEFAULT";
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 860767a52ee..0952d0e4ad7 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2746,6 +2746,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_DOMAIN_CHECK:
err = _("set-returning functions are not allowed in check constraints");
break;
+ case EXPR_KIND_CAST_DEFAULT:
+ err = _("set-returning functions are not allowed in CAST DEFAULT expressions");
+ break;
case EXPR_KIND_COLUMN_DEFAULT:
case EXPR_KIND_FUNCTION_DEFAULT:
err = _("set-returning functions are not allowed in DEFAULT expressions");
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 6a862d5a4f9..35d99fdac07 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -845,7 +845,8 @@ transformAssignmentIndirection(ParseState *pstate,
COERCION_IMPLICIT,
COERCE_IMPLICIT_CAST,
location,
- false);
+ false,
+ NULL);
return (Node *) fstore;
}
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
index bb7eccde9fd..bbf39dac619 100644
--- a/src/backend/parser/parse_type.c
+++ b/src/backend/parser/parse_type.c
@@ -660,6 +660,28 @@ stringTypeDatum(Type tp, char *string, int32 atttypmod)
return OidInputFunctionCall(typinput, string, typioparam, atttypmod);
}
+/*
+ * Error-safe version of stringTypeDatum.
+ *
+ * Returns true if "string" is a valid representation of type "tp".
+ * On success, the internal Datum representation is written to "result".
+ */
+bool
+stringTypeDatumSafe(Type tp, char *string, int32 atttypmod,
+ Node *escontext, Datum *result)
+{
+ FmgrInfo flinfo;
+
+ Form_pg_type typform = (Form_pg_type) GETSTRUCT(tp);
+ Oid typinput = typform->typinput;
+ Oid typioparam = getTypeIOParam(tp);
+
+ fmgr_info(typinput, &flinfo);
+
+ return InputFunctionCallSafe(&flinfo, string, typioparam, atttypmod,
+ escontext, result);
+}
+
/*
* Given a typeid, return the type's typrelid (associated relation), if any.
* Returns InvalidOid if type is not a composite type.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index a049cc67ed6..a7284f48429 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -5196,7 +5196,7 @@ transformPartitionBoundValue(ParseState *pstate, Node *val,
assign_expr_collations(pstate, value);
value = (Node *) expression_planner((Expr *) value);
value = (Node *) evaluate_expr((Expr *) value, colType, colTypmod,
- partCollation);
+ partCollation, NULL);
if (!IsA(value, Const))
elog(ERROR, "could not evaluate partition bound expression");
}
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index 13eed22dd6a..70523248f56 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -3294,6 +3294,15 @@ array_map(Datum arrayd,
/* Apply the given expression to source element */
values[i] = ExecEvalExpr(exprstate, econtext, &nulls[i]);
+ /* Exit early if evaluation failed */
+ if (SOFT_ERROR_OCCURRED(exprstate->escontext))
+ {
+ pfree(values);
+ pfree(nulls);
+
+ return (Datum) 0;
+ }
+
if (nulls[i])
hasnulls = true;
else
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 88de5c0481c..2e47ec21855 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11114,6 +11114,31 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node);
+
+ /*
+ * Cannot deparse castexpr directly, becuase transformTypeCast
+ * may have already constant-folded the cast expression into a
+ * constant. Instead, use "source" and "defexpr" to
+ * reconstruct the CAST DEFAULT clause.
+ */
+ appendStringInfoString(buf, "CAST(");
+ get_rule_expr((Node *) stcexpr->source, context, showimplicit);
+
+ appendStringInfo(buf, " AS %s ",
+ format_type_with_typemod(stcexpr->resulttype,
+ stcexpr->resulttypmod));
+
+ appendStringInfoString(buf, "DEFAULT ");
+
+ get_rule_expr((Node *) stcexpr->defexpr, context, showimplicit);
+
+ appendStringInfoString(buf, " ON CONVERSION ERROR)");
+ }
+ break;
+
case T_JsonExpr:
{
JsonExpr *jexpr = (JsonExpr *) node;
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index c61b3d624d5..faf6976b040 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -265,6 +265,7 @@ typedef enum ExprEvalOp
EEOP_XMLEXPR,
EEOP_JSON_CONSTRUCTOR,
EEOP_IS_JSON,
+ EEOP_SAFETYPE_CAST,
EEOP_JSONEXPR_PATH,
EEOP_JSONEXPR_COERCION,
EEOP_JSONEXPR_COERCION_FINISH,
@@ -754,6 +755,12 @@ typedef struct ExprEvalStep
JsonIsPredicate *pred; /* original expression node */
} is_json;
+ /* for EEOP_SAFETYPE_CAST */
+ struct
+ {
+ struct SafeTypeCastState *stcstate;
+ } stcexpr;
+
/* for EEOP_JSONEXPR_PATH */
struct
{
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 650baab3efc..33bbdbfeffb 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -332,6 +332,7 @@ ExecProcNode(PlanState *node)
* prototypes from functions in execExpr.c
*/
extern ExprState *ExecInitExpr(Expr *node, PlanState *parent);
+extern ExprState *ExecInitExprWithContext(Expr *node, PlanState *parent, Node *escontext);
extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params);
extern ExprState *ExecInitQual(List *qual, PlanState *parent);
extern ExprState *ExecInitCheck(List *qual, PlanState *parent);
@@ -380,6 +381,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList,
TupleTableSlot *slot,
PlanState *parent);
extern ExprState *ExecPrepareExpr(Expr *node, EState *estate);
+extern ExprState *ExecPrepareExprWithContext(Expr *node, EState *estate, Node *escontext);
extern ExprState *ExecPrepareQual(List *qual, EState *estate);
extern ExprState *ExecPrepareCheck(List *qual, EState *estate);
extern List *ExecPrepareExprList(List *nodes, EState *estate);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 53c138310db..2dc95aaaa33 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1096,6 +1096,27 @@ typedef struct DomainConstraintState
ExprState *check_exprstate; /* check_expr's eval state, or NULL */
} DomainConstraintState;
+typedef struct SafeTypeCastState
+{
+ SafeTypeCastExpr *stcexpr;
+
+ /*
+ * The jump target used to skip all remaining steps when the default
+ * expression evaluation is bypassed.
+ */
+ int jump_end;
+
+ /*
+ * Error-safe context for type coercion evaluation. A pointer to this is
+ * passed to ExecInitExprRec() during type coercion expression
+ * initialization (see ExecInitSafeTypeCastExpr).
+ *
+ * At runtime, this is reset before each evaluation of EEOP_SAFETYPE_CAST.
+ */
+ ErrorSaveContext escontext;
+
+} SafeTypeCastState;
+
/*
* State for JsonExpr evaluation, too big to inline.
*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4133c404a6b..641ff29c6d6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -402,6 +402,7 @@ typedef struct TypeCast
NodeTag type;
Node *arg; /* the expression being casted */
TypeName *typeName; /* the target type */
+ Node *defexpr; /* DEFAULT expression */
ParseLoc location; /* token location, or -1 if unknown */
} TypeCast;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index bb05aeebee4..bb4cc37ef9c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -760,6 +760,40 @@ typedef enum CoercionForm
COERCE_SQL_SYNTAX, /* display with SQL-mandated special syntax */
} CoercionForm;
+/*
+ * SafeTypeCastExpr -
+ * Transformed representation of
+ * CAST(expr AS typename DEFAULT expr ON CONVERSION ERROR)
+ */
+typedef struct SafeTypeCastExpr
+{
+ Expr xpr;
+
+ /*
+ * The transformed source expression.
+ *
+ * Cases like ``CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR)`` where
+ * the castexpr evaluates to NULL, we need this field to reconstruct the
+ * original query.
+ */
+ Expr *source;
+
+ /*
+ * transformed cast expression, NULL means cannot coerce to target type
+ */
+ Expr *castexpr pg_node_attr(query_jumble_ignore);
+ /* Fall-back to the default expression if cast evaluation fails */
+ Expr *defexpr;
+ /* target type Oid */
+ Oid resulttype;
+ /* target type modifier */
+ int32 resulttypmod;
+ /* OID of collation, or InvalidOid if none */
+ Oid resultcollid;
+ /* token location, or -1 if unknown */
+ ParseLoc location;
+} SafeTypeCastExpr;
+
/*
* FuncExpr - expression node for a function call
*/
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index cb6241e2bdd..fb81ed6e551 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -151,7 +151,7 @@ extern void convert_saop_to_hashed_saop(Node *node);
extern Node *estimate_expression_value(PlannerInfo *root, Node *node);
extern Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
- Oid result_collation);
+ Oid result_collation, Node *escontext);
extern bool var_is_nonnullable(PlannerInfo *root, Var *var,
NotNullSource source);
diff --git a/src/include/parser/parse_coerce.h b/src/include/parser/parse_coerce.h
index aabacd49b65..12644f146bf 100644
--- a/src/include/parser/parse_coerce.h
+++ b/src/include/parser/parse_coerce.h
@@ -43,15 +43,29 @@ extern Node *coerce_to_target_type(ParseState *pstate,
CoercionContext ccontext,
CoercionForm cformat,
int location);
+extern Node *coerce_to_target_type_extended(ParseState *pstate,
+ Node *expr,
+ Oid exprtype,
+ Oid targettype,
+ int32 targettypmod,
+ CoercionContext ccontext,
+ CoercionForm cformat,
+ int location,
+ Node *escontext);
extern bool can_coerce_type(int nargs, const Oid *input_typeids, const Oid *target_typeids,
CoercionContext ccontext);
extern Node *coerce_type(ParseState *pstate, Node *node,
Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
CoercionContext ccontext, CoercionForm cformat, int location);
+extern Node *coerce_type_extended(ParseState *pstate, Node *node,
+ Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
+ CoercionContext ccontext, CoercionForm cformat, int location,
+ Node *escontext);
extern Node *coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod,
Oid typeId,
CoercionContext ccontext, CoercionForm cformat, int location,
- bool hideInputCoercion);
+ bool hideInputCoercion,
+ Node *escontext);
extern Node *coerce_to_boolean(ParseState *pstate, Node *node,
const char *constructName);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7f4ba6c2a8..472567696d0 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -68,6 +68,8 @@ typedef enum ParseExprKind
EXPR_KIND_VALUES_SINGLE, /* single-row VALUES (in INSERT only) */
EXPR_KIND_CHECK_CONSTRAINT, /* CHECK constraint for a table */
EXPR_KIND_DOMAIN_CHECK, /* CHECK constraint for a domain */
+ EXPR_KIND_CAST_DEFAULT, /* default expression in CAST DEFAULT ON
+ * CONVERSION ERROR */
EXPR_KIND_COLUMN_DEFAULT, /* default value for a table column */
EXPR_KIND_FUNCTION_DEFAULT, /* default parameter value for function */
EXPR_KIND_INDEX_EXPRESSION, /* index expression */
diff --git a/src/include/parser/parse_type.h b/src/include/parser/parse_type.h
index a335807b0b0..e303a1073c1 100644
--- a/src/include/parser/parse_type.h
+++ b/src/include/parser/parse_type.h
@@ -47,6 +47,8 @@ extern char *typeTypeName(Type t);
extern Oid typeTypeRelid(Type typ);
extern Oid typeTypeCollation(Type typ);
extern Datum stringTypeDatum(Type tp, char *string, int32 atttypmod);
+extern bool stringTypeDatumSafe(Type tp, char *string, int32 atttypmod,
+ Node *escontext, Datum *result);
extern Oid typeidTypeRelid(Oid type_id);
extern Oid typeOrDomainTypeRelid(Oid type_id);
diff --git a/src/test/regress/expected/cast.out b/src/test/regress/expected/cast.out
new file mode 100644
index 00000000000..d0afce2bc97
--- /dev/null
+++ b/src/test/regress/expected/cast.out
@@ -0,0 +1,1185 @@
+SET extra_float_digits = 0;
+SET lc_monetary TO "C";
+-- CAST DEFAULT ON CONVERSION ERROR
+SELECT CAST(B'01' AS date DEFAULT NULL ON CONVERSION ERROR);
+ date
+------
+
+(1 row)
+
+SELECT CAST(BIT'01' AS date DEFAULT NULL ON CONVERSION ERROR);
+ date
+------
+
+(1 row)
+
+SELECT CAST(TRUE AS date DEFAULT NULL ON CONVERSION ERROR);
+ date
+------
+
+(1 row)
+
+SELECT CAST(1.1 AS date DEFAULT NULL ON CONVERSION ERROR);
+ date
+------
+
+(1 row)
+
+SELECT CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR);
+ date
+------
+
+(1 row)
+
+SELECT CAST(1111 AS "char" DEFAULT 'A' ON CONVERSION ERROR);
+ char
+------
+ A
+(1 row)
+
+SELECT CAST('def'::text AS integer DEFAULT NULL ON CONVERSION ERROR);
+ int4
+------
+
+(1 row)
+
+SELECT CAST('2'::jsonb AS text DEFAULT 'fallback' ON CONVERSION ERROR);
+ text
+------
+ 2
+(1 row)
+
+SELECT CAST('1' COLLATE "C" || 'h' AS text DEFAULT 'fallback' ON CONVERSION ERROR);
+ text
+------
+ 1h
+(1 row)
+
+SELECT CAST(concat('1' collate "C", 'h') AS int DEFAULT NULL ON CONVERSION ERROR);
+ concat
+--------
+
+(1 row)
+
+SELECT CAST(65536 AS int2 DEFAULT NULL ON CONVERSION ERROR);
+ int2
+------
+
+(1 row)
+
+SELECT CAST(int8(65536) AS int2 DEFAULT NULL ON CONVERSION ERROR);
+ int8
+------
+
+(1 row)
+
+-- source expression is a unknown const
+VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR)); -- error
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR));
+ ^
+VALUES (CAST('error' AS integer DEFAULT NULL ON CONVERSION ERROR));
+ column1
+---------
+
+(1 row)
+
+VALUES (CAST('error' AS integer DEFAULT 42 ON CONVERSION ERROR));
+ column1
+---------
+ 42
+(1 row)
+
+SELECT CAST('a' as int DEFAULT 18 ON CONVERSION ERROR);
+ int4
+------
+ 18
+(1 row)
+
+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); -- error
+ERROR: aggregate functions are not allowed in CAST DEFAULT expressions
+LINE 1: SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR);
+ ^
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); -- error
+ERROR: window functions are not allowed in CAST DEFAULT expressions
+LINE 1: SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION E...
+ ^
+SELECT CAST('a' as int DEFAULT (SELECT NULL) ON CONVERSION ERROR); -- error
+ERROR: cannot use subquery in CAST DEFAULT expression
+LINE 1: SELECT CAST('a' as int DEFAULT (SELECT NULL) ON CONVERSION E...
+ ^
+SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR); -- error
+ERROR: invalid input syntax for type integer: "b"
+LINE 1: SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR);
+ ^
+SELECT CAST('a'::int as int DEFAULT NULL ON CONVERSION ERROR); -- error
+ERROR: invalid input syntax for type integer: "a"
+LINE 1: SELECT CAST('a'::int as int DEFAULT NULL ON CONVERSION ERROR...
+ ^
+-- the default expression’s collation should match target type collation
+VALUES (CAST('error' AS text DEFAULT '1' COLLATE "C" ON CONVERSION ERROR));
+ERROR: collation of CAST DEFAULT expression conflicts with target type collation
+LINE 1: VALUES (CAST('error' AS text DEFAULT '1' COLLATE "C" ON CONV...
+ ^
+DETAIL: "C" versus "default"
+VALUES (CAST('error' AS int2vector DEFAULT '1 3' ON CONVERSION ERROR));
+ column1
+---------
+ 1 3
+(1 row)
+
+VALUES (CAST('error' AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR));
+ column1
+---------
+ {"1 3"}
+(1 row)
+
+-- source expression contain subquery
+SELECT CAST((SELECT b FROM generate_series(1, 1), (VALUES('H')) s(b))
+ AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR);
+ b
+---------
+ {"1 3"}
+(1 row)
+
+SELECT CAST((SELECT ARRAY((SELECT b FROM generate_series(1, 2) g, (VALUES('H')) s(b))))
+ AS INT[]
+ DEFAULT NULL ON CONVERSION ERROR);
+ array
+-------
+
+(1 row)
+
+CREATE FUNCTION ret_int8() RETURNS BIGINT AS
+$$
+BEGIN RETURN 2147483648; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+SELECT CAST('a' as int DEFAULT ret_int8() ON CONVERSION ERROR); -- error
+ERROR: integer out of range
+SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERROR); -- error
+ERROR: cannot coerce CAST DEFAULT expression to type date
+LINE 1: SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERR...
+ ^
+-- DEFAULT expression cannot be set-returning
+CREATE FUNCTION ret_setint() RETURNS SETOF integer AS
+$$
+BEGIN RETURN QUERY EXECUTE 'select 1 union all select 1'; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+CREATE TABLE tcast0(a INT, b int);
+INSERT INTO tcast0 VALUES(1,2), (65536,3);
+SET jit_above_cost = 0;
+SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0;
+ a
+---
+ 1
+
+(2 rows)
+
+SET jit_above_cost TO DEFAULT;
+SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0;
+ a
+---
+ 1
+
+(2 rows)
+
+SELECT CAST(ROW(1,2) AS tcast0 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0 as t;
+ERROR: cannot cast type record to tcast0 with DEFAULT expression in CAST ... ON CONVERSION ERROR
+LINE 1: SELECT CAST(ROW(1,2) AS tcast0 DEFAULT NULL ON CONVERSION ER...
+ ^
+DETAIL: Explicit cast is defined but definition is not error safe.
+DROP TABLE tcast0;
+SELECT CAST(12.111 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale;
+ to_numeric_scale
+------------------
+ 12.1
+(1 row)
+
+CREATE TABLE tcast(a text[], b int GENERATED BY DEFAULT AS IDENTITY);
+INSERT INTO tcast VALUES ('{12}'), ('{1,a, b}'), ('{{1,2}, {c,d}}'), ('{13}');
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) FROM tcast; -- error
+ERROR: set-returning functions are not allowed in CAST DEFAULT expressions
+LINE 1: SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ER...
+ ^
+SELECT CAST(t AS text[] DEFAULT '{21,22, ' || b || '}' ON CONVERSION ERROR) FROM tcast as t;
+ t
+-----------
+ {21,22,1}
+ {21,22,2}
+ {21,22,3}
+ {21,22,4}
+(4 rows)
+
+SELECT CAST(t.a AS int[] DEFAULT '{21,22}'::int[] || b ON CONVERSION ERROR) FROM tcast as t;
+ a
+-----------
+ {12}
+ {21,22,2}
+ {21,22,3}
+ {13}
+(4 rows)
+
+CREATE DOMAIN ddomain AS numeric CHECK ((1/ (VALUE - 1)) > 0);
+SELECT CAST ('1' AS ddomain DEFAULT NULL ON CONVERSION ERROR);
+ERROR: division by zero
+SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR);
+ERROR: division by zero
+CREATE VIEW vddomain AS SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR);
+\sv vddomain
+CREATE OR REPLACE VIEW public.vddomain AS
+ SELECT CAST(1 AS ddomain DEFAULT NULL::numeric::ddomain ON CONVERSION ERROR) AS ddomain
+DROP VIEW vddomain;
+-- Test nested type casts; all sub-expressions will be evaluated in an error-safe manner too.
+CREATE DOMAIN d_nota AS text CHECK (VALUE <> 'a');
+CREATE TABLE tcast1(a text, b text[], c int, d int8, e int8[]);
+INSERT INTO tcast1(a, b, d, e) values('a', '{a}', 2147483648, '{2147483648}');
+SELECT CAST (CAST(a AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ a
+---
+
+(1 row)
+
+SELECT CAST (CAST('a' AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- error
+ERROR: invalid input syntax for type integer: "a"
+LINE 1: SELECT CAST (CAST('a' AS int) AS text DEFAULT NULL ON CONVER...
+ ^
+SELECT CAST (CAST('a'::text AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- ok
+ text
+------
+
+(1 row)
+
+SELECT CAST (CAST(a AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ a
+---
+
+(1 row)
+
+SELECT CAST (CAST('a' AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ text
+------
+
+(1 row)
+
+SELECT CAST (CAST(d AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ d
+---
+
+(1 row)
+
+SELECT CAST (CAST('2147483648'::int8 AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ text
+------
+
+(1 row)
+
+SELECT CAST (CAST('{a}'::text[] AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ text
+------
+
+(1 row)
+
+SELECT CAST (CAST(b AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ b
+---
+
+(1 row)
+
+SELECT CAST (CAST(b AS d_nota[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ b
+---
+
+(1 row)
+
+SELECT CAST (CAST(e AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ e
+---
+
+(1 row)
+
+-- test nested ArrayCoerceExpr constant folding
+SELECT CAST('{a}'::text[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ int4
+------
+
+(1 row)
+
+SELECT CAST(CAST('{a}'::text[] AS date[]) AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+ int4
+------
+
+(1 row)
+
+SELECT CAST(('{' || sum(1 + 2) || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub;
+ int4
+------
+ {6}
+(1 row)
+
+SELECT CAST(('{' || sum(1 + 2) OVER () || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub;
+ int4
+------
+ {6}
+ {6}
+(2 rows)
+
+-- test with user-defined type, domain, array over domain, domain over array
+CREATE DOMAIN d_int42 as int check (value = 42) NOT NULL;
+CREATE DOMAIN d_int42arr as d_int42[];
+CREATE DOMAIN d_int42arr1 as d_int42arr;
+CREATE DOMAIN d_int8 as int8 check (value > 0);
+CREATE DOMAIN d_int8arr as d_int8[];
+CREATE DOMAIN d_char3_not_null as char(3) NOT NULL;
+CREATE DOMAIN d_varchar as varchar(3) NOT NULL;
+CREATE DOMAIN d_int_arr as int[] check (value = '{41, 43}') NOT NULL;
+CREATE TYPE comp_domain_with_typmod AS (a d_char3_not_null, b int);
+CREATE TYPE comp2 AS (a d_varchar);
+CREATE DOMAIN d_numeric as numeric(4,4);
+SELECT CAST(1.0::float4 AS d_numeric DEFAULT NULL ON CONVERSION ERROR);
+ d_numeric
+-----------
+
+(1 row)
+
+SELECT CAST('{1.0}' AS numeric(4,1)[] DEFAULT NULL ON CONVERSION ERROR);
+ numeric
+---------
+ {1.0}
+(1 row)
+
+SELECT CAST('{0.1, 1.0}' AS d_numeric[] DEFAULT NULL ON CONVERSION ERROR);
+ d_numeric
+-----------
+
+(1 row)
+
+SELECT CAST(11 AS d_int42 DEFAULT 41 ON CONVERSION ERROR); -- error
+ERROR: value for domain d_int42 violates check constraint "d_int42_check"
+SELECT CAST(NULL AS d_int42 DEFAULT NULL ON CONVERSION ERROR); -- error
+ERROR: domain d_int42 does not allow null values
+SELECT CAST(11 AS d_int42 DEFAULT 42 ON CONVERSION ERROR);
+ d_int42
+---------
+ 42
+(1 row)
+
+SELECT CAST(NULL AS d_int42 DEFAULT 42 ON CONVERSION ERROR);
+ d_int42
+---------
+ 42
+(1 row)
+
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8 DEFAULT '1' ON CONVERSION ERROR);
+ array
+-------
+ 1
+(1 row)
+
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8[] DEFAULT '{1}' ON CONVERSION ERROR);
+ array
+-------
+ {42}
+(1 row)
+
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr DEFAULT '{1}' ON CONVERSION ERROR);
+ array
+-------
+ {42}
+(1 row)
+
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr[] DEFAULT NULL ON CONVERSION ERROR);
+ array
+-------
+
+(1 row)
+
+SELECT CAST(ARRAY[42,41] AS d_int42[] DEFAULT '{42, 42}' ON CONVERSION ERROR);
+ array
+---------
+ {42,42}
+(1 row)
+
+SELECT CAST(ARRAY[42,41] AS d_int_arr DEFAULT '{41, 43}' ON CONVERSION ERROR);
+ array
+---------
+ {41,43}
+(1 row)
+
+SELECT CAST(ARRAY[42, 41]::d_int42arr1 AS d_int8arr DEFAULT '{1,2,3}' ON CONVERSION ERROR);
+ array
+---------
+ {1,2,3}
+(1 row)
+
+SELECT CAST(ARRAY[42, 41] AS d_int42arr1 DEFAULT '{42, 42}' ON CONVERSION ERROR);
+ array
+---------
+ {42,42}
+(1 row)
+
+SELECT CAST(CAST(ARRAY[CAST(ARRAY[42, 42] AS d_int42arr)] AS d_int42arr1[])
+ AS d_int8arr[]
+ DEFAULT NULL ON CONVERSION ERROR);
+ array
+-------------
+ {"{42,42}"}
+(1 row)
+
+SELECT CAST('(NULL)' AS comp2 DEFAULT '(1232)' ON CONVERSION ERROR); -- error
+ERROR: value too long for type character varying(3)
+LINE 1: SELECT CAST('(NULL)' AS comp2 DEFAULT '(1232)' ON CONVERSION...
+ ^
+SELECT CAST('(NULL)' AS comp2 DEFAULT '(123)' ON CONVERSION ERROR);
+ comp2
+-------
+ (123)
+(1 row)
+
+SELECT CAST('(,42)' AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR);
+ comp_domain_with_typmod
+-------------------------
+
+(1 row)
+
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR);
+ comp_domain_with_typmod
+-------------------------
+ ("1 ",2)
+(1 row)
+
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)' ON CONVERSION ERROR); -- error
+ERROR: value too long for type character(3)
+LINE 1: ...ST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)'...
+ ^
+SELECT CAST(ROW(NULL,42) AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR); -- error
+ERROR: cannot cast type record to comp_domain_with_typmod with DEFAULT expression in CAST ... ON CONVERSION ERROR
+LINE 1: SELECT CAST(ROW(NULL,42) AS comp_domain_with_typmod DEFAULT ...
+ ^
+DETAIL: Explicit cast is defined but definition is not error safe.
+-- test array coerce
+SELECT CAST(array['a'::text] AS int[] DEFAULT NULL ON CONVERSION ERROR);
+ array
+-------
+
+(1 row)
+
+SELECT CAST(array['a'] AS int[] DEFAULT ARRAY[1] ON CONVERSION ERROR);
+ array
+-------
+ {1}
+(1 row)
+
+SELECT CAST(array[11.12] AS date[] DEFAULT NULL ON CONVERSION ERROR);
+ array
+-------
+
+(1 row)
+
+SELECT CAST(array[11111111111111111] AS int[] DEFAULT NULL ON CONVERSION ERROR);
+ array
+-------
+
+(1 row)
+
+SELECT CAST(array[['abc'],[456]] AS int[] DEFAULT NULL ON CONVERSION ERROR);
+ array
+-------
+
+(1 row)
+
+SELECT CAST('{123,abc,456}' AS int[] DEFAULT '{-789}' ON CONVERSION ERROR);
+ int4
+--------
+ {-789}
+(1 row)
+
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON CONVERSION ERROR);
+ int4
+---------
+ {-1011}
+(1 row)
+
+SELECT CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR);
+ array
+-------
+ {1,2}
+(1 row)
+
+SELECT CAST(ARRAY[['1', '2'], ['three', 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+ array
+---------
+ {21,22}
+(1 row)
+
+-- error: `three'::int` will fail earlier
+SELECT CAST(ARRAY[['1', '2'], ['three'::int, 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+ERROR: invalid input syntax for type integer: "three"
+LINE 1: SELECT CAST(ARRAY[['1', '2'], ['three'::int, 'a']] AS int[] ...
+ ^
+SELECT CAST(ARRAY[1, 'three'::int] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+ERROR: invalid input syntax for type integer: "three"
+LINE 1: SELECT CAST(ARRAY[1, 'three'::int] AS int[] DEFAULT '{21,22}...
+ ^
+-- safe cast with geometry data type
+SELECT CAST('(1,2)'::point AS box DEFAULT NULL ON CONVERSION ERROR);
+ box
+-------------
+ (1,2),(1,2)
+(1 row)
+
+SELECT CAST('[(NaN,1),(NaN,infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR);
+ point
+----------------
+ (NaN,Infinity)
+(1 row)
+
+SELECT CAST('[(1e+300,Infinity),(1e+300,Infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR);
+ point
+-------------------
+ (1e+300,Infinity)
+(1 row)
+
+SELECT CAST('[(1,2),(3,4)]'::path as polygon DEFAULT NULL ON CONVERSION ERROR);
+ polygon
+---------
+
+(1 row)
+
+SELECT CAST('(NaN,1.0,NaN,infinity)'::box AS point DEFAULT NULL ON CONVERSION ERROR);
+ point
+----------------
+ (NaN,Infinity)
+(1 row)
+
+SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS lseg DEFAULT NULL ON CONVERSION ERROR);
+ lseg
+---------------
+ [(2,2),(0,0)]
+(1 row)
+
+SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS polygon DEFAULT NULL ON CONVERSION ERROR);
+ polygon
+---------------------------
+ ((0,0),(0,2),(2,2),(2,0))
+(1 row)
+
+SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS path DEFAULT NULL ON CONVERSION ERROR);
+ path
+------
+
+(1 row)
+
+SELECT CAST('(2.0,infinity,NaN,infinity)'::box AS circle DEFAULT NULL ON CONVERSION ERROR);
+ circle
+----------------------
+ <(NaN,Infinity),NaN>
+(1 row)
+
+SELECT CAST('(NaN,0.0),(2.0,4.0),(0.0,infinity)'::polygon AS point DEFAULT NULL ON CONVERSION ERROR);
+ point
+----------------
+ (NaN,Infinity)
+(1 row)
+
+SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS path DEFAULT NULL ON CONVERSION ERROR);
+ path
+---------------------
+ ((2,0),(2,4),(0,0))
+(1 row)
+
+SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS box DEFAULT NULL ON CONVERSION ERROR);
+ box
+-------------
+ (2,4),(0,0)
+(1 row)
+
+SELECT CAST('(NaN,infinity),(2.0,4.0),(0.0,infinity)'::polygon AS circle DEFAULT NULL ON CONVERSION ERROR);
+ circle
+----------------------
+ <(NaN,Infinity),NaN>
+(1 row)
+
+SELECT CAST('<(5,1),3>'::circle AS point DEFAULT NULL ON CONVERSION ERROR);
+ point
+-------
+ (5,1)
+(1 row)
+
+SELECT CAST('<(3,5),0>'::circle as box DEFAULT NULL ON CONVERSION ERROR);
+ box
+-------------
+ (3,5),(3,5)
+(1 row)
+
+SELECT CAST('<(3,5),0>'::circle as polygon DEFAULT NULL ON CONVERSION ERROR);
+ polygon
+---------
+
+(1 row)
+
+-- safe cast with money data type
+-- cast from money to other type is not error safe
+SELECT CAST('123456789012345678'::numeric AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+ money
+-------
+
+(1 row)
+
+SELECT CAST('123456789012345678'::int8 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+ money
+-------
+
+(1 row)
+
+SELECT CAST('2147483647'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+ money
+-------------------
+ $2,147,483,647.00
+(1 row)
+
+SELECT CAST('-2147483648'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+ money
+--------------------
+ -$2,147,483,648.00
+(1 row)
+
+SELECT CAST('-0'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+ money
+-------
+ $0.00
+(1 row)
+
+SELECT CAST(NULL::money AS numeric DEFAULT NULL ON CONVERSION ERROR);
+ERROR: cannot cast type money to numeric with DEFAULT expression in CAST ... ON CONVERSION ERROR
+LINE 1: SELECT CAST(NULL::money AS numeric DEFAULT NULL ON CONVERSIO...
+ ^
+DETAIL: Explicit cast is defined but definition is not error safe.
+SELECT CAST(NULL::money[] AS numeric[] DEFAULT NULL ON CONVERSION ERROR);
+ERROR: cannot cast type money[] to numeric[] with DEFAULT expression in CAST ... ON CONVERSION ERROR
+LINE 1: SELECT CAST(NULL::money[] AS numeric[] DEFAULT NULL ON CONVE...
+ ^
+DETAIL: Explicit cast is defined but definition is not error safe.
+-- safe cast from boolean type to other data types
+SELECT CAST(true AS numeric DEFAULT NULL ON CONVERSION ERROR);
+ numeric
+---------
+
+(1 row)
+
+SELECT CAST(false AS int DEFAULT NULL ON CONVERSION ERROR);
+ int4
+------
+ 0
+(1 row)
+
+SELECT CAST(false AS text DEFAULT NULL ON CONVERSION ERROR);
+ text
+-------
+ false
+(1 row)
+
+-- safe cast from bytea type to other data types
+SELECT CAST ('\x112233445566778899'::bytea AS int8 DEFAULT 19 ON CONVERSION ERROR);
+ int8
+------
+ 19
+(1 row)
+
+SELECT CAST('\x123456789A'::bytea AS int4 DEFAULT 20 ON CONVERSION ERROR);
+ int4
+------
+ 20
+(1 row)
+
+SELECT CAST('\x123456'::bytea AS int2 DEFAULT 21 ON CONVERSION ERROR);
+ int2
+------
+ 21
+(1 row)
+
+SELECT CAST('\x1234567890abcdef'::bytea AS uuid DEFAULT NULL ON CONVERSION ERROR);
+ uuid
+------
+
+(1 row)
+
+-- safe cast from uuid type to other data types
+SELECT CAST('5b35380a-7143-4912-9b55-f322699c6770'::uuid AS bytea DEFAULT NULL ON CONVERSION ERROR);
+ bytea
+------------------------------------
+ \x5b35380a714349129b55f322699c6770
+(1 row)
+
+-- safe cast related to network address data type
+SELECT CAST('192.168.1.x' as inet DEFAULT NULL ON CONVERSION ERROR);
+ inet
+------
+
+(1 row)
+
+SELECT CAST('192.168.1.2/30' as cidr DEFAULT NULL ON CONVERSION ERROR);
+ cidr
+------
+
+(1 row)
+
+SELECT CAST('22:00:5c:08:55:08:01:02'::macaddr8 as macaddr DEFAULT NULL ON CONVERSION ERROR);
+ macaddr
+---------
+
+(1 row)
+
+-- safe cast from bit type to other data types
+SELECT CAST('111111111100001'::bit(100) AS INT DEFAULT 22 ON CONVERSION ERROR);
+ int4
+------
+ 22
+(1 row)
+
+SELECT CAST ('111111111100001'::bit(100) AS INT8 DEFAULT 23 ON CONVERSION ERROR);
+ int8
+------
+ 23
+(1 row)
+
+-- safe cast related to oid data type
+SELECT CAST(12345678901::bigint AS oid DEFAULT NULL ON CONVERSION ERROR);
+ oid
+-----
+
+(1 row)
+
+SELECT CAST(12345678901::bigint AS regproc DEFAULT NULL ON CONVERSION ERROR);
+ regproc
+---------
+
+(1 row)
+
+SELECT CAST(12345678901::bigint AS regprocedure DEFAULT NULL ON CONVERSION ERROR);
+ regprocedure
+--------------
+
+(1 row)
+
+SELECT CAST(12345678901::bigint AS regclass DEFAULT NULL ON CONVERSION ERROR);
+ regclass
+----------
+
+(1 row)
+
+SELECT CAST(1234567890::bigint AS oid DEFAULT NULL ON CONVERSION ERROR);
+ oid
+------------
+ 1234567890
+(1 row)
+
+SELECT CAST(1234567890::bigint AS regrole DEFAULT NULL ON CONVERSION ERROR);
+ regrole
+------------
+ 1234567890
+(1 row)
+
+SELECT CAST(1234567890::bigint AS regnamespace DEFAULT NULL ON CONVERSION ERROR);
+ regnamespace
+--------------
+ 1234567890
+(1 row)
+
+SELECT CAST(1234567890::bigint AS regdatabase DEFAULT NULL ON CONVERSION ERROR);
+ regdatabase
+-------------
+ 1234567890
+(1 row)
+
+-- safe cast from text type to other data types
+SELECT CAST('a.b.c.d'::text as regclass DEFAULT NULL ON CONVERSION ERROR);
+ regclass
+----------
+
+(1 row)
+
+CREATE TABLE test_safecast0(col0 text, col1 jsonb);
+INSERT INTO test_safecast0 VALUES ('<value>one</value', '"test"');
+SELECT col0 as text,
+ CAST(col0 AS regclass DEFAULT NULL ON CONVERSION ERROR) as to_regclass,
+ CAST(col0 AS regclass DEFAULT NULL ON CONVERSION ERROR) IS NULL as expect_true,
+ CAST(col0 AS "char" DEFAULT NULL ON CONVERSION ERROR) as to_char,
+ CAST(col0 AS name DEFAULT NULL ON CONVERSION ERROR) as to_name
+FROM test_safecast0;
+ text | to_regclass | expect_true | to_char | to_name
+-------------------+-------------+-------------+---------+-------------------
+ <value>one</value | | t | < | <value>one</value
+(1 row)
+
+-- safe type cast for jsonb data type
+SELECT col1 as jsonb,
+ CAST(col1 AS integer DEFAULT NULL ON CONVERSION ERROR) as to_integer,
+ CAST(col1 AS numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col1 AS bigint DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col1 AS float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col1 AS float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col1 AS boolean DEFAULT NULL ON CONVERSION ERROR) as to_bool,
+ CAST(col1 AS smallint DEFAULT NULL ON CONVERSION ERROR) as to_smallint
+FROM test_safecast0;
+ jsonb | to_integer | to_numeric | to_int8 | to_float4 | to_float8 | to_bool | to_smallint
+--------+------------+------------+---------+-----------+-----------+---------+-------------
+ "test" | | | | | | |
+(1 row)
+
+-- test CAST DEFAULT expression mutability
+CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as int DEFAULT random(min=>1, max=>1) ON CONVERSION ERROR))); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as ...
+ ^
+CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as xid DEFAULT NULL ON CONVERSION ERROR))); -- error
+ERROR: data type xid has no default operator class for access method "btree"
+HINT: You must specify an operator class for the index or define a default operator class for the data type.
+CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as int DEFAULT NULL ON CONVERSION ERROR))); -- ok
+SELECT pg_get_indexdef('cast_error_idx'::regclass);
+ pg_get_indexdef
+--------------------------------------------------------------------------------------------------------------------------------------
+ CREATE INDEX cast_error_idx ON public.test_safecast0 USING btree ((CAST(col1 AS integer DEFAULT NULL::integer ON CONVERSION ERROR)))
+(1 row)
+
+-- safe cast betweeen range data types
+SELECT CAST('[1,2]'::int4range AS int4multirange DEFAULT NULL ON CONVERSION ERROR);
+ int4multirange
+----------------
+ {[1,3)}
+(1 row)
+
+SELECT CAST('[1,2]'::int8range AS int8multirange DEFAULT NULL ON CONVERSION ERROR);
+ int8multirange
+----------------
+ {[1,3)}
+(1 row)
+
+SELECT CAST('[1,2]'::numrange AS nummultirange DEFAULT NULL ON CONVERSION ERROR);
+ nummultirange
+---------------
+ {[1,2]}
+(1 row)
+
+SELECT CAST('[-infinity,infinity]'::daterange AS datemultirange DEFAULT NULL ON CONVERSION ERROR);
+ datemultirange
+------------------------
+ {[-infinity,infinity]}
+(1 row)
+
+SELECT CAST('[-infinity,infinity]'::tsrange AS tsmultirange DEFAULT NULL ON CONVERSION ERROR);
+ tsmultirange
+------------------------
+ {[-infinity,infinity]}
+(1 row)
+
+SELECT CAST('[-infinity,infinity]'::tstzrange AS tstzmultirange DEFAULT NULL ON CONVERSION ERROR);
+ tstzmultirange
+------------------------
+ {[-infinity,infinity]}
+(1 row)
+
+-- safe cast betweeen numeric data types
+CREATE TABLE test_safecast1(
+ col1 float4, col2 float8, col3 numeric, col4 numeric[],
+ col5 int2 default 32767,
+ col6 int4 default 32768,
+ col7 int8 default 4294967296);
+INSERT INTO test_safecast1 VALUES('11.1234', '11.1234', '9223372036854775808', '{11.1234}'::numeric[]);
+INSERT INTO test_safecast1 VALUES('inf', 'inf', 'inf', '{11.1234, 12, inf, NaN}'::numeric[]);
+INSERT INTO test_safecast1 VALUES('-inf', '-inf', '-inf', '{11.1234, 12, -inf, NaN}'::numeric[]);
+INSERT INTO test_safecast1 VALUES('NaN', 'NaN', 'NaN', '{11.1234, 12, -inf, NaN}'::numeric[]);
+SELECT col5 as int2,
+ CAST(col5 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col5 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col5 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col5 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col5 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col5 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col5 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col5 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+ int2 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale
+-------+---------+---------+--------+---------+-----------+-----------+------------+------------------
+ 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0
+ 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0
+ 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0
+ 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0
+(4 rows)
+
+SELECT col6 as int4,
+ CAST(col6 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col6 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col6 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col6 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col6 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col6 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col6 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col6 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+ int4 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale
+-------+---------+---------+--------+---------+-----------+-----------+------------+------------------
+ 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0
+ 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0
+ 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0
+ 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0
+(4 rows)
+
+SELECT col7 as int8,
+ CAST(col7 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col7 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col7 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col7 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col7 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col7 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col7 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col7 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+ int8 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale
+------------+---------+---------+--------+------------+-------------+------------+------------+------------------
+ 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 |
+ 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 |
+ 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 |
+ 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 |
+(4 rows)
+
+SELECT col3 as numeric,
+ CAST(col3 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col3 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col3 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col3 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col3 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col3 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col3 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col3 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+ numeric | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale
+---------------------+---------+---------+--------+---------+-------------+----------------------+---------------------+------------------
+ 9223372036854775808 | | | | | 9.22337e+18 | 9.22337203685478e+18 | 9223372036854775808 |
+ Infinity | | | | | Infinity | Infinity | Infinity |
+ -Infinity | | | | | -Infinity | -Infinity | -Infinity |
+ NaN | | | | | NaN | NaN | NaN | NaN
+(4 rows)
+
+SELECT col4 as num_arr,
+ CAST(col4 AS int2[] DEFAULT NULL ON CONVERSION ERROR) as int2arr,
+ CAST(col4 AS int4[] DEFAULT NULL ON CONVERSION ERROR) as int4arr,
+ CAST(col4 as int8[] DEFAULT NULL ON CONVERSION ERROR) as int8arr,
+ CAST(col4 as float4[] DEFAULT NULL ON CONVERSION ERROR) as f4arr,
+ CAST(col4 as float8[] DEFAULT NULL ON CONVERSION ERROR) as f8arr,
+ CAST(col4 as numeric(10,1)[] DEFAULT NULL ON CONVERSION ERROR) as numarr
+FROM test_safecast1;
+ num_arr | int2arr | int4arr | int8arr | f4arr | f8arr | numarr
+----------------------------+---------+---------+---------+----------------------------+----------------------------+--------
+ {11.1234} | {11} | {11} | {11} | {11.1234} | {11.1234} | {11.1}
+ {11.1234,12,Infinity,NaN} | | | | {11.1234,12,Infinity,NaN} | {11.1234,12,Infinity,NaN} |
+ {11.1234,12,-Infinity,NaN} | | | | {11.1234,12,-Infinity,NaN} | {11.1234,12,-Infinity,NaN} |
+ {11.1234,12,-Infinity,NaN} | | | | {11.1234,12,-Infinity,NaN} | {11.1234,12,-Infinity,NaN} |
+(4 rows)
+
+SELECT col1 as float4,
+ CAST(col1 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col1 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col1 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col1 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col1 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col1 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col1 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col1 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+ float4 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale
+-----------+---------+---------+--------+---------+-----------+------------------+------------+------------------
+ 11.1234 | 11 | 11 | | 11 | 11.1234 | 11.1233997344971 | 11.1234 | 11.1
+ Infinity | | | | | Infinity | Infinity | Infinity |
+ -Infinity | | | | | -Infinity | -Infinity | -Infinity |
+ NaN | | | | | NaN | NaN | NaN | NaN
+(4 rows)
+
+SELECT col2 as float8,
+ CAST(col2 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col2 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col2 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col2 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col2 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col2 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col2 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col2 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+ float8 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale
+-----------+---------+---------+--------+---------+-----------+-----------+------------+------------------
+ 11.1234 | 11 | 11 | | 11 | 11.1234 | 11.1234 | 11.1234 | 11.1
+ Infinity | | | | | Infinity | Infinity | Infinity |
+ -Infinity | | | | | -Infinity | -Infinity | -Infinity |
+ NaN | | | | | NaN | NaN | NaN | NaN
+(4 rows)
+
+-- safe type cast for type date/timestamp/timestamptz/interval
+CREATE TABLE test_safecast2(
+ col0 date, col1 timestamp, col2 timestamptz,
+ col3 interval, col4 time, col5 timetz);
+INSERT INTO test_safecast2 VALUES
+('-infinity', '-infinity', '-infinity', '-infinity',
+ '2003-03-07 15:36:39 America/New_York', '2003-07-07 15:36:39 America/New_York'),
+('-infinity', 'infinity', 'infinity', 'infinity', '11:59:59.99 PM', '11:59:59.99 PM PDT');
+SELECT col0 as date,
+ CAST(col0 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col0 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col0 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col0 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col0 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale
+FROM test_safecast2;
+ date | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale
+-----------+----------------+-----------+----------+-----------+--------------------
+ -infinity | -infinity | -infinity | | | -infinity
+ -infinity | -infinity | -infinity | | | -infinity
+(2 rows)
+
+SELECT col1 as timestamp,
+ CAST(col1 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col1 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col1 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col1 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col1 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale
+FROM test_safecast2;
+ timestamp | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale
+-----------+----------------+-----------+----------+-----------+--------------------
+ -infinity | -infinity | -infinity | | | -infinity
+ infinity | infinity | infinity | | | infinity
+(2 rows)
+
+SELECT col2 as timestamptz,
+ CAST(col2 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col2 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col2 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col2 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col2 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale
+FROM test_safecast2;
+ timestamptz | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale
+-------------+----------------+-----------+----------+-----------+--------------------
+ -infinity | -infinity | -infinity | | | -infinity
+ infinity | infinity | infinity | | | infinity
+(2 rows)
+
+SELECT col3 as interval,
+ CAST(col3 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col3 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col3 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col3 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col3 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale,
+ CAST(col3 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale
+FROM test_safecast2;
+ interval | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale | to_interval_scale
+-----------+----------------+---------+----------+-----------+--------------------+-------------------
+ -infinity | | | | | | -infinity
+ infinity | | | | | | infinity
+(2 rows)
+
+SELECT col4 as time,
+ CAST(col4 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col4 AS timetz DEFAULT NULL ON CONVERSION ERROR) IS NOT NULL as to_timetz,
+ CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval,
+ CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale
+FROM test_safecast2;
+ time | to_times | to_timetz | to_interval | to_interval_scale
+-------------+-------------+-----------+-------------------------------+-------------------------------
+ 15:36:39 | 15:36:39 | t | @ 15 hours 36 mins 39 secs | @ 15 hours 36 mins 39 secs
+ 23:59:59.99 | 23:59:59.99 | t | @ 23 hours 59 mins 59.99 secs | @ 23 hours 59 mins 59.99 secs
+(2 rows)
+
+SELECT col5 as timetz,
+ CAST(col5 AS time DEFAULT NULL ON CONVERSION ERROR) as to_time,
+ CAST(col5 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_time_scale,
+ CAST(col5 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col5 AS timetz(6) DEFAULT NULL ON CONVERSION ERROR) as to_timetz_scale,
+ CAST(col5 AS interval DEFAULT NULL ON CONVERSION ERROR) as to_interval,
+ CAST(col5 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale
+FROM test_safecast2;
+ timetz | to_time | to_time_scale | to_timetz | to_timetz_scale | to_interval | to_interval_scale
+----------------+-------------+---------------+----------------+-----------------+-------------+-------------------
+ 15:36:39-04 | 15:36:39 | 15:36:39 | 15:36:39-04 | 15:36:39-04 | |
+ 23:59:59.99-07 | 23:59:59.99 | 23:59:59.99 | 23:59:59.99-07 | 23:59:59.99-07 | |
+(2 rows)
+
+-- test deparse
+SET datestyle TO ISO, YMD;
+CREATE VIEW safecastview AS
+SELECT CAST('1234' as char(3) DEFAULT -1111 ON CONVERSION ERROR),
+ CAST(1 as date DEFAULT (('2025-Dec-06'::date + random(min=>1, max=>1::int))) ON CONVERSION ERROR) as cast0,
+ CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast1,
+ CAST(ARRAY[['1', '2'], ['three', 'a']] AS date[] DEFAULT NULL ON CONVERSION ERROR) as cast2,
+ CAST(ARRAY['three'] AS INT[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast3;
+\sv safecastview
+CREATE OR REPLACE VIEW public.safecastview AS
+ SELECT CAST('1234' AS character(3) DEFAULT '-1111'::integer::character(3) ON CONVERSION ERROR) AS bpchar,
+ CAST(1 AS date DEFAULT '2025-12-06'::date + random(min => 1, max => 1) ON CONVERSION ERROR) AS cast0,
+ CAST(ARRAY[ARRAY[1], ARRAY['three'], ARRAY['a']] AS integer[] DEFAULT '{1,2}'::integer[] ON CONVERSION ERROR) AS cast1,
+ CAST(ARRAY[ARRAY['1', '2'], ARRAY['three', 'a']] AS date[] DEFAULT NULL::date[] ON CONVERSION ERROR) AS cast2,
+ CAST(ARRAY['three'] AS integer[] DEFAULT '{1,2}'::integer[] ON CONVERSION ERROR) AS cast3
+SELECT * FROM safecastview;
+ bpchar | cast0 | cast1 | cast2 | cast3
+--------+------------+-------+-------+-------
+ 123 | 2025-12-07 | {1,2} | | {1,2}
+(1 row)
+
+CREATE VIEW safecastview1 AS
+SELECT CAST(ARRAY[['1'], ['three'],['a']] AS d_int_arr
+ DEFAULT '{41,43}' ON CONVERSION ERROR) as cast1,
+ CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr
+ DEFAULT '{41,43}' ON CONVERSION ERROR) as cast2;
+\sv safecastview1
+CREATE OR REPLACE VIEW public.safecastview1 AS
+ SELECT CAST(ARRAY[ARRAY[1], ARRAY['three'], ARRAY['a']] AS d_int_arr DEFAULT '{41,43}'::integer[]::d_int_arr ON CONVERSION ERROR) AS cast1,
+ CAST(ARRAY[ARRAY[1, 2, 1.1::integer], ARRAY['three', true, '01'::"bit"]] AS d_int_arr DEFAULT '{41,43}'::integer[]::d_int_arr ON CONVERSION ERROR) AS cast2
+SELECT * FROM safecastview1;
+ cast1 | cast2
+---------+---------
+ {41,43} | {41,43}
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('1' || 'a' AS int DEFAULT 2 ON CONVERSION ERROR);
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Result
+ Output: CAST(('1'::text || 'a'::text) AS integer DEFAULT 2 ON CONVERSION ERROR)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR);
+ QUERY PLAN
+--------------------------------------------------------------
+ Result
+ Output: CAST('a' AS integer DEFAULT 2 ON CONVERSION ERROR)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr DEFAULT NULL ON CONVERSION ERROR);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Result
+ Output: CAST(ARRAY[ARRAY[1, 2, (1.1)::integer], ARRAY['three', true, '01'::"bit"]] AS d_int_arr DEFAULT (NULL::integer[])::d_int_arr ON CONVERSION ERROR)
+(2 rows)
+
+RESET datestyle;
+DROP VIEW safecastview;
+DROP VIEW safecastview1;
+DROP TABLE test_safecast0;
+DROP TABLE test_safecast1;
+DROP TABLE test_safecast2;
+DROP TABLE tcast;
+DROP TABLE tcast1;
+DROP FUNCTION ret_int8;
+DROP FUNCTION ret_setint;
+DROP TYPE comp_domain_with_typmod;
+DROP TYPE comp2;
+DROP DOMAIN d_nota;
+DROP DOMAIN d_varchar;
+DROP DOMAIN d_int8arr;
+DROP DOMAIN d_int8;
+DROP DOMAIN d_int42arr1;
+DROP DOMAIN d_int42arr;
+DROP DOMAIN d_int42;
+DROP DOMAIN d_char3_not_null;
+DROP DOMAIN d_numeric;
+DROP DOMAIN ddomain;
+RESET extra_float_digits;
+RESET lc_monetary;
diff --git a/src/test/regress/expected/create_cast.out b/src/test/regress/expected/create_cast.out
index 0e69644bca2..64ac60b157f 100644
--- a/src/test/regress/expected/create_cast.out
+++ b/src/test/regress/expected/create_cast.out
@@ -88,6 +88,11 @@ SELECT 1234::int4::casttesttype; -- Should work now
bar1234
(1 row)
+SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVERSION ERROR); -- error
+ERROR: cannot cast type integer to casttesttype with DEFAULT expression in CAST ... ON CONVERSION ERROR
+LINE 1: SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVE...
+ ^
+DETAIL: Safe type casts for user-defined types are not yet supported.
-- check dependencies generated for that
SELECT pg_describe_object(classid, objid, objsubid) as obj,
pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index ad8ab294ff6..3af737c220a 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -95,6 +95,25 @@ create function int8alias1cmp(int8, int8alias1) returns int
strict immutable language internal as 'btint8cmp';
alter operator family integer_ops using btree add
function 1 int8alias1cmp (int8, int8alias1);
+-- int8alias2 binary-coercible to int8, this should not fail
+select cast('1'::int8 as int8alias2 default null on conversion error);
+ int8alias2
+------------
+ 1
+(1 row)
+
+select cast('1'::int8alias2 as int8 default null on conversion error);
+ int8
+------
+ 1
+(1 row)
+
+select cast('a' as int8alias2 default null on conversion error);
+ int8alias2
+------------
+
+(1 row)
+
create table ec0 (ff int8 primary key, f1 int8, f2 int8);
create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);
create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index fb3e0ec41b2..f4fc64c1a92 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -24,6 +24,12 @@ SELECT * FROM xmltest;
2 | <value>two</value>
(2 rows)
+SELECT CAST('<wrong'::text AS xml DEFAULT NULL ON CONVERSION ERROR) as to_xml;
+ to_xml
+--------
+
+(1 row)
+
-- test non-throwing API, too
SELECT pg_input_is_valid('<value>one</value>', 'xml');
pg_input_is_valid
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8fa0a6c47fb..b6ef7ccecb2 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -115,7 +115,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
# NB: temp.sql does reconnects which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml cast
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/cast.sql b/src/test/regress/sql/cast.sql
new file mode 100644
index 00000000000..776e9ab8e0b
--- /dev/null
+++ b/src/test/regress/sql/cast.sql
@@ -0,0 +1,453 @@
+SET extra_float_digits = 0;
+SET lc_monetary TO "C";
+
+-- CAST DEFAULT ON CONVERSION ERROR
+SELECT CAST(B'01' AS date DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(BIT'01' AS date DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(TRUE AS date DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(1.1 AS date DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(1111 AS "char" DEFAULT 'A' ON CONVERSION ERROR);
+SELECT CAST('def'::text AS integer DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('2'::jsonb AS text DEFAULT 'fallback' ON CONVERSION ERROR);
+SELECT CAST('1' COLLATE "C" || 'h' AS text DEFAULT 'fallback' ON CONVERSION ERROR);
+SELECT CAST(concat('1' collate "C", 'h') AS int DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(65536 AS int2 DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(int8(65536) AS int2 DEFAULT NULL ON CONVERSION ERROR);
+
+-- source expression is a unknown const
+VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR)); -- error
+VALUES (CAST('error' AS integer DEFAULT NULL ON CONVERSION ERROR));
+VALUES (CAST('error' AS integer DEFAULT 42 ON CONVERSION ERROR));
+SELECT CAST('a' as int DEFAULT 18 ON CONVERSION ERROR);
+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); -- error
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); -- error
+SELECT CAST('a' as int DEFAULT (SELECT NULL) ON CONVERSION ERROR); -- error
+SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR); -- error
+SELECT CAST('a'::int as int DEFAULT NULL ON CONVERSION ERROR); -- error
+
+-- the default expression’s collation should match target type collation
+VALUES (CAST('error' AS text DEFAULT '1' COLLATE "C" ON CONVERSION ERROR));
+
+VALUES (CAST('error' AS int2vector DEFAULT '1 3' ON CONVERSION ERROR));
+VALUES (CAST('error' AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR));
+
+-- source expression contain subquery
+SELECT CAST((SELECT b FROM generate_series(1, 1), (VALUES('H')) s(b))
+ AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR);
+SELECT CAST((SELECT ARRAY((SELECT b FROM generate_series(1, 2) g, (VALUES('H')) s(b))))
+ AS INT[]
+ DEFAULT NULL ON CONVERSION ERROR);
+
+CREATE FUNCTION ret_int8() RETURNS BIGINT AS
+$$
+BEGIN RETURN 2147483648; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+SELECT CAST('a' as int DEFAULT ret_int8() ON CONVERSION ERROR); -- error
+SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERROR); -- error
+
+-- DEFAULT expression cannot be set-returning
+CREATE FUNCTION ret_setint() RETURNS SETOF integer AS
+$$
+BEGIN RETURN QUERY EXECUTE 'select 1 union all select 1'; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+CREATE TABLE tcast0(a INT, b int);
+INSERT INTO tcast0 VALUES(1,2), (65536,3);
+SET jit_above_cost = 0;
+SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0;
+SET jit_above_cost TO DEFAULT;
+SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0;
+SELECT CAST(ROW(1,2) AS tcast0 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0 as t;
+DROP TABLE tcast0;
+
+SELECT CAST(12.111 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale;
+
+CREATE TABLE tcast(a text[], b int GENERATED BY DEFAULT AS IDENTITY);
+INSERT INTO tcast VALUES ('{12}'), ('{1,a, b}'), ('{{1,2}, {c,d}}'), ('{13}');
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) FROM tcast; -- error
+SELECT CAST(t AS text[] DEFAULT '{21,22, ' || b || '}' ON CONVERSION ERROR) FROM tcast as t;
+SELECT CAST(t.a AS int[] DEFAULT '{21,22}'::int[] || b ON CONVERSION ERROR) FROM tcast as t;
+
+CREATE DOMAIN ddomain AS numeric CHECK ((1/ (VALUE - 1)) > 0);
+SELECT CAST ('1' AS ddomain DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR);
+CREATE VIEW vddomain AS SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR);
+\sv vddomain
+DROP VIEW vddomain;
+
+-- Test nested type casts; all sub-expressions will be evaluated in an error-safe manner too.
+CREATE DOMAIN d_nota AS text CHECK (VALUE <> 'a');
+CREATE TABLE tcast1(a text, b text[], c int, d int8, e int8[]);
+INSERT INTO tcast1(a, b, d, e) values('a', '{a}', 2147483648, '{2147483648}');
+SELECT CAST (CAST(a AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST (CAST('a' AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- error
+SELECT CAST (CAST('a'::text AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- ok
+SELECT CAST (CAST(a AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST (CAST('a' AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+
+SELECT CAST (CAST(d AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST (CAST('2147483648'::int8 AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+
+SELECT CAST (CAST('{a}'::text[] AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST (CAST(b AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST (CAST(b AS d_nota[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST (CAST(e AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+
+-- test nested ArrayCoerceExpr constant folding
+SELECT CAST('{a}'::text[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST(CAST('{a}'::text[] AS date[]) AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1;
+SELECT CAST(('{' || sum(1 + 2) || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub;
+SELECT CAST(('{' || sum(1 + 2) OVER () || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub;
+
+-- test with user-defined type, domain, array over domain, domain over array
+CREATE DOMAIN d_int42 as int check (value = 42) NOT NULL;
+CREATE DOMAIN d_int42arr as d_int42[];
+CREATE DOMAIN d_int42arr1 as d_int42arr;
+CREATE DOMAIN d_int8 as int8 check (value > 0);
+CREATE DOMAIN d_int8arr as d_int8[];
+CREATE DOMAIN d_char3_not_null as char(3) NOT NULL;
+CREATE DOMAIN d_varchar as varchar(3) NOT NULL;
+CREATE DOMAIN d_int_arr as int[] check (value = '{41, 43}') NOT NULL;
+CREATE TYPE comp_domain_with_typmod AS (a d_char3_not_null, b int);
+CREATE TYPE comp2 AS (a d_varchar);
+CREATE DOMAIN d_numeric as numeric(4,4);
+
+SELECT CAST(1.0::float4 AS d_numeric DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('{1.0}' AS numeric(4,1)[] DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('{0.1, 1.0}' AS d_numeric[] DEFAULT NULL ON CONVERSION ERROR);
+
+SELECT CAST(11 AS d_int42 DEFAULT 41 ON CONVERSION ERROR); -- error
+SELECT CAST(NULL AS d_int42 DEFAULT NULL ON CONVERSION ERROR); -- error
+SELECT CAST(11 AS d_int42 DEFAULT 42 ON CONVERSION ERROR);
+SELECT CAST(NULL AS d_int42 DEFAULT 42 ON CONVERSION ERROR);
+
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8 DEFAULT '1' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8[] DEFAULT '{1}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr DEFAULT '{1}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr[] DEFAULT NULL ON CONVERSION ERROR);
+
+SELECT CAST(ARRAY[42,41] AS d_int42[] DEFAULT '{42, 42}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[42,41] AS d_int_arr DEFAULT '{41, 43}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[42, 41]::d_int42arr1 AS d_int8arr DEFAULT '{1,2,3}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[42, 41] AS d_int42arr1 DEFAULT '{42, 42}' ON CONVERSION ERROR);
+SELECT CAST(CAST(ARRAY[CAST(ARRAY[42, 42] AS d_int42arr)] AS d_int42arr1[])
+ AS d_int8arr[]
+ DEFAULT NULL ON CONVERSION ERROR);
+
+SELECT CAST('(NULL)' AS comp2 DEFAULT '(1232)' ON CONVERSION ERROR); -- error
+SELECT CAST('(NULL)' AS comp2 DEFAULT '(123)' ON CONVERSION ERROR);
+SELECT CAST('(,42)' AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR);
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)' ON CONVERSION ERROR); -- error
+SELECT CAST(ROW(NULL,42) AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR); -- error
+
+-- test array coerce
+SELECT CAST(array['a'::text] AS int[] DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(array['a'] AS int[] DEFAULT ARRAY[1] ON CONVERSION ERROR);
+SELECT CAST(array[11.12] AS date[] DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(array[11111111111111111] AS int[] DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(array[['abc'],[456]] AS int[] DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('{123,abc,456}' AS int[] DEFAULT '{-789}' ON CONVERSION ERROR);
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[['1', '2'], ['three', 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+-- error: `three'::int` will fail earlier
+SELECT CAST(ARRAY[['1', '2'], ['three'::int, 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[1, 'three'::int] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+
+-- safe cast with geometry data type
+SELECT CAST('(1,2)'::point AS box DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[(NaN,1),(NaN,infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[(1e+300,Infinity),(1e+300,Infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[(1,2),(3,4)]'::path as polygon DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(NaN,1.0,NaN,infinity)'::box AS point DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS lseg DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS polygon DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS path DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(2.0,infinity,NaN,infinity)'::box AS circle DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(NaN,0.0),(2.0,4.0),(0.0,infinity)'::polygon AS point DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS path DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS box DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(NaN,infinity),(2.0,4.0),(0.0,infinity)'::polygon AS circle DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('<(5,1),3>'::circle AS point DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('<(3,5),0>'::circle as box DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('<(3,5),0>'::circle as polygon DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast with money data type
+-- cast from money to other type is not error safe
+SELECT CAST('123456789012345678'::numeric AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('123456789012345678'::int8 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('2147483647'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('-2147483648'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('-0'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(NULL::money AS numeric DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(NULL::money[] AS numeric[] DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast from boolean type to other data types
+SELECT CAST(true AS numeric DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(false AS int DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(false AS text DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast from bytea type to other data types
+SELECT CAST ('\x112233445566778899'::bytea AS int8 DEFAULT 19 ON CONVERSION ERROR);
+SELECT CAST('\x123456789A'::bytea AS int4 DEFAULT 20 ON CONVERSION ERROR);
+SELECT CAST('\x123456'::bytea AS int2 DEFAULT 21 ON CONVERSION ERROR);
+SELECT CAST('\x1234567890abcdef'::bytea AS uuid DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast from uuid type to other data types
+SELECT CAST('5b35380a-7143-4912-9b55-f322699c6770'::uuid AS bytea DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast related to network address data type
+SELECT CAST('192.168.1.x' as inet DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('192.168.1.2/30' as cidr DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('22:00:5c:08:55:08:01:02'::macaddr8 as macaddr DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast from bit type to other data types
+SELECT CAST('111111111100001'::bit(100) AS INT DEFAULT 22 ON CONVERSION ERROR);
+SELECT CAST ('111111111100001'::bit(100) AS INT8 DEFAULT 23 ON CONVERSION ERROR);
+
+-- safe cast related to oid data type
+SELECT CAST(12345678901::bigint AS oid DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(12345678901::bigint AS regproc DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(12345678901::bigint AS regprocedure DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(12345678901::bigint AS regclass DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(1234567890::bigint AS oid DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(1234567890::bigint AS regrole DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(1234567890::bigint AS regnamespace DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST(1234567890::bigint AS regdatabase DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast from text type to other data types
+SELECT CAST('a.b.c.d'::text as regclass DEFAULT NULL ON CONVERSION ERROR);
+CREATE TABLE test_safecast0(col0 text, col1 jsonb);
+INSERT INTO test_safecast0 VALUES ('<value>one</value', '"test"');
+
+SELECT col0 as text,
+ CAST(col0 AS regclass DEFAULT NULL ON CONVERSION ERROR) as to_regclass,
+ CAST(col0 AS regclass DEFAULT NULL ON CONVERSION ERROR) IS NULL as expect_true,
+ CAST(col0 AS "char" DEFAULT NULL ON CONVERSION ERROR) as to_char,
+ CAST(col0 AS name DEFAULT NULL ON CONVERSION ERROR) as to_name
+FROM test_safecast0;
+
+-- safe type cast for jsonb data type
+SELECT col1 as jsonb,
+ CAST(col1 AS integer DEFAULT NULL ON CONVERSION ERROR) as to_integer,
+ CAST(col1 AS numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col1 AS bigint DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col1 AS float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col1 AS float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col1 AS boolean DEFAULT NULL ON CONVERSION ERROR) as to_bool,
+ CAST(col1 AS smallint DEFAULT NULL ON CONVERSION ERROR) as to_smallint
+FROM test_safecast0;
+
+-- test CAST DEFAULT expression mutability
+CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as int DEFAULT random(min=>1, max=>1) ON CONVERSION ERROR))); -- error
+CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as xid DEFAULT NULL ON CONVERSION ERROR))); -- error
+CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as int DEFAULT NULL ON CONVERSION ERROR))); -- ok
+SELECT pg_get_indexdef('cast_error_idx'::regclass);
+
+-- safe cast betweeen range data types
+SELECT CAST('[1,2]'::int4range AS int4multirange DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[1,2]'::int8range AS int8multirange DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[1,2]'::numrange AS nummultirange DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[-infinity,infinity]'::daterange AS datemultirange DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[-infinity,infinity]'::tsrange AS tsmultirange DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('[-infinity,infinity]'::tstzrange AS tstzmultirange DEFAULT NULL ON CONVERSION ERROR);
+
+-- safe cast betweeen numeric data types
+CREATE TABLE test_safecast1(
+ col1 float4, col2 float8, col3 numeric, col4 numeric[],
+ col5 int2 default 32767,
+ col6 int4 default 32768,
+ col7 int8 default 4294967296);
+INSERT INTO test_safecast1 VALUES('11.1234', '11.1234', '9223372036854775808', '{11.1234}'::numeric[]);
+INSERT INTO test_safecast1 VALUES('inf', 'inf', 'inf', '{11.1234, 12, inf, NaN}'::numeric[]);
+INSERT INTO test_safecast1 VALUES('-inf', '-inf', '-inf', '{11.1234, 12, -inf, NaN}'::numeric[]);
+INSERT INTO test_safecast1 VALUES('NaN', 'NaN', 'NaN', '{11.1234, 12, -inf, NaN}'::numeric[]);
+
+SELECT col5 as int2,
+ CAST(col5 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col5 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col5 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col5 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col5 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col5 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col5 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col5 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+
+SELECT col6 as int4,
+ CAST(col6 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col6 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col6 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col6 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col6 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col6 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col6 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col6 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+
+SELECT col7 as int8,
+ CAST(col7 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col7 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col7 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col7 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col7 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col7 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col7 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col7 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+
+SELECT col3 as numeric,
+ CAST(col3 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col3 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col3 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col3 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col3 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col3 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col3 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col3 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+
+SELECT col4 as num_arr,
+ CAST(col4 AS int2[] DEFAULT NULL ON CONVERSION ERROR) as int2arr,
+ CAST(col4 AS int4[] DEFAULT NULL ON CONVERSION ERROR) as int4arr,
+ CAST(col4 as int8[] DEFAULT NULL ON CONVERSION ERROR) as int8arr,
+ CAST(col4 as float4[] DEFAULT NULL ON CONVERSION ERROR) as f4arr,
+ CAST(col4 as float8[] DEFAULT NULL ON CONVERSION ERROR) as f8arr,
+ CAST(col4 as numeric(10,1)[] DEFAULT NULL ON CONVERSION ERROR) as numarr
+FROM test_safecast1;
+
+SELECT col1 as float4,
+ CAST(col1 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col1 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col1 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col1 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col1 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col1 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col1 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col1 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+
+SELECT col2 as float8,
+ CAST(col2 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2,
+ CAST(col2 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4,
+ CAST(col2 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid,
+ CAST(col2 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8,
+ CAST(col2 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4,
+ CAST(col2 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8,
+ CAST(col2 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric,
+ CAST(col2 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale
+FROM test_safecast1;
+
+-- safe type cast for type date/timestamp/timestamptz/interval
+CREATE TABLE test_safecast2(
+ col0 date, col1 timestamp, col2 timestamptz,
+ col3 interval, col4 time, col5 timetz);
+INSERT INTO test_safecast2 VALUES
+('-infinity', '-infinity', '-infinity', '-infinity',
+ '2003-03-07 15:36:39 America/New_York', '2003-07-07 15:36:39 America/New_York'),
+('-infinity', 'infinity', 'infinity', 'infinity', '11:59:59.99 PM', '11:59:59.99 PM PDT');
+
+SELECT col0 as date,
+ CAST(col0 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col0 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col0 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col0 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col0 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale
+FROM test_safecast2;
+
+SELECT col1 as timestamp,
+ CAST(col1 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col1 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col1 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col1 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col1 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale
+FROM test_safecast2;
+
+SELECT col2 as timestamptz,
+ CAST(col2 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col2 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col2 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col2 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col2 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale
+FROM test_safecast2;
+
+SELECT col3 as interval,
+ CAST(col3 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz,
+ CAST(col3 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date,
+ CAST(col3 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col3 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col3 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale,
+ CAST(col3 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale
+FROM test_safecast2;
+
+SELECT col4 as time,
+ CAST(col4 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_times,
+ CAST(col4 AS timetz DEFAULT NULL ON CONVERSION ERROR) IS NOT NULL as to_timetz,
+ CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval,
+ CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale
+FROM test_safecast2;
+
+SELECT col5 as timetz,
+ CAST(col5 AS time DEFAULT NULL ON CONVERSION ERROR) as to_time,
+ CAST(col5 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_time_scale,
+ CAST(col5 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz,
+ CAST(col5 AS timetz(6) DEFAULT NULL ON CONVERSION ERROR) as to_timetz_scale,
+ CAST(col5 AS interval DEFAULT NULL ON CONVERSION ERROR) as to_interval,
+ CAST(col5 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale
+FROM test_safecast2;
+
+-- test deparse
+SET datestyle TO ISO, YMD;
+CREATE VIEW safecastview AS
+SELECT CAST('1234' as char(3) DEFAULT -1111 ON CONVERSION ERROR),
+ CAST(1 as date DEFAULT (('2025-Dec-06'::date + random(min=>1, max=>1::int))) ON CONVERSION ERROR) as cast0,
+ CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast1,
+ CAST(ARRAY[['1', '2'], ['three', 'a']] AS date[] DEFAULT NULL ON CONVERSION ERROR) as cast2,
+ CAST(ARRAY['three'] AS INT[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast3;
+\sv safecastview
+SELECT * FROM safecastview;
+
+CREATE VIEW safecastview1 AS
+SELECT CAST(ARRAY[['1'], ['three'],['a']] AS d_int_arr
+ DEFAULT '{41,43}' ON CONVERSION ERROR) as cast1,
+ CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr
+ DEFAULT '{41,43}' ON CONVERSION ERROR) as cast2;
+\sv safecastview1
+SELECT * FROM safecastview1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('1' || 'a' AS int DEFAULT 2 ON CONVERSION ERROR);
+EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR);
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr DEFAULT NULL ON CONVERSION ERROR);
+
+RESET datestyle;
+
+DROP VIEW safecastview;
+DROP VIEW safecastview1;
+DROP TABLE test_safecast0;
+DROP TABLE test_safecast1;
+DROP TABLE test_safecast2;
+DROP TABLE tcast;
+DROP TABLE tcast1;
+DROP FUNCTION ret_int8;
+DROP FUNCTION ret_setint;
+DROP TYPE comp_domain_with_typmod;
+DROP TYPE comp2;
+DROP DOMAIN d_nota;
+DROP DOMAIN d_varchar;
+DROP DOMAIN d_int8arr;
+DROP DOMAIN d_int8;
+DROP DOMAIN d_int42arr1;
+DROP DOMAIN d_int42arr;
+DROP DOMAIN d_int42;
+DROP DOMAIN d_char3_not_null;
+DROP DOMAIN d_numeric;
+DROP DOMAIN ddomain;
+RESET extra_float_digits;
+RESET lc_monetary;
diff --git a/src/test/regress/sql/create_cast.sql b/src/test/regress/sql/create_cast.sql
index 32187853cc7..0a15a795d87 100644
--- a/src/test/regress/sql/create_cast.sql
+++ b/src/test/regress/sql/create_cast.sql
@@ -62,6 +62,7 @@ $$ SELECT ('bar'::text || $1::text); $$;
CREATE CAST (int4 AS casttesttype) WITH FUNCTION bar_int4_text(int4) AS IMPLICIT;
SELECT 1234::int4::casttesttype; -- Should work now
+SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVERSION ERROR); -- error
-- check dependencies generated for that
SELECT pg_describe_object(classid, objid, objsubid) as obj,
diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql
index 7fc2159349b..96e6f73c7b6 100644
--- a/src/test/regress/sql/equivclass.sql
+++ b/src/test/regress/sql/equivclass.sql
@@ -98,6 +98,11 @@ create function int8alias1cmp(int8, int8alias1) returns int
alter operator family integer_ops using btree add
function 1 int8alias1cmp (int8, int8alias1);
+-- int8alias2 binary-coercible to int8, this should not fail
+select cast('1'::int8 as int8alias2 default null on conversion error);
+select cast('1'::int8alias2 as int8 default null on conversion error);
+select cast('a' as int8alias2 default null on conversion error);
+
create table ec0 (ff int8 primary key, f1 int8, f2 int8);
create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);
create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index aafd39433a6..dbfec20a05e 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -16,6 +16,8 @@ SELECT count(*) = 0 AS skip_test FROM xmltest \gset
SELECT * FROM xmltest;
+SELECT CAST('<wrong'::text AS xml DEFAULT NULL ON CONVERSION ERROR) as to_xml;
+
-- test non-throwing API, too
SELECT pg_input_is_valid('<value>one</value>', 'xml');
SELECT pg_input_is_valid('<value>one</', 'xml');
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..69a005aa039 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2782,6 +2782,8 @@ STRLEN
SV
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
+SafeTypeCastExpr
+SafeTypeCastState
SampleScan
SampleScanGetSampleSize_function
SampleScanState
--
2.34.1
view thread (68+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
In-Reply-To: <CACJufxGqn5fQCPkUeRC4FQLMSFZ9vBMqq65zXmsRnp5OAMOkaA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox