On Tue, Mar 31, 2026 at 6:18 AM Zsolt Parragi <[email protected]> wrote:
>
> v6 is better, but I found a few more questions:
>
> +select pg_get_viewdef('tcast_v1', true);
> + pg_get_viewdef
> +--------------------------------------------------------------------------------------------
> + SELECT CAST( col1 AS date FORMAT 'YYYY-MM-DD'::text) AS to_date,
> +
> Is that space after "CAST(" intentional?
>
I have removed this white spce.
> + format = coerce_to_target_type(pstate, fmt,
> + exprType(fmt), TEXTOID,
> + exprTypmod(fmt),
> + ccontext, cformat,
> + exprLocation(fmt));
>
> Is exprTypmod(fmt) correct, shouldn't it use -1?
>
yes. it should be -1.
> This one also shows a not so userfriendly error:
>
> postgres=# SELECT CAST('hello' AS name FORMAT 'test') ;
>
> SELECT CAST('hello' AS bpchar FORMAT 'test') ;
now:
SELECT CAST('hello' AS name FORMAT 'test') ;
ERROR: cannot cast type text to name using formatted template
SELECT CAST('hello' AS bpchar FORMAT 'test') ;
ERROR: cannot cast type text to character while using a format template
LINE 1: SELECT CAST('hello' AS bpchar FORMAT 'test') ;
^
DETAIL: binary coercible type cast is not supported while using a
format template
type Text to type name is not binary coercible, see outpout of:
select *, castfunc::regproc from pg_cast
where castsource = 'text'::regtype and casttarget = 'name'::regtype;
--
jian
https://www.enterprisedb.com/
From e07a575e81069a91ff7b9168cd4585ac5d4d10bf Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 31 Mar 2026 15:45:17 +0800
Subject: [PATCH v7 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 | 325 ++++++++++++++++-
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 | 329 ++++++++++++++++++
.../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 | 114 ++++++
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, 1257 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..70c45e011bf 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,242 @@ 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;
+ s_typcategory = TYPCATEGORY_STRING;
+ }
+
+ /*
+ * 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: errdetail 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)),
+ errdetail("Only timestamptz, text, numeric and date data type are supported for formatted type casting"),
+ parser_coercion_errposition(pstate, location, node));
+ break;
+ }
+
+ /*
+ * 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)));
+
+ /*
+ * 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, -1,
+ ccontext, cformat,
+ exprLocation(fmt));
+
+ 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 +414,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 +434,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 +459,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 +493,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 +538,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..040eabe09bd 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..e08714bed93
--- /dev/null
+++ b/src/test/regress/expected/cast.out
@@ -0,0 +1,329 @@
+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('hello' as name format 'test');
+ERROR: cannot cast type text to name using formatted template
+select cast('hello' as bpchar format 'test');
+ERROR: cannot cast type text to character while using a format template
+LINE 1: select cast('hello' as bpchar format 'test');
+ ^
+DETAIL: binary coercible type cast is not supported while using a format template
+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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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');
+ ^
+DETAIL: 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-...
+ ^
+DETAIL: 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...
+ ^
+DETAIL: 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...
+ ^
+DETAIL: 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)
+
+explain (verbose, costs off)
+select cast(col5::float8 as text format '9.99EEEE') as to_txt1,
+ cast(col5::numeric as text format '9.99EEEE') as to_txt2
+from tcast;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on public.tcast
+ Output: CAST((col5)::double precision AS text FORMAT '9.99EEEE'::text), CAST((col5)::numeric AS text FORMAT '9.99EEEE'::text)
+(2 rows)
+
+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..e7062f44e42
--- /dev/null
+++ b/src/test/regress/sql/cast.sql
@@ -0,0 +1,114 @@
+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('hello' as name format 'test');
+select cast('hello' as bpchar format 'test');
+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);
+
+explain (verbose, costs off)
+select cast(col5::float8 as text format '9.99EEEE') as to_txt1,
+ cast(col5::numeric as text format '9.99EEEE') as to_txt2
+from tcast;
+
+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
