Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions - Mailing list pgsql-hackers

On Tue, Jul 22, 2025 at 8:26 PM Vik Fearing <vik@postgresfriends.org> wrote:
>
>
> On 22/07/2025 12:19, jian he wrote:
> > On Tue, Jul 22, 2025 at 2:45 PM Vik Fearing <vik@postgresfriends.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 ERROR)
> >
> > if so, what's the restriction of default_expression?
>
>
> The actual syntax is:
>
>
> <cast specification> ::=
>      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> ::=
>      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.