pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedExclusion constraint with negated operator?
2+ messages / 2 participants
[nested] [flat]
* Exclusion constraint with negated operator?
@ 2023-06-13 18:11 Torsten Förtsch <tfoertsch123@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Torsten Förtsch @ 2023-06-13 18:11 UTC (permalink / raw)
To: pgsql-general
Hi,
I wanted to create an exclusion constraint like
EXCLUDE (c1 WITH =, c2 with <>)
This gives an error:
operator <>(integer,integer) is not a member of operator family
"integer_ops"
I can resolve that by using gist and the btree_gist extension.
My question is can I somehow express something like
EXCLUDE (c1 WITH =, c2 with NOT =)
It seems that's not possible at the moment. But is there any obstacle in
principle or is it just not implemented?
We have a ton of overlap operators (geometric, ranges, arrays) but none for
the opposite. Sometimes that's useful for exclusion constraints.
Thanks,
Torsten
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Exclusion constraint with negated operator?
@ 2023-06-13 20:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Torsten Förtsch <tfoertsch123@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-06-13 20:15 UTC (permalink / raw)
To: Torsten Förtsch <tfoertsch123@gmail.com>; +Cc: pgsql-general
=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> My question is can I somehow express something like
> EXCLUDE (c1 WITH =, c2 with NOT =)
> It seems that's not possible at the moment. But is there any obstacle in
> principle or is it just not implemented?
Well, it'd likely be a bad idea. Indexes are meant to help you quickly
find a small part of a table that satisfies a condition. Finding the
probably-much-larger part of the table that doesn't satisfy the condition
is something they are bad at. This is why "=" is an indexable operator
while "<>" is not. It's not impossible in principle for "<>" to be an
index operator, but the set of cases where indexing on such a condition
would beat a seqscan is likely to be uselessly small. By the same
token, EXCLUDE constraints using such a condition would be unpleasantly
inefficient.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-06-13 20:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-13 18:11 Exclusion constraint with negated operator? Torsten Förtsch <tfoertsch123@gmail.com>
2023-06-13 20:15 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox