Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wf21w-005DaH-12 for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Jul 2026 20:59:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wf21v-00Fh2H-0T for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Jul 2026 20:59:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wf21u-00Fh29-1w for pgsql-hackers@lists.postgresql.org; Wed, 01 Jul 2026 20:59:34 +0000 Received: from mail-yx1-xb12a.google.com ([2607:f8b0:4864:20::b12a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wf21r-000000018iN-45An for pgsql-hackers@postgresql.org; Wed, 01 Jul 2026 20:59:33 +0000 Received: by mail-yx1-xb12a.google.com with SMTP id 956f58d0204a3-664dd23829eso1059720d50.3 for ; Wed, 01 Jul 2026 13:59:31 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1782939571; cv=none; d=google.com; s=arc-20260327; b=XeE47k8lMyYZqk3aGf60LgVChlF485833Gir4zb+CVqNrOIV5XE9gcE/9dAjNvuIcg 1g2ocCf8UlNqBuJDs1J3RZSIzC169gevSlvgH9N50JAw/x2knBb0/rKDZJ4REmI3fQqT 2TEQKeEsE43VCA2yTpCk+nY98Zg7uXgb2cLYVyRC8iOo/6QXp62Sa9u+EGS/wXgS4nXv Ym1HXNFDBlLvi0YVHi+/pB7I6SbBxA2Wv4nL0nJ1SON0VDyDfMuGTZONmDTNju9tD0Ok HF38WKp0yQHomcfM1Tl7EgA3Ib0oRy1Z9X64lSemJgNBgf4fspuWBmFOOt+cht7H/5qR 0S3g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20260327; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=YoEcO3l1gxuwcFjHMgqgYxTgY62F2CZWDwBy7QVs7OQ=; fh=1sRH0WTbGaTuXJxtmX0Ri3q3H2UKDWpbJtFqkt2iH9o=; b=H9ASAtuRQGG7rki4asau89gUGb0zCjNfObbhGC+jGzbvd4TAeFc9u7wUMbZRC33sLC ib4viYUchhZt7rxFriy/Ft/49Glhgq9HCLvkcO0/CHoVHV/szY/y4cVchaQMfPsc/SP7 gj0NCdlBtrA55CLQnmgjKl8/+B5nQgF3zUur8cH2KdtaJT7KRKInyf/nSy9/K2hrEmzR SyxK3DfSPPO1gI7lrUcInmFk4YnO8juWYegK9cpuaaM7D+VUNzZAgaWCrEm8rrRFJiSx kx1IIHqtIOe9uR3RofqkErQooPVsMGBBJ51nOyXJV2x0/a8L+xbKfG6LHz88qykXrrkq IKgQ==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1782939571; x=1783544371; darn=postgresql.org; h=content-type:cc:to:subject:message-id:date:from:in-reply-to :references:mime-version:from:to:cc:subject:date:message-id:reply-to :content-type; bh=YoEcO3l1gxuwcFjHMgqgYxTgY62F2CZWDwBy7QVs7OQ=; b=EFLTz1Adgm+0qRRNdOc2A2ZLmervVBO0nt6qff4Mbj8QAqItD4Z8dKBdMtmMqKdsll lfzU3T8Cb6tUMRyl2xyhPHjQWjhpJqt9xg694Fz1kEg+CWymHTnSEZW5OsW/dBZv1IpU VvF5gMNlGyp+l6YEIbUCc1kuNjFhI02Ys0LEeORZSdJJYMx4yW4CT8fUdTRQdi9q1fjO wxO+hvCcE5Aw7BVBnv4Oiw0iXW1Li2oUzzucqwCv0Yevix89gdwO76wHhJRoBiYFJIZb S34fHx/pgI3dpxGu93Sep+VyMAuWiPOmxioaxB4ww3xlgpgA+ERSpG1TxBUeP8f14D57 S1gQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1782939571; x=1783544371; h=content-type:cc:to:subject:message-id:date:from:in-reply-to :references:mime-version:x-gm-gg:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to:content-type; bh=YoEcO3l1gxuwcFjHMgqgYxTgY62F2CZWDwBy7QVs7OQ=; b=W218kKw8lfPgvemgj9S6CutaoDMqGTs8ybJRBAg4MOHThPdpZTQ+++ec7i3JZcuvak mdG3jnmzDQeKbAsDGj5opPCqs1EwmnN8i+oMinJneibqixhGL58HMPqL4wmd/BnlWHlv c2SgQov69N7QOxpg9A0EJYfD7K5jv0mZPi5QbL8hOmWDFz+fWNYrzfABSctiKp41z5Vw 8pcOvMaGz1Aeyb9Pg5HLw9SZ9DsIy4JUdf4bFjk2kHkJ+DkGIO+JKSCSULtjlDLPc26s FMtSzL428sNovJgA/xQ/BIRU9zNB/bAoJZ1TkUzRwcaI/zHtRX3CSfEIYTpOBOX9gUGl oz4g== X-Forwarded-Encrypted: i=1; AHgh+RrEanZypLnwY7TimRK0r7RBaudhO61oDb0tT47sT9+ECsj/ERrLvbAMvA42aajtYmVe6beiJZ0SeCQgj/mV@postgresql.org X-Gm-Message-State: AOJu0Yxyi3DUYyZgVi1Uefn3UwghtsJD+lp0z/L5pMBjLrb249wJaGkO haUG36h49DBI9lJIjpPpVUtTpKr+uXwRTKXIPcFYG5lpkQTyts/RLFCaW/a+GnPpY8ndRanV1ww 1Jl9cEFaZVIzxegHmWaEI5XJVWTbtMZA= X-Gm-Gg: AfdE7cmiJf3s59SW9Yats/CAI5Xvuqx4I/CpKxc1rkNVJOHayExPluAMng7/e2+1PLl vFQXgsWGu4Js7Yt9jVq4Uqf7D4aqwd3JpN7zVeuzzovPLx9zTXvPP9gkoenrcDoq5raKJZAiHk5 HGYQ/Xt06NGCv0xNLkQLxeB1frWUD5h5lLNlPDbnPLG0Tem/OBQFk7/jmkBqDm1GEHiMg+PYbRX /0miQ9rhMasZ+GEt7+vGXrsy2oNP95Uu4nBojw3mAWRl8YRafPIV2nuC0v1I+1/xJea5n46RVTQ FzEukx4NYdiceuvpX+cN1GpsHqIa X-Received: by 2002:a05:690e:4105:b0:663:1161:cf4f with SMTP id 956f58d0204a3-6659653c552mr2529158d50.29.1782939570984; Wed, 01 Jul 2026 13:59:30 -0700 (PDT) MIME-Version: 1.0 References: <4c358bf8-87e8-4760-83d5-a6f9fcd88907@postgresfriends.org> <1b702de4-86b0-4144-a2d0-38266296894f@postgresfriends.org> <82b4223e-84d4-432d-8167-dd698ce489f5@postgresfriends.org> In-Reply-To: From: "David G. Johnston" Date: Wed, 1 Jul 2026 13:58:55 -0700 X-Gm-Features: AVVi8CcD214hqFHsYhQ4z4G70s1Kl7gF4XjUwocJWz1q3qKkDbXhHViiJmzhmuQ Message-ID: Subject: Re: implement CAST(expr AS type FORMAT 'template') To: Haibo Yan Cc: jian he , Robert Haas , Corey Huinker , Zsolt Parragi , Vik Fearing , PostgreSQL-development Content-Type: multipart/alternative; boundary="000000000000c8da5a065592f782" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c8da5a065592f782 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jul 1, 2026 at 9:45=E2=80=AFAM Haibo Yan wr= ote: > Hi Jian, > > Thanks for looking at this. > > On Wed, Jul 1, 2026 at 6:45=E2=80=AFAM jian he > wrote: > > > > On Wed, Jul 1, 2026 at 1:39=E2=80=AFAM Haibo Yan wrote: > > > > > > Thanks for looking at the patches. > > > I liked your suggestion to call these =E2=80=9Cformat casts=E2=80=9D = rather than > > > =E2=80=9Cformatters=E2=80=9D, so I changed the series in that directi= on. 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 test= s > > > 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 form= at > 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 cas= e: > > > > 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=3D# 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=3D# 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. --000000000000c8da5a065592f782 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jul 1, 2026 at 9:45=E2=80=AFAM Haibo Y= an <tristan.yim@gmail.com> wrote:
Hi Jian,

Thanks for looking at this.

On Wed, Jul 1, 2026 at 6:45=E2=80=AFAM jian he <jian.universality@gmail.com>= ; wrote:
>
> On Wed, Jul 1, 2026 at 1:39=E2=80=AFAM Haibo Yan <tristan.yim@gmail.com> wro= te:
> >
> > Thanks for looking at the patches.
> > I liked your suggestion to call these =E2=80=9Cformat casts=E2=80= =9D rather than
> > =E2=80=9Cformatters=E2=80=9D, so I changed the series in that dir= ection.=C2=A0 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 us= es
> > pg_format_cast and CoerceViaFormatCast.=C2=A0 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 st= ill
> > catalog-driven, and both built-in and user-defined cases go throu= gh
> > the same lookup path.
> >
>
> In an earlier thread [1], [2], I proposed introducing:
>
> CREATE CAST (source_type AS target_type)
>=C2=A0 =C2=A0 =C2=A0WITH [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)
>=C2=A0 =C2=A0 =C2=A0WITH FUNCTION function_name [ (argument_type [, ...= ]) ]
>
> This syntax is quite close to CREATE CAST.
> per https://www.postgresql.org/docs/de= vel/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 i= s a
little different.=C2=A0 This patch creates a separate object kind rather th= an 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.=C2=A0 Ordinary casts live = in
pg_cast and are tied to things like cast context and cast method.=C2=A0 A f= ormat
cast is only considered for

CAST(expr AS type FORMAT fmt)= =C2=A0

and not for ordinary casts without a FORMAT clause, assignment casts, or implicit casts.=C2=A0 It also always needs a function whose second argument= is the
FORMAT expression, not the destination typmod argument used by ordinary cas= t
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_attri= bute are modified.=C2=A0 It seems quite reasonable that executing "cre= ate cast" causes both pg_cast and pg_cast_format to be populated.

With two separate commands a cast could exist on pg_cast_f= ormat that doesn't exist in pg_cast.=C2=A0 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)
>=C2=A0 =C2=A0 =C2=A0WITH FUNCTION function_name [ (argument_type [, ...= ]) ]
>=C2=A0 =C2=A0 =C2=A0AS FORMAT
> ----------------------------------------------------------------------= ------------------
> As I mentioned previously [3], there's an open question around thi= s 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 ap= plied to each
> element (2022-01-01, 2022-21-01) individually via the cast format func= tion?

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 form= at clause should make when performing the cast is allowing a cast that woul= d fail due to a syntax error to succeed.

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

postgres=3D# select= cast(cast('{"2025T01-01","2026T02-02","2027T0= 3-03"}'::text as
text[]) as date[]);
ERROR: =C2=A0invalid i= nput syntax for type date: "2025T01-01"

A fo= rmat clause would make the second query not error with an appropriate forma= t specification - in which case it then behaves identically to the first qu= ery.

If the=C2=A0SQL Standard somehow contradicts this= intent I'd be curious to understand what it=C2=A0does intend.
=
David J.

--000000000000c8da5a065592f782--