From: KES <kes-kes@yandex.ru>
Envelope-From: kes-kes@yandex.com
To: Tom Lane <tgl@sss.pgh.pa.us>,
	Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
In-Reply-To: <12107.1533736288@sss.pgh.pa.us>
References: <153121527691.1408.5686988620817799073@wrigleys.postgresql.org>
 <20180807173659.GD7297@momjian.us>
 <31616681533725753@sas1-d856b3d759c7.qloud-c.yandex.net>
 <20180808130040.GA2611@momjian.us> <12107.1533736288@sss.pgh.pa.us>
Subject: Re: Typo in doc or wrong EXCLUDE implementation
MIME-Version: 1.0
Date: Thu, 09 Aug 2018 13:11:05 +0300
Message-Id: <37593701533809465@sas1-87f9feb8d943.qloud-c.yandex.net>
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=utf-8
Precedence: bulk

Bruce:
>Yes, it would work, but doing that only for equality would be surprising
 to many people 

Why surprising? It is [documented](https://www.postgresql.org/docs/current/static/sql-createtable.html#sql-createtable-exclude):
>If all of the specified operators test for equality, this is equivalent to a UNIQUE constraint, although an ordinary unique constraint will be faster.

Thus the UNIQUE constraint is just particular case of exclusion constraint, is not?

Tom
>It's less efficient (1) and less portable
Yes, portability has matter, but more general SQL would be more efficient at developer hours to support such application in compare to writing many particular SQL's (one SQL expression is better than two which do same job). Personally I would close the eyes on portability in favor of using modern features (looking forward for inclusion constraint)

For speed efficiency (1) this particular case of exclusion constraint can be implemented via btree-based uniqueness. (like uniqueness is implemented via indexes under the hood. but the implementaion details have no matter as for me)


08.08.2018, 16:51, "Tom Lane" <tgl@sss.pgh.pa.us>:
> Bruce Momjian <bruce@momjian.us> writes:
>>  On Wed, Aug 8, 2018 at 01:55:53PM +0300, KES wrote:
>>>  If such exclusion constraint would be marked as UNIQUE we can use it for FK while implementing temporal/bi-temporal tables.
>
>>  Yes, it would work, but doing that only for equality would be surprising
>>  to many people because exclusion constraints are more general than
>>  equality comparisons.
>
> In general, we should be discouraging people from using EXCLUDE syntax
> with simple equality operators, not encouraging them to do so. It's
> less efficient and less portable than a regular btree-based uniqueness
> constraint. So I think this proposal is a bad idea regardless of
> whether it'd be technically feasible or not.
>
>                         regards, tom lane