MIME-Version: 1.0
References: 
 <CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com>
 <CAMsGm5dpfm2PHL8XZvC-JSd+UPkgx3rpReUA=G=4+rUCH+Ntcw@mail.gmail.com>
 <CADkLM=eD_S8mGhPfu5+hXXvXgR0-cxGpGd9dgPzD+nCuO7HFaQ@mail.gmail.com>
 <CACJufxHCMzrHOW=wRe8L30rMhB3sjwAv1LE928Fa7sxMu1Tx-g@mail.gmail.com>
 <04afcd1f-ed7d-4c0a-add1-50e3719ccbf9@postgresfriends.org>
 <CACJufxGRAnwJzu7nMq4ZP=yqa1Sz=qR+mR1TmY0aCDjJoJRRtg@mail.gmail.com>
 <762ae707-7fdc-43d8-a77a-3a10d12ce21d@postgresfriends.org>
In-Reply-To: <762ae707-7fdc-43d8-a77a-3a10d12ce21d@postgresfriends.org>
From: jian he <jian.universality@gmail.com>
Date: Thu, 24 Jul 2025 21:44:22 +0800
Message-ID: 
 <CACJufxHhUD4u7PXUzT1T6hM9ZcQyj3eTyLMQmzREj+_5ca0_ug@mail.gmail.com>
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User
 Functions
To: Vik Fearing <vik@postgresfriends.org>
Cc: Corey Huinker <corey.huinker@gmail.com>,
 Isaac Morland <isaac.morland@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxHhUD4u7PXUzT1T6hM9ZcQyj3eTyLMQmzREj%2B_5ca0_ug%40mail.gmail.com>
Precedence: bulk

On Tue, Jul 22, 2025 at 8:26=E2=80=AFPM Vik Fearing <vik@postgresfriends.or=
g> wrote:
>
>
> On 22/07/2025 12:19, jian he wrote:
> > On Tue, Jul 22, 2025 at 2:45=E2=80=AFPM Vik Fearing <vik@postgresfriend=
s.org> wrote:
> >> It was accepted into the standard after 2023 was released.  I am the
> >> author of this change in the standard, so feel free to ask me anything
> >> you're unsure about.
> >>
> > is the generally syntax as mentioned in this thread:
> > CAST(source_expression AS target_type DEFAULT default_expression ON ERR=
OR)
> >
> > if so, what's the restriction of default_expression?
>
>
> The actual syntax is:
>
>
> <cast specification> ::=3D
>      CAST <left paren>
>          <cast operand> AS <cast target>
>          [ FORMAT <cast template> ]
>          [ <cast error behavior> ON CONVERSION ERROR ]
>          <right paren>
>
> "CONVERSION" is probably a noise word, but it is there because A) Oracle
> wanted it there, and B) it makes sense because if the <cast error
> behavior> fails, that is still a failure of the entire CAST.
>
>
> The <cast error behavior> is:
>
>
> <cast error behavior> ::=3D
>      ERROR
>    | NULL
>    | DEFAULT <value expression>
>
>

hi.

just want to confirm my understanding of ``[ FORMAT <cast template> ]``.

SELECT CAST('2022-13-32' AS DATE FORMAT 'YYYY-MM-DD' DEFAULT NULL ON
CONVERSION ERROR);
will return NULL.
because  ``SELECT to_date('2022-13-32', 'YYYY-MM-DD');``
will error out, so the above query will fall back to the DEFAULT
expression evaluation.