Re: implement CAST(expr AS type FORMAT 'template')

On Wed, Jul 1, 2026 at 9:45 AM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:

> Hi Jian,
>
> Thanks for looking at this.
>
> On Wed, Jul 1, 2026 at 6:45 AM jian he <jian(dot)universality(at)gmail(dot)com>
> wrote:
> >
> > On Wed, Jul 1, 2026 at 1:39 AM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:
> > >
> > > Thanks for looking at the patches.
> > > I liked your suggestion to call these “format casts” rather than
> > > “formatters”, so I changed the series in that direction. The DDL is
> > > now:
> > > ------------------------------------------------------
> > > CREATE FORMAT CAST (...)
> > > DROP FORMAT CAST (...)
> > > ------------------------------------------------------
> > > rather than CREATE/DROP FORMATTER, so the patch no longer adds
> > > FORMATTER as a keyword.
> > > I also renamed the related catalog and node names, so this now uses
> > > pg_format_cast and CoerceViaFormatCast. The
> > > pg_dump/object-address/comment/extension code and the regression tests
> > > have been updated to use the new terminology as well.
> > > The rest of the design is the same as before: format casts are still
> > > catalog-driven, and both built-in and user-defined cases go through
> > > the same lookup path.
> > >
> >
> > In an earlier thread [1], [2], I proposed introducing:
> >
> > CREATE CAST (source_type AS target_type)
> > WITH [SAFE] FUNCTION function_name [ (argument_type [, ...]) ]
> >
> > That drew some pushback over non-standard conformance.
> > Here, we are introducing
> >
> > CREATE FORMAT CAST (source_type AS target_type)
> > WITH FUNCTION function_name [ (argument_type [, ...]) ]
> >
> > This syntax is quite close to CREATE CAST.
> > per https://www.postgresql.org/docs/devel/sql-createcast.html
> > CREATE FORMAT CAST would be an extension to the standard, though we
> > already have non-standard precedent: AS IMPLICIT.
> >
> > Before proceeding, do we need to reach consensus on the syntax itself?
>
> yes, I agree that we should get agreement on the DDL syntax before going
> much further.
>
> I see the analogy with the earlier SAFE discussion, but I think this case
> is a
> little different. This patch creates a separate object kind rather than
> adding
> another option to ordinary CREATE CAST
>
> >
> > Does the following alternative syntax make sense for CAST FORMAT?
>
> My reason for preferring CREATE FORMAT CAST is that a format cast is not
> really an ordinary cast with one more attribute. Ordinary casts live in
> pg_cast and are tied to things like cast context and cast method. A format
> cast is only considered for
>
> CAST(expr AS type FORMAT fmt)

> and not for ordinary casts without a FORMAT clause, assignment casts, or
> implicit casts. It also always needs a function whose second argument is
> the
> FORMAT expression, not the destination typmod argument used by ordinary
> cast
> functions.
> So I think
>
> CREATE CAST (...) WITH FUNCTION ... AS FORMAT
>
> would make this look more like a pg_cast entry than it really is.

I think the fact the standard put this inside the 'cast(...)' means it's
quite reasonable to consider the aspect part of a cast definition as
opposed to something wholly different.

When we issue "create table" both pg_class and pg_attribute are modified.
It seems quite reasonable that executing "create cast" causes both pg_cast
and pg_cast_format to be populated.

With two separate commands a cast could exist on pg_cast_format that
doesn't exist in pg_cast. I'm unsure whether this is allowed or
desired...but seems a bit unexpected to me at least.

> >
> > CREATE CAST (source_type AS target_type)
> > WITH FUNCTION function_name [ (argument_type [, ...]) ]
> > AS FORMAT
> >
> ----------------------------------------------------------------------------------------
> > As I mentioned previously [3], there's an open question around this case:
> >
> > CAST('{2022-01-01, 2022-21-01}' AS DATE[] FORMAT 'YYYY-DD-MM');
> >
> > does this mean the format template ('YYYY-DD-MM') should be applied to
> each
> > element (2022-01-01, 2022-21-01) individually via the cast format
> function?
>

Yes, the internal array formatting structure used by PostgreSQL isn't
(needn't be) user-configurable; the useful thing for the format to apply to
is the value of the elements of the array.

IMO the only change the addition of a format clause should make when
performing the cast is allowing a cast that would fail due to a syntax
error to succeed.

postgres=# select
cast(cast('{"2025-01-01","2026-02-02","2027-03-03"}'::text as
text[]) as date[]);
date
------------------------------------
{2025-01-01,2026-02-02,2027-03-03}
(1 row)

postgres=# select
cast(cast('{"2025T01-01","2026T02-02","2027T03-03"}'::text as
text[]) as date[]);
ERROR: invalid input syntax for type date: "2025T01-01"

A format clause would make the second query not error with an appropriate
format specification - in which case it then behaves identically to the
first query.

If the SQL Standard somehow contradicts this intent I'd be curious to
understand what it does intend.

David J.