Message-ID: <7d44dade-ff31-5eef-80ac-78ed838066f6@postgresfriends.org>
Date: Fri, 1 Sep 2023 12:50:08 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.13.0
Subject: Re: SQL:2011 application time
Content-Language: en-US
To: Peter Eisentraut <peter@eisentraut.org>,
 Paul Jungwirth <pj@illuminatedcomputing.com>
Cc: Corey Huinker <corey.huinker@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <CA+renyUMKg4-MyVbAi8Kj12Srd9BkU5Gt3FdbGdNr-ezHpL6cw@mail.gmail.com>
 <5650d77a-1d73-fd8d-4410-8e97dbeff572@illuminatedcomputing.com>
 <CA+renyWqpNdsi9OaPmXheeQL72adi_iz36VgVsqyyGpQ20FDAg@mail.gmail.com>
 <21964319-46e5-3e47-217b-6ac4169bdf13@enterprisedb.com>
 <CA+renyX0ipvY6A_jUOHeB1q9mL4bEYfAZ5FBB7G7jUo5bykjrA@mail.gmail.com>
 <f42e5be5-cd3e-bc4f-83e4-04a1e0270b17@enterprisedb.com>
 <27361388-f5ab-ea36-ea35-41d68a90e60d@illuminatedcomputing.com>
 <152e4c2c-36b4-bdc0-1b62-e9f9e8b68adc@enterprisedb.com>
 <251B44D1-D3E0-47E4-AB9D-4A848EDB495E@yesql.se>
 <1c674adf-4114-e8d8-cec7-ac10e2f424c7@enterprisedb.com>
 <CA+renyVyMOb+t6QNoq=PBK1CY_oa9wdJrObjj6S1-RK9CxYwKQ@mail.gmail.com>
 <831f17ba-ff36-1380-a475-a7cd2c65a89c@enterprisedb.com>
 <ab4cf49c-9839-3426-5114-f596fa9ee76b@illuminatedcomputing.com>
 <6f010a6e-8e20-658b-dc05-dc9033a694da@eisentraut.org>
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: <6f010a6e-8e20-658b-dc05-dc9033a694da@eisentraut.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7d44dade-ff31-5eef-80ac-78ed838066f6%40postgresfriends.org>
Precedence: bulk

On 9/1/23 11:30, Peter Eisentraut wrote:
> 1) If I write UNIQUE (a, b, c WITHOUT OVERLAPS), does the WITHOUT 
> OVERLAPS clause attach to the last column, or to the whole column list? 
> In the SQL standard, you can only have one period and it has to be 
> listed last, so this question does not arise.  But here we are building 
> a more general facility to then build the SQL facility on top of.  So I 
> think it doesn't make sense that the range column must be last or that 
> there can only be one.  Also, your implementation requires at least one 
> non-overlaps column, which also seems like a confusing restriction.
> 
> I think the WITHOUT OVERLAPS clause should be per-column, so that 
> something like UNIQUE (a WITHOUT OVERLAPS, b, c WITHOUT OVERLAPS) would 
> be possible.  Then the WITHOUT OVERLAPS clause would directly correspond 
> to the choice between equality or overlaps operator per column.
> 
> An alternative interpretation would be that WITHOUT OVERLAPS applies to 
> the whole column list, and we would take it to mean, for any range 
> column, use the overlaps operator, for any non-range column, use the 
> equals operator.  But I think this would be confusing and would prevent 
> the case of using the equality operator for some ranges and the overlaps 
> operator for some other ranges in the same key.

I prefer the first option.  That is: WITHOUT OVERLAPS applies only to 
the column or expression it is attached to, and need not be last in line.
-- 
Vik Fearing