Message-ID: <762ae707-7fdc-43d8-a77a-3a10d12ce21d@postgresfriends.org>
Date: Tue, 22 Jul 2025 14:26:25 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User
 Functions
To: jian he <jian.universality@gmail.com>
Cc: Corey Huinker <corey.huinker@gmail.com>,
 Isaac Morland <isaac.morland@gmail.com>, pgsql-hackers@lists.postgresql.org
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>
Content-Language: en-US
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: 
 <CACJufxGRAnwJzu7nMq4ZP=yqa1Sz=qR+mR1TmY0aCDjJoJRRtg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/762ae707-7fdc-43d8-a77a-3a10d12ce21d%40postgresfriends.org>
Precedence: bulk


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>


but I am planning on removing the NULL variant in favor of having the 
<value expression> be a <contextually typed value specification>.  So it 
would be either ERROR ON CONVERSION ERROR (postgres's current behavior), 
or DEFAULT NULL ON CONVERSION ERROR.


An example of B) above would be: CAST('five' AS INTEGER DEFAULT 'six' ON 
CONVERSION ERROR).  'six' is no more an integer than 'five' is, so that 
would error out because the conversion error does not happen on the 
operand but on the default clause. CAST('five' AS INTEGER DEFAULT 6 ON 
CONVERSION ERROR) would work.

-- 

Vik Fearing