On Tue, Mar 24, 2026 at 4:20 AM Corey Huinker <[email protected]> wrote:
>
> Surya and I did a pair-review of this. In addition to the notes above (which
> we agree with), we have the following notes:
>
> + if (inputBaseTypeId == targetBaseTypeId)
> + ereport(ERROR,
> + errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("cannot cast type %s to %s while using
> a format template",
> + format_type_be(inputBaseTypeId),
> + format_type_be(targetBaseTypeId)),
> + errdetail("binary coercible type cast is not
> supported while using a format template"),
> + parser_coercion_errposition(pstate, location,
> node));
>
> This could use a bit more explanation in a comment - is it because there is
> no plausible type that can take a FORMAT and be cast to itself?
>
We might be able to do it in the future; it's currently not allowed.
I have used the ERRCODE_FEATURE_NOT_SUPPORTED error code.
We should not use
``> + if (inputBaseTypeId == targetBaseTypeId)``
We need to use IsBinaryCoercible.
src1=# SELECT CAST('52'::int2 as numeric FORMAT '9 9 9 9 9 9 . 9 9');
ERROR: function pg_catalog.to_number(smallint, text) does not exist
DETAIL: No function of that name accepts the given argument types.
HINT: You might need to add explicit type casts.
The above CAST FORMAT error message is not ideal, therefore we need
stricter type restrictions for source and target data types,
and more type checking.
The target type must exactly match the function's result type.
to_char, to_date, to_number, to_timestamp.
> + if (s_typcategory != TYPCATEGORY_NUMERIC &&
> + s_typcategory != TYPCATEGORY_STRING &&
> + s_typcategory != TYPCATEGORY_DATETIME &&
> + s_typcategory != TYPCATEGORY_TIMESPAN)
>
> In situations like this, the committers have shown a strong preference for
> switch() statements. Though it may make more sense to package this if into a
> static function is_formattable_type() or similar.
>
we need more restriction on CAST FORMAT source type and target type.
pg_type->typcategory is not reliable for disallowing source/target types,
So, I use switch() to enumerate all allowed data types and the
switch() default branch handles ereport(ERROR).
--
jian
https://www.enterprisedb.com/
From 28c00853b0fa76d08a9d0503eeccaa94feb6e2f3 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 30 Mar 2026 16:00:00 +0800
Subject: [PATCH v6 1/1] CAST(expr AS type FORMAT 'template')
This enables the CAST(expression AS type FORMAT template) syntax.
For Binary-coercible casts: Specifying a format template for binary-coercible
casts (e.g., text to text) will now raise an error.
No pg_cast entries have been modified at this stage. Adding these
formatting functions to pg_cast has complex implications requiring further
discussion (see [1]) and is not strictly necessary to achieve the current
behavior.
Under the hood, CAST FORMAT is transformed into a FuncExpr node. Since only
function to_char, to_date, to_number, and to_timestamp support formatting, this
feature is currently limited to the input and result types compatible with these
functions.
[1]: https://postgr.es/m/CACJufxF4OW=x2rcwa+zmcgopdwgkdxha09qtftpcj3qstg6...@mail.gmail.com
context: https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Major_features_simply_not_implemented_yet
discussion: https://postgr.es/m/CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=nolh_p6uuyes...@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/5957
---
src/backend/nodes/nodeFuncs.c | 2 +
src/backend/parser/gram.y | 19 +
src/backend/parser/parse_agg.c | 11 +
src/backend/parser/parse_coerce.c | 326 +++++++++++++++++-
src/backend/parser/parse_expr.c | 26 +-
src/backend/parser/parse_func.c | 3 +
src/backend/parser/parse_utilcmd.c | 1 +
src/backend/utils/adt/ruleutils.c | 20 ++
src/include/nodes/parsenodes.h | 1 +
src/include/parser/parse_coerce.h | 11 +
src/include/parser/parse_node.h | 1 +
src/test/regress/expected/cast.out | 312 +++++++++++++++++
.../regress/expected/collate.linux.utf8.out | 39 +++
src/test/regress/expected/horology.out | 130 +++++++
src/test/regress/expected/interval.out | 12 +
src/test/regress/expected/numeric.out | 147 +++++++-
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/cast.sql | 107 ++++++
src/test/regress/sql/collate.linux.utf8.sql | 8 +
src/test/regress/sql/horology.sql | 42 +++
src/test/regress/sql/interval.sql | 2 +
src/test/regress/sql/numeric.sql | 26 +-
22 files changed, 1234 insertions(+), 14 deletions(-)
create mode 100644 src/test/regress/expected/cast.out
create mode 100644 src/test/regress/sql/cast.sql
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 6a850349cf7..2e33c92345d 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4502,6 +4502,8 @@ raw_expression_tree_walker_impl(Node *node,
if (WALK(tc->arg))
return true;
+ if (WALK(tc->format))
+ return true;
if (WALK(tc->typeName))
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0fea726cdd5..e3d042cd8ae 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -157,6 +157,9 @@ static RawStmt *makeRawStmt(Node *stmt, int stmt_location);
static void updateRawStmtEnd(RawStmt *rs, int end_location);
static Node *makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner);
+static Node *makeFormattedTypeCast(Node *arg, TypeName *typename,
+ Node *format,
+ int location);
static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
static Node *makeStringConstCast(char *str, int location, TypeName *typename);
static Node *makeIntConst(int val, int location);
@@ -16687,6 +16690,8 @@ func_expr_common_subexpr:
}
| CAST '(' a_expr AS Typename ')'
{ $$ = makeTypeCast($3, $5, @1); }
+ | CAST '(' a_expr AS Typename FORMAT a_expr ')'
+ { $$ = makeFormattedTypeCast($3, $5, $7, @1); }
| EXTRACT '(' extract_list ')'
{
$$ = (Node *) makeFuncCall(SystemFuncName("extract"),
@@ -19841,12 +19846,26 @@ makeColumnRef(char *colname, List *indirection,
return (Node *) c;
}
+static Node *
+makeFormattedTypeCast(Node *arg, TypeName *typename, Node *format, int location)
+{
+ TypeCast *n = makeNode(TypeCast);
+
+ n->arg = arg;
+ n->typeName = typename;
+ n->format = format;
+ n->location = location;
+
+ return (Node *) n;
+}
+
static Node *
makeTypeCast(Node *arg, TypeName *typename, int location)
{
TypeCast *n = makeNode(TypeCast);
n->arg = arg;
+ n->format = NULL;
n->typeName = typename;
n->location = location;
return (Node *) n;
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 6076e9373c1..5beec4b4518 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -593,6 +593,14 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
break;
+ case EXPR_KIND_TYPECAST_FORMAT:
+ if (isAgg)
+ err = _("aggregate functions are not allowed in CAST FORMAT expressions");
+ else
+ err = _("grouping operations are not allowed in CAST FORMAT expressions");
+
+ break;
+
/*
* There is intentionally no default: case here, so that the
* compiler will warn if we add a new ParseExprKind without
@@ -1035,6 +1043,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_PROPGRAPH_PROPERTY:
err = _("window functions are not allowed in property definition expressions");
break;
+ case EXPR_KIND_TYPECAST_FORMAT:
+ err = _("window functions are not allowed in CAST FORMAT expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 913ca53666f..02e8dda993a 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -23,6 +23,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_coerce.h"
+#include "parser/parse_func.h"
#include "parser/parse_relation.h"
#include "parser/parse_type.h"
#include "utils/builtins.h"
@@ -74,6 +75,8 @@ static bool typeIsOfTypedTable(Oid reltypeId, Oid reloftypeId);
* targettypmod - desired result typmod
* ccontext, cformat - context indicators to control coercions
* location - parse location of the coercion request, or -1 if unknown/implicit
+ * format - cast format template expression, this typically is NULL, but it's
+ * for CAST(expr as type FORMAT 'format_expr') construct.
*/
Node *
coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype,
@@ -81,6 +84,22 @@ 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);
+}
+
+Node *
+coerce_to_target_type_extended(ParseState *pstate, Node *expr, Oid exprtype,
+ Oid targettype, int32 targettypmod,
+ CoercionContext ccontext,
+ CoercionForm cformat,
+ int location,
+ Node *format)
{
Node *result;
Node *origexpr;
@@ -102,9 +121,10 @@ 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,
+ format);
/*
* If the target is a fixed-length type, it may need a length coercion as
@@ -131,6 +151,243 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype,
return result;
}
+ /*
+ * coerce_type_with_format()
+ *
+ * Coerce the CAST(expr AS type FORMAT 'fmt') construct to the target type.
+ *
+ * This is a subroutine of coerce_type_extended. The caller must ensure that
+ * the coercion is possible via can_coerce_type.
+ *
+ * We cannot simply construct a FuncCall node and rely on transformFuncCall,
+ * because the source expression and format template have already been
+ * transformed. Invoking transformExprRecurse again would be incorrect.
+ * Instead, we construct a FuncCall node and let ParseFuncOrColumn produce
+ * the final FuncExpr node.
+ */
+static Node *
+coerce_type_with_format(ParseState *pstate, Node *node, Node *fmt,
+ Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
+ CoercionContext ccontext, CoercionForm cformat, int location)
+{
+ Node *format;
+ Node *funcexpr;
+ FuncCall *fn;
+ List *funcname = NIL;
+ List *args = NIL;
+ TYPCATEGORY s_typcategory;
+ TYPCATEGORY t_typcategory;
+
+ int32 targetBaseTypeMod = targetTypeMod;
+ Oid targetBaseTypeId = getBaseTypeAndTypmod(targetTypeId,
+ &targetBaseTypeMod);
+ Oid inputBaseTypeId = getBaseType(inputTypeId);
+ Oid formatBaseTypeId = getBaseType(exprType(fmt));
+ TYPCATEGORY fmtcategory = TypeCategory(formatBaseTypeId);
+ Node *arg = node;
+
+ if (fmtcategory != TYPCATEGORY_STRING && fmtcategory != TYPCATEGORY_UNKNOWN)
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("CAST FORMAT expression is not of type text"),
+ parser_errposition(pstate, exprLocation(fmt)));
+
+ s_typcategory = TypeCategory(inputBaseTypeId);
+ t_typcategory = TypeCategory(targetBaseTypeId);
+
+ /*
+ * For CAST FORMAT, we explicitly coerce the source expression to TEXT and
+ * later pass it to FuncCall node, then ParseFuncOrColumn can use it.
+ */
+ if (IsA(node, Const) && inputTypeId == UNKNOWNOID)
+ {
+ Const *con = (Const *) node;
+ Const *newcon = makeNode(Const);
+ Type textType = typeidType(TEXTOID);
+
+ newcon->consttype = TEXTOID;
+ newcon->consttypmod = -1;
+ newcon->constcollid = typeTypeCollation(textType);
+ newcon->constlen = typeLen(textType);
+ newcon->constbyval = typeByVal(textType);
+ newcon->constisnull = con->constisnull;
+ newcon->location = exprLocation(node);
+
+ newcon->constvalue = stringTypeDatum(textType,
+ DatumGetCString(con->constvalue),
+ -1);
+ if (!newcon->constisnull && newcon->constlen == -1)
+ newcon->constvalue =
+ PointerGetDatum(PG_DETOAST_DATUM(newcon->constvalue));
+
+ ReleaseSysCache(textType);
+
+ arg = (Node *) newcon;
+ inputBaseTypeId = TEXTOID;
+ inputTypeId = TEXTOID;
+ }
+
+ /*
+ * It's not allowed to take a FORMAT and be cast to itself. This may
+ * change in the future.
+ */
+ if (IsBinaryCoercible(inputBaseTypeId, targetBaseTypeId))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot cast type %s to %s while using a format template",
+ format_type_be(inputBaseTypeId),
+ format_type_be(targetBaseTypeId)),
+ errdetail("binary coercible type cast is not supported while using a format template"),
+ parser_coercion_errposition(pstate, location, node));
+
+ switch (inputBaseTypeId)
+ {
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ case DATEOID:
+ case TIMEOID:
+ case TIMESTAMPOID:
+ case TIMESTAMPTZOID:
+ case INTERVALOID:
+ case NAMEOID:
+ case TEXTOID:
+ case BPCHAROID:
+ case VARCHAROID:
+ break;
+ default:
+
+ /*
+ * TODO: We should ideally avoid erroring out if inputBaseTypeId
+ * is binary-coercible to the above types, but iterating
+ * IsBinaryCoercible() for each type is too expensive.
+ */
+
+ /*
+ * FIXME: errhint is wrong, since timetz is a category of
+ * datetime. But it's not allowed.
+ */
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot cast type %s to %s using formatted template",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)),
+ errdetail("Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting"),
+ parser_coercion_errposition(pstate, location, node));
+ break;
+ }
+
+ switch (targetBaseTypeId)
+ {
+ case NUMERICOID:
+ case TIMESTAMPTZOID:
+ case DATEOID:
+ case NAMEOID:
+ case TEXTOID:
+ case BPCHAROID:
+ case VARCHAROID:
+ break;
+ default:
+
+ /*
+ * TODO: We should ideally avoid erroring out if targetBaseTypeId
+ * is binary-coercible to the above types, but iterating
+ * IsBinaryCoercible() for each type is too expensive.
+ */
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot cast type %s to %s using formatted template",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)),
+ errhint("Only timestamptz, text, numeric and date data type are supported for formatted type casting"),
+ parser_coercion_errposition(pstate, location, node));
+ break;
+ }
+
+ /*
+ * Internally, CAST FORMAT delegates to functions (e.g., to_char, to_date)
+ * where the format string parameter is typed as TEXT. Consequently, the
+ * FORMAT clause requires explicit coercion to TEXT.
+ */
+ format = coerce_to_target_type(pstate, fmt,
+ exprType(fmt), TEXTOID,
+ exprTypmod(fmt),
+ ccontext, cformat,
+ exprLocation(fmt));
+
+
+ /*
+ * For erroring out case like: CAST(NULL::int2 as numeric FORMAT '9');
+ *
+ * This is a necessary hack! The code above only checks the source and
+ * target types individually, rather than validating their combination.
+ * This works because all these formatting functions (to_char, to_date,
+ * to_number, to_timestamp) have distinct type categories for their inputs
+ * versus their outputs.
+ */
+ if (s_typcategory == t_typcategory)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot cast type %s to %s using formatted template",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)));
+
+ switch (targetBaseTypeId)
+ {
+ case DATEOID:
+ funcname = list_make2(makeString("pg_catalog"),
+ makeString("to_date"));
+ break;
+ case NUMERICOID:
+ funcname = list_make2(makeString("pg_catalog"),
+ makeString("to_number"));
+ break;
+ case TIMESTAMPTZOID:
+ funcname = list_make2(makeString("pg_catalog"),
+ makeString("to_timestamp"));
+ break;
+ case NAMEOID:
+ case TEXTOID:
+ case BPCHAROID:
+ case VARCHAROID:
+ funcname = list_make2(makeString("pg_catalog"),
+ makeString("to_char"));
+ break;
+ default:
+ elog(ERROR, "failed to find conversion function from %s to %s while using a format template",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId));
+ break;
+ }
+
+ args = list_make2(arg, format);
+ fn = makeFuncCall(funcname, args, COERCE_SQL_SYNTAX, -1);
+
+ funcexpr = ParseFuncOrColumn(pstate,
+ fn->funcname,
+ fn->args,
+ NULL,
+ fn,
+ false,
+ fn->location);
+
+ /*
+ * For CAST FORMAT, we do not enforce the exact source type, allowing
+ * certain categories of types instead. Therefore, the produced FuncExpr
+ * must be coerced to the exact target type. This is also necessary
+ * because the target type might be a domain.
+ */
+ return coerce_to_target_type(pstate,
+ funcexpr, exprType(funcexpr),
+ targetTypeId, targetTypeMod,
+ ccontext,
+ cformat,
+ location);
+}
+
/*
* coerce_type()
@@ -158,6 +415,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 *fmt)
{
Node *result;
CoercionPathType pathtype;
@@ -166,6 +435,22 @@ coerce_type(ParseState *pstate, Node *node,
if (targetTypeId == inputTypeId ||
node == NULL)
{
+ if (fmt != NULL)
+ {
+ if (inputTypeId == UNKNOWNOID)
+ inputTypeId = TEXTOID;
+
+ if (targetTypeId == UNKNOWNOID)
+ targetTypeId = TEXTOID;
+
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s while using a format template",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)),
+ errdetail("binary coercible type cast is not supported while using a format template"));
+ }
+
/* no conversion needed */
return node;
}
@@ -175,6 +460,18 @@ coerce_type(ParseState *pstate, Node *node,
targetTypeId == ANYCOMPATIBLEOID ||
targetTypeId == ANYCOMPATIBLENONARRAYOID)
{
+ if (fmt != NULL)
+ {
+ if (inputTypeId == UNKNOWNOID)
+ inputTypeId = TEXTOID;
+
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s while using a format template",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)));
+ }
+
/*
* Assume can_coerce_type verified that implicit coercion is okay.
*
@@ -197,6 +494,18 @@ coerce_type(ParseState *pstate, Node *node,
targetTypeId == ANYCOMPATIBLERANGEOID ||
targetTypeId == ANYCOMPATIBLEMULTIRANGEOID)
{
+ if (fmt != NULL)
+ {
+ if (inputTypeId == UNKNOWNOID)
+ inputTypeId = TEXTOID;
+
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s while using a format template",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)));
+ }
+
/*
* Assume can_coerce_type verified that implicit coercion is okay.
*
@@ -230,6 +539,17 @@ coerce_type(ParseState *pstate, Node *node,
return node;
}
}
+
+ if (fmt != NULL)
+ {
+ result = coerce_type_with_format(pstate, node, fmt,
+ inputTypeId,
+ targetTypeId, targetTypeMod,
+ ccontext, cformat,
+ location);
+ return result;
+ }
+
if (inputTypeId == UNKNOWNOID && IsA(node, Const))
{
/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 312dfdc182a..9156f149343 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -579,6 +579,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_GENERATED_COLUMN:
case EXPR_KIND_CYCLE_MARK:
case EXPR_KIND_PROPGRAPH_PROPERTY:
+ case EXPR_KIND_TYPECAST_FORMAT:
/* okay */
break;
@@ -1880,6 +1881,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_PROPGRAPH_PROPERTY:
err = _("cannot use subquery in property definition expression");
break;
+ case EXPR_KIND_TYPECAST_FORMAT:
+ err = _("cannot use subquery in CAST FORMAT expression");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -2726,6 +2730,7 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
Node *result;
Node *arg = tc->arg;
Node *expr;
+ Node *format = NULL;
Oid inputType;
Oid targetType;
int32 targetTypmod;
@@ -2747,6 +2752,12 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
int32 targetBaseTypmod;
Oid elementType;
+ if (tc->format)
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("formatted type cast is not supported for array type"),
+ parser_coercion_errposition(pstate, exprLocation(arg), arg));
+
/*
* If target is a domain over array, work with the base array type
* here. Below, we'll cast the array type to the domain. In the
@@ -2774,6 +2785,8 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
if (inputType == InvalidOid)
return expr; /* do nothing if NULL input */
+ format = transformExpr(pstate, tc->format, EXPR_KIND_TYPECAST_FORMAT);
+
/*
* Location of the coercion is preferentially the location of the :: or
* CAST symbol, but if there is none then use the location of the type
@@ -2783,11 +2796,12 @@ 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);
+ result = coerce_to_target_type_extended(pstate, expr, inputType,
+ targetType, targetTypmod,
+ COERCION_EXPLICIT,
+ COERCE_EXPLICIT_CAST,
+ location,
+ format);
if (result == NULL)
ereport(ERROR,
(errcode(ERRCODE_CANNOT_COERCE),
@@ -3241,6 +3255,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "CYCLE";
case EXPR_KIND_PROPGRAPH_PROPERTY:
return "property definition expression";
+ case EXPR_KIND_TYPECAST_FORMAT:
+ return "CAST FORMAT expression";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 8dbd41a3548..614ab69b239 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2786,6 +2786,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_PROPGRAPH_PROPERTY:
err = _("set-returning functions are not allowed in property definition expressions");
break;
+ case EXPR_KIND_TYPECAST_FORMAT:
+ err = _("set-returning functions are not allowed in CAST FORMAT expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 9a918e14aa7..6157490e5ef 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -686,6 +686,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
castnode = makeNode(TypeCast);
castnode->typeName = SystemTypeName("regclass");
castnode->arg = (Node *) snamenode;
+ castnode->format = NULL;
castnode->location = -1;
funccallnode = makeFuncCall(SystemFuncName("nextval"),
list_make1(castnode),
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 7bc12589e40..a31c55f5744 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11972,6 +11972,26 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
get_rule_expr((Node *) lsecond(expr->args), context, false);
appendStringInfoString(buf, "))");
return true;
+ case F_TO_CHAR_INT4_TEXT:
+ case F_TO_CHAR_INT8_TEXT:
+ case F_TO_CHAR_FLOAT4_TEXT:
+ case F_TO_CHAR_FLOAT8_TEXT:
+ case F_TO_CHAR_NUMERIC_TEXT:
+ case F_TO_CHAR_INTERVAL_TEXT:
+ case F_TO_CHAR_TIMESTAMP_TEXT:
+ case F_TO_CHAR_TIMESTAMPTZ_TEXT:
+ case F_TO_NUMBER:
+ case F_TO_TIMESTAMP_TEXT_TEXT:
+ case F_TO_DATE:
+ /* CAST FORMAT */
+ appendStringInfoString(buf, "CAST( ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " AS ");
+ appendStringInfoString(buf, format_type_be(expr->funcresulttype));
+ appendStringInfoString(buf, " FORMAT ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
}
return false;
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index df431220ac5..b79515a9b98 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -397,6 +397,7 @@ typedef struct TypeCast
NodeTag type;
Node *arg; /* the expression being casted */
TypeName *typeName; /* the target type */
+ Node *format; /* the cast format template expression */
ParseLoc location; /* token location, or -1 if unknown */
} TypeCast;
diff --git a/src/include/parser/parse_coerce.h b/src/include/parser/parse_coerce.h
index aabacd49b65..147c15c8e5a 100644
--- a/src/include/parser/parse_coerce.h
+++ b/src/include/parser/parse_coerce.h
@@ -43,11 +43,22 @@ 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 *format);
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 *format);
extern Node *coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod,
Oid typeId,
CoercionContext ccontext, CoercionForm cformat, int location,
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index fc2cbeb2083..a2b044d6583 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -83,6 +83,7 @@ typedef enum ParseExprKind
EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */
EXPR_KIND_CYCLE_MARK, /* cycle mark value */
EXPR_KIND_PROPGRAPH_PROPERTY, /* derived property expression */
+ EXPR_KIND_TYPECAST_FORMAT, /* CAST FORMAT */
} ParseExprKind;
diff --git a/src/test/regress/expected/cast.out b/src/test/regress/expected/cast.out
new file mode 100644
index 00000000000..c15a1828291
--- /dev/null
+++ b/src/test/regress/expected/cast.out
@@ -0,0 +1,312 @@
+create function ret_settxt() returns setof text as
+$$
+begin
+ return query execute 'select 1 union all select 1';
+end;
+$$
+language plpgsql immutable;
+-- check CAST FORMAT expression, the following should all fail
+select cast(NULL as date format ret_settxt()); -- cannot return a set
+ERROR: set-returning functions are not allowed in CAST FORMAT expressions
+LINE 1: select cast(NULL as date format ret_settxt());
+ ^
+select cast(NULL as date format (select 1::text where false));
+ERROR: cannot use subquery in CAST FORMAT expression
+LINE 1: select cast(NULL as date format (select 1::text where false)...
+ ^
+select cast(NULL as date format (string_agg(NULL, ' ')));
+ERROR: aggregate functions are not allowed in CAST FORMAT expressions
+LINE 1: select cast(NULL as date format (string_agg(NULL, ' ')));
+ ^
+select cast(NULL as date format (string_agg(NULL, ' ') over () ));
+ERROR: window functions are not allowed in CAST FORMAT expressions
+LINE 1: select cast(NULL as date format (string_agg(NULL, ' ') over ...
+ ^
+select cast(NULL as date format NULL::int);
+ERROR: CAST FORMAT expression is not of type text
+LINE 1: select cast(NULL as date format NULL::int);
+ ^
+select cast('1' as date format B'01');
+ERROR: CAST FORMAT expression is not of type text
+LINE 1: select cast('1' as date format B'01');
+ ^
+-- CAST FORMAT is restricted to the source and target types used by to_char, to_date, to_timestamp, and to_number.
+-- The following should all fail
+select cast('-34,338,492' as bigint format '99G999G999');
+ERROR: cannot cast type text to bigint using formatted template
+LINE 1: select cast('-34,338,492' as bigint format '99G999G999');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast(array[1] as text format 'YYYY');
+ERROR: formatted type cast is not supported for array type
+LINE 1: select cast(array[1] as text format 'YYYY');
+ ^
+select cast('1' as timestamp[] format 'YYYY-MM-DD');
+ERROR: cannot cast type text to timestamp without time zone[] using formatted template
+LINE 1: select cast('1' as timestamp[] format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('2012-13-12' as timestamp format 'YYYY-MM-DD');
+ERROR: cannot cast type text to timestamp without time zone using formatted template
+LINE 1: select cast('2012-13-12' as timestamp format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('2012-13-12' as time format 'YYYY-MM-DD');
+ERROR: cannot cast type text to time without time zone using formatted template
+LINE 1: select cast('2012-13-12' as time format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('2012-13-12' as timetz format 'YYYY-MM-DD');
+ERROR: cannot cast type text to time with time zone using formatted template
+LINE 1: select cast('2012-13-12' as timetz format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('2012-13-12' as interval format 'YYYY-MM-DD');
+ERROR: cannot cast type text to interval using formatted template
+LINE 1: select cast('2012-13-12' as interval format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('1'::text as unknown format 'YYYY-MM-DD');
+ERROR: cannot cast type text to unknown using formatted template
+LINE 1: select cast('1'::text as unknown format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('1' as bool format 'YYYY-MM-DD');
+ERROR: cannot cast type text to boolean using formatted template
+LINE 1: select cast('1' as bool format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('1' as json format 'YYYY-MM-DD');
+ERROR: cannot cast type text to json using formatted template
+LINE 1: select cast('1' as json format 'YYYY-MM-DD');
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('1'::json as text format 'YYYY-MM-DD');
+ERROR: cannot cast type json to text using formatted template
+LINE 1: select cast('1'::json as text format 'YYYY-MM-DD');
+ ^
+DETAIL: Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting
+select cast('1' as anyelement format 'YYYY-MM-DD');
+ERROR: cannot cast type text to anyelement while using a format template
+select cast('1' as anyenum format 'YYYY-MM-DD');
+ERROR: cannot cast type unknown to anyenum
+LINE 1: select cast('1' as anyenum format 'YYYY-MM-DD');
+ ^
+select cast('1' as anyarray format 'YYYY-MM-DD');
+ERROR: cannot cast type text to anyarray while using a format template
+select cast(null::anyelement as anyelement format 'YYYY-MM-DD');
+ERROR: cannot cast type text to anyelement while using a format template
+select cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ');
+ERROR: cannot cast type time with time zone to text using formatted template
+LINE 1: select cast('2012-12-12 12:00'::timetz as text format 'YYYY-...
+ ^
+DETAIL: Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting
+select cast(null::regclass as text format 'YYYY-MM-DD HH:MI:SS TZ');
+ERROR: cannot cast type regclass to text using formatted template
+LINE 1: select cast(null::regclass as text format 'YYYY-MM-DD HH:MI:...
+ ^
+DETAIL: Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting
+select cast(null::int2 as numeric format null);
+ERROR: cannot cast type smallint to numeric using formatted template
+select cast(null::date as timestamptz format null);
+ERROR: cannot cast type date to timestamp with time zone using formatted template
+select cast(null::time as timestamptz format null);
+ERROR: cannot cast type time without time zone to timestamp with time zone
+LINE 1: select cast(null::time as timestamptz format null);
+ ^
+-- CAST FORMAT is not supported for binary coercible type cast
+select cast('2022-01-01' as unknown format null);
+ERROR: cannot cast type text to text while using a format template
+DETAIL: binary coercible type cast is not supported while using a format template
+select cast('1' as text format '1'::text);
+ERROR: cannot cast type text to text while using a format template
+LINE 1: select cast('1' as text format '1'::text);
+ ^
+DETAIL: binary coercible type cast is not supported while using a format template
+select cast('1'::text as text format '1'::text);
+ERROR: cannot cast type text to text while using a format template
+DETAIL: binary coercible type cast is not supported while using a format template
+select cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ'); -- error
+ERROR: cannot cast type time with time zone to text using formatted template
+LINE 1: select cast('2012-12-12 12:00'::timetz as text format 'YYYY-...
+ ^
+DETAIL: Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting
+select cast('2012-13-12' as date format 'YYYY-DD-MM'); -- ok
+ date
+------------
+ 12-13-2012
+(1 row)
+
+select cast('2012-13-12' as date format 'YYYY-MM-DD'); -- error
+ERROR: date/time field value out of range: "2012-13-12"
+select cast('1' as date format 'YYYY-MM-DD');
+ date
+------------
+ 01-01-0001
+(1 row)
+
+select cast('1' collate "C" as date format 'YYYY-MM-DD');
+ date
+------------
+ 01-01-0001
+(1 row)
+
+select cast('2012-13-12' as date format 'YYYY-DD-MM') as date;
+ date
+------------
+ 12-13-2012
+(1 row)
+
+select cast('2012-13-12' as timestamptz format 'YYYY-DD-MM') as date;
+ date
+------------------------------
+ Thu Dec 13 00:00:00 2012 PST
+(1 row)
+
+select cast('2012-13-12'::text as timestamp format 'YYYY-DD-MM') as date; -- error
+ERROR: cannot cast type text to timestamp without time zone using formatted template
+LINE 1: select cast('2012-13-12'::text as timestamp format 'YYYY-DD-...
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('1' as timestamp format 'YYYY-MM-DD') = to_timestamp('1', 'YYYY-MM-DD');
+ERROR: cannot cast type text to timestamp without time zone using formatted template
+LINE 1: select cast('1' as timestamp format 'YYYY-MM-DD') = to_times...
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('2026-01-28 13:29:12.324606+01'::text as timestamp format 'YYYY-MM-DD') =
+ to_timestamp('2026-01-28 13:29:12.324606+01'::text, 'YYYY-MM-DD');
+ERROR: cannot cast type text to timestamp without time zone using formatted template
+LINE 1: select cast('2026-01-28 13:29:12.324606+01'::text as timesta...
+ ^
+HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting
+select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD');
+ ?column?
+----------
+ t
+(1 row)
+
+-- test with domain
+create domain d1 as date check (value <> '0001-01-01');
+select cast('1' as text format 'YYYY-MM-DD'); -- error
+ERROR: cannot cast type text to text while using a format template
+LINE 1: select cast('1' as text format 'YYYY-MM-DD');
+ ^
+DETAIL: binary coercible type cast is not supported while using a format template
+select cast('1' as d1 format 'YYYY-MM-DD'); -- error
+ERROR: value for domain d1 violates check constraint "d1_check"
+select cast('1' as date format 'MM-DD'); -- ok
+ date
+---------------
+ 01-01-0001 BC
+(1 row)
+
+select cast('1' as d1 format 'MM-DD'); -- ok
+ d1
+---------------
+ 01-01-0001 BC
+(1 row)
+
+select cast('1'::text collate "C" as date format 'YYYY-MM-DD');
+ date
+------------
+ 01-01-0001
+(1 row)
+
+select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD') as expect_true;
+ expect_true
+-------------
+ t
+(1 row)
+
+create table tcast(col1 text, col2 text, col3 date, col4 timestamptz, col5 int8);
+insert into tcast(col1, col2, col5)
+ values('2022-12-13', 'YYYY-MM-DD', 1234),
+ ('2022-12-01', 'YYYY-DD-MM', -1234);
+select cast(col1 as date format col2) from tcast;
+ col1
+------------
+ 12-13-2022
+ 01-12-2022
+(2 rows)
+
+select cast(col1 as date format col3) from tcast; -- error
+ERROR: CAST FORMAT expression is not of type text
+LINE 1: select cast(col1 as date format col3) from tcast;
+ ^
+select cast(col1 as date format col3::text) from tcast; -- ok
+ col1
+------
+
+
+(2 rows)
+
+create function imm_const() returns text as $$ begin return 'YYYY-MM-DD'; end; $$ language plpgsql immutable;
+select cast(col1 as date format imm_const()) from tcast;
+ col1
+------------
+ 12-13-2022
+ 12-01-2022
+(2 rows)
+
+create index s1 on tcast(to_date(col1, 'YYYY-MM-DD')); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index s1 on tcast(to_date(col1, 'YYYY-MM-DD'));
+ ^
+create index s1 on tcast(cast(col1 as date format 'YYYY-MM-DD')); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index s1 on tcast(cast(col1 as date format 'YYYY-MM-D...
+ ^
+create view tcast_v1 as
+ select cast(col1 as date format 'YYYY-MM-DD') as to_date,
+ cast(col1 as timestamptz format 'YYYY-MM-DD') as to_timestamptz,
+ cast(NULL::interval as text format 'YYYY-MM-DD') as to_txt0,
+ cast(col1::timestamp as text format 'YYYY-MM-DD') as to_txt1,
+ cast(col3 as text format 'YYYY-MM-DD') as to_txt2,
+ cast(col4 as text format 'YYYY-MM-DD') as to_txt3,
+ cast(numeric 'inf' as text format 'YYYY-MM-DD') as to_txt4,
+ cast(bigint '12324' as text format 'YYYY-MM-DD') as to_txt5
+ from tcast;
+select pg_get_viewdef('tcast_v1', true);
+ pg_get_viewdef
+--------------------------------------------------------------------------------------------
+ SELECT CAST( col1 AS date FORMAT 'YYYY-MM-DD'::text) AS to_date, +
+ CAST( col1 AS timestamp with time zone FORMAT 'YYYY-MM-DD'::text) AS to_timestamptz, +
+ CAST( NULL::interval AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt0, +
+ CAST( col1::timestamp without time zone AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt1,+
+ CAST( col3 AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt2, +
+ CAST( col4 AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt3, +
+ CAST( 'Infinity'::numeric AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt4, +
+ CAST( '12324'::bigint AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt5 +
+ FROM tcast;
+(1 row)
+
+create view tcast_v2 as
+ select cast(col5 as text format '9.99EEEE') as to_txt0,
+ cast(col5::float8 as text format '9.99EEEE') as to_txt1,
+ cast(col5::float4 as text format '9.99EEEE') as to_txt2,
+ cast(col5::numeric as text format '9.99EEEE') as to_txt3,
+ cast(col5::int2 as text format '9.99EEEE') as to_txt4,
+ cast(col5::int4 as text format '9.99EEEE') as to_txt5
+ from tcast;
+select pg_get_viewdef('tcast_v2', true);
+ pg_get_viewdef
+-------------------------------------------------------------------------------
+ SELECT CAST( col5 AS text FORMAT '9.99EEEE'::text) AS to_txt0, +
+ CAST( col5::double precision AS text FORMAT '9.99EEEE'::text) AS to_txt1,+
+ CAST( col5::real AS text FORMAT '9.99EEEE'::text) AS to_txt2, +
+ CAST( col5::numeric AS text FORMAT '9.99EEEE'::text) AS to_txt3, +
+ CAST( col5::smallint AS text FORMAT '9.99EEEE'::text) AS to_txt4, +
+ CAST( col5::integer AS text FORMAT '9.99EEEE'::text) AS to_txt5 +
+ FROM tcast;
+(1 row)
+
+select * from tcast_v2;
+ to_txt0 | to_txt1 | to_txt2 | to_txt3 | to_txt4 | to_txt5
+-----------+-----------+-----------+-----------+-----------+-----------
+ 1.23e+03 | 1.23e+03 | 1.23e+03 | 1.23e+03 | 1.23e+03 | 1.23e+03
+ -1.23e+03 | -1.23e+03 | -1.23e+03 | -1.23e+03 | -1.23e+03 | -1.23e+03
+(2 rows)
+
+drop function ret_settxt;
+drop view tcast_v1, tcast_v2;
+drop table tcast;
diff --git a/src/test/regress/expected/collate.linux.utf8.out b/src/test/regress/expected/collate.linux.utf8.out
index c6e84c27b69..129130a4f1b 100644
--- a/src/test/regress/expected/collate.linux.utf8.out
+++ b/src/test/regress/expected/collate.linux.utf8.out
@@ -463,6 +463,30 @@ SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR");
01 NİS 2010
(1 row)
+SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY');
+ text
+-------------
+ 01 ŞUB 2010
+(1 row)
+
+SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR");
+ text
+-------------
+ 01 ŞUB 2010
+(1 row)
+
+SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY');
+ text
+-------------
+ 01 NIS 2010
+(1 row)
+
+SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR");
+ text
+-------------
+ 01 NİS 2010
+(1 row)
+
-- to_date
SELECT to_date('01 ŞUB 2010', 'DD TMMON YYYY');
to_date
@@ -479,6 +503,21 @@ SELECT to_date('01 Şub 2010', 'DD TMMON YYYY');
SELECT to_date('1234567890ab 2010', 'TMMONTH YYYY'); -- fail
ERROR: invalid value "1234567890ab" for "MONTH"
DETAIL: The given value did not match any of the allowed values for this field.
+SELECT CAST('01 ŞUB 2010' as date format 'DD TMMON YYYY');
+ date
+------------
+ 02-01-2010
+(1 row)
+
+SELECT CAST('01 ŞUB 2010' as date format 'DD TMMON YYYY'); -- ok
+ date
+------------
+ 02-01-2010
+(1 row)
+
+SELECT CAST('1234567890ab 2010' as date format 'TMMONTH YYYY'); -- fail
+ERROR: invalid value "1234567890ab" for "MONTH"
+DETAIL: The given value did not match any of the allowed values for this field.
-- backwards parsing
CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc';
CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 32cf62b6741..95ca730113e 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3324,72 +3324,144 @@ SELECT to_timestamp('2011-12-18 11:38 EST', 'YYYY-MM-DD HH12:MI TZ');
Sun Dec 18 08:38:00 2011 PST
(1 row)
+SELECT cast('2011-12-18 11:38 EST' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
+ timestamptz
+------------------------------
+ Sun Dec 18 08:38:00 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZ');
to_timestamp
------------------------------
Sun Dec 18 08:38:00 2011 PST
(1 row)
+SELECT cast('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
+ timestamptz
+------------------------------
+ Sun Dec 18 08:38:00 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ');
to_timestamp
------------------------------
Sun Dec 18 02:08:00 2011 PST
(1 row)
+SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
+ timestamptz
+------------------------------
+ Sun Dec 18 02:08:00 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ'); -- dyntz
to_timestamp
------------------------------
Sat Dec 17 23:38:00 2011 PST
(1 row)
+SELECT cast('2011-12-18 11:38 MSK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- dyntz
+ timestamptz
+------------------------------
+ Sat Dec 17 23:38:00 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 00:00 LMT', 'YYYY-MM-DD HH24:MI TZ'); -- dyntz
to_timestamp
------------------------------
Sat Dec 17 23:52:58 2011 PST
(1 row)
+SELECT cast('2011-12-18 00:00 LMT' as timestamptz format 'YYYY-MM-DD HH24:MI TZ'); -- dyntz
+ timestamptz
+------------------------------
+ Sat Dec 17 23:52:58 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38ESTFOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
to_timestamp
------------------------------
Sun Dec 18 08:38:24 2011 PST
(1 row)
+SELECT cast('2011-12-18 11:38ESTFOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS');
+ timestamptz
+------------------------------
+ Sun Dec 18 08:38:24 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
to_timestamp
------------------------------
Sun Dec 18 08:38:24 2011 PST
(1 row)
+SELECT cast('2011-12-18 11:38-05FOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS');
+ timestamptz
+------------------------------
+ Sun Dec 18 08:38:24 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ'); -- error
ERROR: invalid value "JUNK" for "TZ"
DETAIL: Time zone abbreviation is not recognized.
+SELECT cast('2011-12-18 11:38 JUNK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error
+ERROR: invalid value "JUNK" for "TZ"
+DETAIL: Time zone abbreviation is not recognized.
SELECT to_timestamp('2011-12-18 11:38 ...', 'YYYY-MM-DD HH12:MI TZ'); -- error
ERROR: invalid value ".." for "TZ"
DETAIL: Value must be an integer.
+SELECT cast('2011-12-18 11:38 ...' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error
+ERROR: invalid value ".." for "TZ"
+DETAIL: Value must be an integer.
SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF');
to_timestamp
------------------------------
Sun Dec 18 08:38:00 2011 PST
(1 row)
+SELECT cast ('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI OF');
+ timestamptz
+------------------------------
+ Sun Dec 18 08:38:00 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF');
to_timestamp
------------------------------
Sun Dec 18 02:08:00 2011 PST
(1 row)
+SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format 'YYYY-MM-DD HH12:MI OF');
+ timestamptz
+------------------------------
+ Sun Dec 18 02:08:00 2011 PST
+(1 row)
+
SELECT to_timestamp('2011-12-18 11:38 +xyz', 'YYYY-MM-DD HH12:MI OF'); -- error
ERROR: invalid value "xy" for "OF"
DETAIL: Value must be an integer.
+SELECT cast('2011-12-18 11:38 +xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF'); -- error
+ERROR: invalid value "xy" for "OF"
+DETAIL: Value must be an integer.
SELECT to_timestamp('2011-12-18 11:38 +01:xyz', 'YYYY-MM-DD HH12:MI OF'); -- error
ERROR: invalid value "xy" for "OF"
DETAIL: Value must be an integer.
+SELECT cast('2011-12-18 11:38 +01:xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF'); -- error
+ERROR: invalid value "xy" for "OF"
+DETAIL: Value must be an integer.
SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS');
to_timestamp
----------------------------------
Fri Nov 02 12:34:56.025 2018 PDT
(1 row)
+SELECT cast('2018-11-02 12:34:56.025' as timestamptz format 'YYYY-MM-DD HH24:MI:SS.MS');
+ timestamptz
+----------------------------------
+ Fri Nov 02 12:34:56.025 2018 PDT
+(1 row)
+
SELECT i, to_timestamp('2018-11-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
i | to_timestamp
---+------------------------------
@@ -3469,6 +3541,8 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF'
SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
ERROR: date/time field value out of range: "2018-11-02 12:34:56.123456789"
+SELECT i, cast('2018-11-02 12:34:56.123456789' as timestamptz format 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
+ERROR: date/time field value out of range: "2018-11-02 12:34:56.123456789"
SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i) FROM generate_series(1, 6) i;
i | to_timestamp
---+-------------------------------------
@@ -3486,18 +3560,36 @@ SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored
04-01-1902
(1 row)
+SELECT cast('1 4 1902' as date format 'Q MM YYYY'); -- Q is ignored
+ date
+------------
+ 04-01-1902
+(1 row)
+
SELECT to_date('3 4 21 01', 'W MM CC YY');
to_date
------------
04-15-2001
(1 row)
+SELECT cast('3 4 21 01' as date format 'W MM CC YY');
+ date
+------------
+ 04-15-2001
+(1 row)
+
SELECT to_date('2458872', 'J');
to_date
------------
01-23-2020
(1 row)
+SELECT cast('2458872' as date format 'J');
+ date
+------------
+ 01-23-2020
+(1 row)
+
--
-- Check handling of BC dates
--
@@ -3833,12 +3925,24 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
2012-12-12 12:00:00 PST
(1 row)
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ');
+ text
+-------------------------
+ 2012-12-12 12:00:00 PST
+(1 row)
+
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS tz');
to_char
-------------------------
2012-12-12 12:00:00 pst
(1 row)
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS tz');
+ text
+-------------------------
+ 2012-12-12 12:00:00 pst
+(1 row)
+
--
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
--
@@ -3868,6 +3972,32 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
2012-12-12 12:00:00 -01:30
(1 row)
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ'),
+ to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+ text | to_char
+----------------------------+----------------------------
+ 2012-12-12 12:00:00 -01:30 | 2012-12-12 12:00:00 -01:30
+(1 row)
+
+SELECT cast('2012-12-12 12:00'::date as text format 'YYYY-MM-DD HH:MI:SS TZ'),
+ to_char('2012-12-12 12:00'::date, 'YYYY-MM-DD HH:MI:SS TZ');
+ text | to_char
+----------------------------+----------------------------
+ 2012-12-12 12:00:00 -01:30 | 2012-12-12 12:00:00 -01:30
+(1 row)
+
+SELECT cast('12:00'::time as text format 'HH:MI:SS'),
+ to_char('12:00'::time, 'HH:MI:SS');
+ text | to_char
+----------+----------
+ 12:00:00 | 12:00:00
+(1 row)
+
+SELECT cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ');
+ERROR: cannot cast type time with time zone to text using formatted template
+LINE 1: SELECT cast('2012-12-12 12:00'::timetz as text format 'YYYY-...
+ ^
+DETAIL: Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS');
to_char
------------------
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index a16e3ccdb2e..4bcaaaf04a7 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -2263,12 +2263,24 @@ SELECT to_char('infinity'::interval, 'YYYY');
(1 row)
+SELECT cast('infinity'::interval as text format 'YYYY');
+ text
+------
+
+(1 row)
+
SELECT to_char('-infinity'::interval, 'YYYY');
to_char
---------
(1 row)
+SELECT cast('-infinity'::interval as text format 'YYYY');
+ text
+------
+
+(1 row)
+
-- "ago" can only appear once at the end of an interval.
SELECT INTERVAL '42 days 2 seconds ago ago';
ERROR: invalid input syntax for type interval: "42 days 2 seconds ago ago"
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index c58e232a263..09264b98bfb 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2264,147 +2264,286 @@ SELECT to_number('-34,338,492', '99G999G999');
-34338492
(1 row)
+SELECT CAST('-34,338,492' as numeric FORMAT '99G999G999');
+ numeric
+-----------
+ -34338492
+(1 row)
+
SELECT to_number('-34,338,492.654,878', '99G999G999D999G999');
to_number
------------------
-34338492.654878
(1 row)
+SELECT CAST('-34,338,492.654,878' as numeric FORMAT '99G999G999D999G999');
+ numeric
+------------------
+ -34338492.654878
+(1 row)
+
SELECT to_number('<564646.654564>', '999999.999999PR');
to_number
----------------
-564646.654564
(1 row)
+SELECT CAST('<564646.654564>' as numeric FORMAT '999999.999999PR');
+ numeric
+----------------
+ -564646.654564
+(1 row)
+
SELECT to_number('0.00001-', '9.999999S');
to_number
-----------
-0.00001
(1 row)
+SELECT CAST('0.00001-' as numeric FORMAT '9.999999S');
+ numeric
+----------
+ -0.00001
+(1 row)
+
SELECT to_number('5.01-', 'FM9.999999S');
to_number
-----------
-5.01
(1 row)
+SELECT CAST('5.01-' as numeric FORMAT 'FM9.999999S');
+ numeric
+---------
+ -5.01
+(1 row)
+
SELECT to_number('5.01-', 'FM9.999999MI');
to_number
-----------
-5.01
(1 row)
+SELECT CAST('5.01-' as numeric FORMAT 'FM9.999999MI');
+ numeric
+---------
+ -5.01
+(1 row)
+
SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
to_number
-----------
544448.78
(1 row)
+SELECT CAST('5 4 4 4 4 8 . 7 8' as numeric FORMAT '9 9 9 9 9 9 . 9 9');
+ numeric
+-----------
+ 544448.78
+(1 row)
+
SELECT to_number('.01', 'FM9.99');
to_number
-----------
0.01
(1 row)
+SELECT CAST('.01' as numeric FORMAT 'FM9.99');
+ numeric
+---------
+ 0.01
+(1 row)
+
SELECT to_number('.0', '99999999.99999999');
to_number
-----------
0.0
(1 row)
+SELECT CAST('.0' as numeric FORMAT '99999999.99999999');
+ numeric
+---------
+ 0.0
+(1 row)
+
SELECT to_number('0', '99.99');
to_number
-----------
0
(1 row)
+SELECT CAST('0' as numeric FORMAT '99.99');
+ numeric
+---------
+ 0
+(1 row)
+
SELECT to_number('.-01', 'S99.99');
to_number
-----------
-0.01
(1 row)
+SELECT CAST('.-01' as numeric FORMAT 'S99.99');
+ numeric
+---------
+ -0.01
+(1 row)
+
SELECT to_number('.01-', '99.99S');
to_number
-----------
-0.01
(1 row)
+SELECT CAST('.01-' as numeric FORMAT '99.99S');
+ numeric
+---------
+ -0.01
+(1 row)
+
SELECT to_number(' . 0 1-', ' 9 9 . 9 9 S');
to_number
-----------
-0.01
(1 row)
+SELECT CAST(' . 0 1-' as numeric FORMAT ' 9 9 . 9 9 S');
+ numeric
+---------
+ -0.01
+(1 row)
+
SELECT to_number('34,50','999,99');
to_number
-----------
3450
(1 row)
+SELECT CAST('34,50' as numeric FORMAT '999,99');
+ numeric
+---------
+ 3450
+(1 row)
+
SELECT to_number('123,000','999G');
to_number
-----------
123
(1 row)
+SELECT CAST('123,000' as numeric FORMAT '999G');
+ numeric
+---------
+ 123
+(1 row)
+
SELECT to_number('123456','999G999');
to_number
-----------
123456
(1 row)
+SELECT CAST('123456' as numeric FORMAT '999G999');
+ numeric
+---------
+ 123456
+(1 row)
+
SELECT to_number('$1234.56','L9,999.99');
to_number
-----------
1234.56
(1 row)
+SELECT CAST('$1234.56' as numeric FORMAT 'L9,999.99');
+ numeric
+---------
+ 1234.56
+(1 row)
+
SELECT to_number('$1234.56','L99,999.99');
to_number
-----------
1234.56
(1 row)
+SELECT CAST('$1234.56' as numeric FORMAT 'L99,999.99');
+ numeric
+---------
+ 1234.56
+(1 row)
+
SELECT to_number('$1,234.56','L99,999.99');
to_number
-----------
1234.56
(1 row)
+SELECT CAST('$1,234.56' as numeric FORMAT 'L99,999.99');
+ numeric
+---------
+ 1234.56
+(1 row)
+
SELECT to_number('1234.56','L99,999.99');
to_number
-----------
1234.56
(1 row)
+SELECT CAST('1234.56' as numeric FORMAT 'L99,999.99');
+ numeric
+---------
+ 1234.56
+(1 row)
+
SELECT to_number('1,234.56','L99,999.99');
to_number
-----------
1234.56
(1 row)
+SELECT CAST('1,234.56' as numeric FORMAT 'L99,999.99');
+ numeric
+---------
+ 1234.56
+(1 row)
+
SELECT to_number('42nd', '99th');
to_number
-----------
42
(1 row)
+SELECT CAST('42nd' as numeric FORMAT '99th');
+ numeric
+---------
+ 42
+(1 row)
+
SELECT to_number('123456', '99999V99');
to_number
-------------------------
1234.560000000000000000
(1 row)
+SELECT CAST('123456' as numeric FORMAT '99999V99');
+ numeric
+-------------------------
+ 1234.560000000000000000
+(1 row)
+
-- Test for correct conversion between numbers and Roman numerals
WITH rows AS
(SELECT i, to_char(i, 'RN') AS roman FROM generate_series(1, 3999) AS i)
SELECT
- bool_and(to_number(roman, 'RN') = i) as valid
+ bool_and(to_number(roman, 'RN') = i) as valid,
+ bool_and(cast(roman as numeric format 'RN') = i) as valid
FROM rows;
- valid
--------
- t
+ valid | valid
+-------+-------
+ t | t
(1 row)
-- Some additional tests for RN input
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 734da057c34..fdacfeae8ec 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..2999ed11198
--- /dev/null
+++ b/src/test/regress/sql/cast.sql
@@ -0,0 +1,107 @@
+create function ret_settxt() returns setof text as
+$$
+begin
+ return query execute 'select 1 union all select 1';
+end;
+$$
+language plpgsql immutable;
+
+-- check CAST FORMAT expression, the following should all fail
+select cast(NULL as date format ret_settxt()); -- cannot return a set
+select cast(NULL as date format (select 1::text where false));
+select cast(NULL as date format (string_agg(NULL, ' ')));
+select cast(NULL as date format (string_agg(NULL, ' ') over () ));
+select cast(NULL as date format NULL::int);
+select cast('1' as date format B'01');
+
+-- CAST FORMAT is restricted to the source and target types used by to_char, to_date, to_timestamp, and to_number.
+-- The following should all fail
+select cast('-34,338,492' as bigint format '99G999G999');
+select cast(array[1] as text format 'YYYY');
+select cast('1' as timestamp[] format 'YYYY-MM-DD');
+select cast('2012-13-12' as timestamp format 'YYYY-MM-DD');
+select cast('2012-13-12' as time format 'YYYY-MM-DD');
+select cast('2012-13-12' as timetz format 'YYYY-MM-DD');
+select cast('2012-13-12' as interval format 'YYYY-MM-DD');
+select cast('1'::text as unknown format 'YYYY-MM-DD');
+select cast('1' as bool format 'YYYY-MM-DD');
+select cast('1' as json format 'YYYY-MM-DD');
+select cast('1'::json as text format 'YYYY-MM-DD');
+select cast('1' as anyelement format 'YYYY-MM-DD');
+select cast('1' as anyenum format 'YYYY-MM-DD');
+select cast('1' as anyarray format 'YYYY-MM-DD');
+select cast(null::anyelement as anyelement format 'YYYY-MM-DD');
+select cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ');
+select cast(null::regclass as text format 'YYYY-MM-DD HH:MI:SS TZ');
+select cast(null::int2 as numeric format null);
+select cast(null::date as timestamptz format null);
+select cast(null::time as timestamptz format null);
+
+-- CAST FORMAT is not supported for binary coercible type cast
+select cast('2022-01-01' as unknown format null);
+select cast('1' as text format '1'::text);
+select cast('1'::text as text format '1'::text);
+
+select cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ'); -- error
+select cast('2012-13-12' as date format 'YYYY-DD-MM'); -- ok
+select cast('2012-13-12' as date format 'YYYY-MM-DD'); -- error
+select cast('1' as date format 'YYYY-MM-DD');
+select cast('1' collate "C" as date format 'YYYY-MM-DD');
+select cast('2012-13-12' as date format 'YYYY-DD-MM') as date;
+select cast('2012-13-12' as timestamptz format 'YYYY-DD-MM') as date;
+select cast('2012-13-12'::text as timestamp format 'YYYY-DD-MM') as date; -- error
+select cast('1' as timestamp format 'YYYY-MM-DD') = to_timestamp('1', 'YYYY-MM-DD');
+select cast('2026-01-28 13:29:12.324606+01'::text as timestamp format 'YYYY-MM-DD') =
+ to_timestamp('2026-01-28 13:29:12.324606+01'::text, 'YYYY-MM-DD');
+select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD');
+
+-- test with domain
+create domain d1 as date check (value <> '0001-01-01');
+select cast('1' as text format 'YYYY-MM-DD'); -- error
+select cast('1' as d1 format 'YYYY-MM-DD'); -- error
+select cast('1' as date format 'MM-DD'); -- ok
+select cast('1' as d1 format 'MM-DD'); -- ok
+
+select cast('1'::text collate "C" as date format 'YYYY-MM-DD');
+select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD') as expect_true;
+
+create table tcast(col1 text, col2 text, col3 date, col4 timestamptz, col5 int8);
+insert into tcast(col1, col2, col5)
+ values('2022-12-13', 'YYYY-MM-DD', 1234),
+ ('2022-12-01', 'YYYY-DD-MM', -1234);
+
+select cast(col1 as date format col2) from tcast;
+select cast(col1 as date format col3) from tcast; -- error
+select cast(col1 as date format col3::text) from tcast; -- ok
+
+create function imm_const() returns text as $$ begin return 'YYYY-MM-DD'; end; $$ language plpgsql immutable;
+select cast(col1 as date format imm_const()) from tcast;
+create index s1 on tcast(to_date(col1, 'YYYY-MM-DD')); -- error
+create index s1 on tcast(cast(col1 as date format 'YYYY-MM-DD')); -- error
+
+create view tcast_v1 as
+ select cast(col1 as date format 'YYYY-MM-DD') as to_date,
+ cast(col1 as timestamptz format 'YYYY-MM-DD') as to_timestamptz,
+ cast(NULL::interval as text format 'YYYY-MM-DD') as to_txt0,
+ cast(col1::timestamp as text format 'YYYY-MM-DD') as to_txt1,
+ cast(col3 as text format 'YYYY-MM-DD') as to_txt2,
+ cast(col4 as text format 'YYYY-MM-DD') as to_txt3,
+ cast(numeric 'inf' as text format 'YYYY-MM-DD') as to_txt4,
+ cast(bigint '12324' as text format 'YYYY-MM-DD') as to_txt5
+ from tcast;
+
+select pg_get_viewdef('tcast_v1', true);
+
+create view tcast_v2 as
+ select cast(col5 as text format '9.99EEEE') as to_txt0,
+ cast(col5::float8 as text format '9.99EEEE') as to_txt1,
+ cast(col5::float4 as text format '9.99EEEE') as to_txt2,
+ cast(col5::numeric as text format '9.99EEEE') as to_txt3,
+ cast(col5::int2 as text format '9.99EEEE') as to_txt4,
+ cast(col5::int4 as text format '9.99EEEE') as to_txt5
+ from tcast;
+select pg_get_viewdef('tcast_v2', true);
+select * from tcast_v2;
+drop function ret_settxt;
+drop view tcast_v1, tcast_v2;
+drop table tcast;
diff --git a/src/test/regress/sql/collate.linux.utf8.sql b/src/test/regress/sql/collate.linux.utf8.sql
index 132d13af0a8..f8ba5a0e815 100644
--- a/src/test/regress/sql/collate.linux.utf8.sql
+++ b/src/test/regress/sql/collate.linux.utf8.sql
@@ -182,12 +182,20 @@ SELECT to_char(date '2010-02-01', 'DD TMMON YYYY' COLLATE "tr_TR");
SELECT to_char(date '2010-04-01', 'DD TMMON YYYY');
SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR");
+SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY');
+SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR");
+SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY');
+SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR");
+
-- to_date
SELECT to_date('01 ŞUB 2010', 'DD TMMON YYYY');
SELECT to_date('01 Şub 2010', 'DD TMMON YYYY');
SELECT to_date('1234567890ab 2010', 'TMMONTH YYYY'); -- fail
+SELECT CAST('01 ŞUB 2010' as date format 'DD TMMON YYYY');
+SELECT CAST('01 ŞUB 2010' as date format 'DD TMMON YYYY'); -- ok
+SELECT CAST('1234567890ab 2010' as date format 'TMMONTH YYYY'); -- fail
-- backwards parsing
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 8978249a5dc..714e375b088 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -539,21 +539,46 @@ SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM');
SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM');
SELECT to_timestamp('2011-12-18 11:38 EST', 'YYYY-MM-DD HH12:MI TZ');
+SELECT cast('2011-12-18 11:38 EST' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
+
SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZ');
+SELECT cast('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
+
SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ');
+SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
+
SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ'); -- dyntz
+SELECT cast('2011-12-18 11:38 MSK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- dyntz
+
SELECT to_timestamp('2011-12-18 00:00 LMT', 'YYYY-MM-DD HH24:MI TZ'); -- dyntz
+SELECT cast('2011-12-18 00:00 LMT' as timestamptz format 'YYYY-MM-DD HH24:MI TZ'); -- dyntz
+
SELECT to_timestamp('2011-12-18 11:38ESTFOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
+SELECT cast('2011-12-18 11:38ESTFOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS');
+
SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
+SELECT cast('2011-12-18 11:38-05FOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS');
+
SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ'); -- error
+SELECT cast('2011-12-18 11:38 JUNK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error
+
SELECT to_timestamp('2011-12-18 11:38 ...', 'YYYY-MM-DD HH12:MI TZ'); -- error
+SELECT cast('2011-12-18 11:38 ...' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error
SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF');
+SELECT cast ('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI OF');
+
SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF');
+SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format 'YYYY-MM-DD HH12:MI OF');
+
SELECT to_timestamp('2011-12-18 11:38 +xyz', 'YYYY-MM-DD HH12:MI OF'); -- error
+SELECT cast('2011-12-18 11:38 +xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF'); -- error
+
SELECT to_timestamp('2011-12-18 11:38 +01:xyz', 'YYYY-MM-DD HH12:MI OF'); -- error
+SELECT cast('2011-12-18 11:38 +01:xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF'); -- error
SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS');
+SELECT cast('2018-11-02 12:34:56.025' as timestamptz format 'YYYY-MM-DD HH24:MI:SS.MS');
SELECT i, to_timestamp('2018-11-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
SELECT i, to_timestamp('2018-11-02 12:34:56.1', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
@@ -563,11 +588,15 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YYYY-MM-DD HH24:MI:SS.FF' ||
SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
+SELECT i, cast('2018-11-02 12:34:56.123456789' as timestamptz format 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i) FROM generate_series(1, 6) i;
SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored
+SELECT cast('1 4 1902' as date format 'Q MM YYYY'); -- Q is ignored
SELECT to_date('3 4 21 01', 'W MM CC YY');
+SELECT cast('3 4 21 01' as date format 'W MM CC YY');
SELECT to_date('2458872', 'J');
+SELECT cast('2458872' as date format 'J');
--
-- Check handling of BC dates
@@ -677,7 +706,9 @@ SELECT to_date('2147483647 01', 'CC YY');
-- to_char's TZ format code produces zone abbrev if known
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ');
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS tz');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS tz');
--
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
@@ -692,6 +723,17 @@ SELECT '2012-12-12 12:00'::timestamptz;
SELECT '2012-12-12 12:00 America/New_York'::timestamptz;
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ'),
+ to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+
+SELECT cast('2012-12-12 12:00'::date as text format 'YYYY-MM-DD HH:MI:SS TZ'),
+ to_char('2012-12-12 12:00'::date, 'YYYY-MM-DD HH:MI:SS TZ');
+
+SELECT cast('12:00'::time as text format 'HH:MI:SS'),
+ to_char('12:00'::time, 'HH:MI:SS');
+
+SELECT cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ');
+
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS');
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS');
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 43bc793925e..a5b4d63e9a4 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -801,7 +801,9 @@ SELECT 'infinity'::interval::time;
SELECT '-infinity'::interval::time;
SELECT to_char('infinity'::interval, 'YYYY');
+SELECT cast('infinity'::interval as text format 'YYYY');
SELECT to_char('-infinity'::interval, 'YYYY');
+SELECT cast('-infinity'::interval as text format 'YYYY');
-- "ago" can only appear once at the end of an interval.
SELECT INTERVAL '42 days 2 seconds ago ago';
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 640c6d92f4c..fd7bc26887f 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1065,34 +1065,58 @@ SELECT to_char('100'::numeric, 'f"ool\\"999');
--
SET lc_numeric = 'C';
SELECT to_number('-34,338,492', '99G999G999');
+SELECT CAST('-34,338,492' as numeric FORMAT '99G999G999');
SELECT to_number('-34,338,492.654,878', '99G999G999D999G999');
+SELECT CAST('-34,338,492.654,878' as numeric FORMAT '99G999G999D999G999');
SELECT to_number('<564646.654564>', '999999.999999PR');
+SELECT CAST('<564646.654564>' as numeric FORMAT '999999.999999PR');
SELECT to_number('0.00001-', '9.999999S');
+SELECT CAST('0.00001-' as numeric FORMAT '9.999999S');
SELECT to_number('5.01-', 'FM9.999999S');
+SELECT CAST('5.01-' as numeric FORMAT 'FM9.999999S');
SELECT to_number('5.01-', 'FM9.999999MI');
+SELECT CAST('5.01-' as numeric FORMAT 'FM9.999999MI');
SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
+SELECT CAST('5 4 4 4 4 8 . 7 8' as numeric FORMAT '9 9 9 9 9 9 . 9 9');
SELECT to_number('.01', 'FM9.99');
+SELECT CAST('.01' as numeric FORMAT 'FM9.99');
SELECT to_number('.0', '99999999.99999999');
+SELECT CAST('.0' as numeric FORMAT '99999999.99999999');
SELECT to_number('0', '99.99');
+SELECT CAST('0' as numeric FORMAT '99.99');
SELECT to_number('.-01', 'S99.99');
+SELECT CAST('.-01' as numeric FORMAT 'S99.99');
SELECT to_number('.01-', '99.99S');
+SELECT CAST('.01-' as numeric FORMAT '99.99S');
SELECT to_number(' . 0 1-', ' 9 9 . 9 9 S');
+SELECT CAST(' . 0 1-' as numeric FORMAT ' 9 9 . 9 9 S');
SELECT to_number('34,50','999,99');
+SELECT CAST('34,50' as numeric FORMAT '999,99');
SELECT to_number('123,000','999G');
+SELECT CAST('123,000' as numeric FORMAT '999G');
SELECT to_number('123456','999G999');
+SELECT CAST('123456' as numeric FORMAT '999G999');
SELECT to_number('$1234.56','L9,999.99');
+SELECT CAST('$1234.56' as numeric FORMAT 'L9,999.99');
SELECT to_number('$1234.56','L99,999.99');
+SELECT CAST('$1234.56' as numeric FORMAT 'L99,999.99');
SELECT to_number('$1,234.56','L99,999.99');
+SELECT CAST('$1,234.56' as numeric FORMAT 'L99,999.99');
SELECT to_number('1234.56','L99,999.99');
+SELECT CAST('1234.56' as numeric FORMAT 'L99,999.99');
SELECT to_number('1,234.56','L99,999.99');
+SELECT CAST('1,234.56' as numeric FORMAT 'L99,999.99');
SELECT to_number('42nd', '99th');
+SELECT CAST('42nd' as numeric FORMAT '99th');
SELECT to_number('123456', '99999V99');
+SELECT CAST('123456' as numeric FORMAT '99999V99');
-- Test for correct conversion between numbers and Roman numerals
WITH rows AS
(SELECT i, to_char(i, 'RN') AS roman FROM generate_series(1, 3999) AS i)
SELECT
- bool_and(to_number(roman, 'RN') = i) as valid
+ bool_and(to_number(roman, 'RN') = i) as valid,
+ bool_and(cast(roman as numeric format 'RN') = i) as valid
FROM rows;
-- Some additional tests for RN input
--
2.34.1
