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

On Wed, Jul 15, 2026 at 10:44 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Wed, Jul 15, 2026 at 5:11 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> > src8=# select cast('2012-13-12' as date format 'YYYY-MM-DD' );
> > ERROR: date/time field value out of range: "2012-13-12"
> >
> > With this example, I hope I've convinced you that it's still worthwhile to have
> > the parser build a FuncExpr for CAST ... FORMAT, even though that same FuncExpr
> > could otherwise be produced by calling the to_* function directly.
>
> How does this show that? Why not just call the to_* function directly?
>

you are right.

select cast('2012-13-12' as date format 'YYYY-MM-DD' default null on
conversion error);

We can do the same with:

select cast(to_date('2012-13-12', 'YYYY-MM-DD') as date default null
on conversion error);

However, this seems a bit strange.
Since to_date already returns a DATE, it's unlikely that anyone would
explicitly cast a DATE to DATE.
We can document that this approach provides an error-safe way to
evaluate these to_* functions.
(After we refactored these to_* function to be error safe).