I'm trying to create a table with a path column. This table should only allow inserting a new path if the new path is neither an ancestor nor a descendant of any already inserted path. i.e. with '/foo/bar' already in the table: - inserting '/foo/bar' should fail - inserting '/foo/bar/baz' should fail - inserting '/foo/bar/baz/bat' should fail - inserting '/foo' should fail - inserting '/' should fail - inserting '/foo/bar2' should succeed - inserting '/foo/baz' should succeed - inserting '/fox/bar' should succeed - inserting '/xxx/yyy/foo/bar' should succeed Essentially, these are absolute and fully-resolved (no /../ or /./) unix path to some filesystem directories (they are relative in my precise use case but all will have the same origin so they can be safely compared with the same logic as one would use for absolute paths). My solution was to try an exclude constraint using the ^@ (aka starts_with) operator (with proper r-trimming and appending of a path delimiter to avoid false-positives, like /foo/bar and /foo/bar2. with

CREATE TABLE location (
    id UUID NOT NULL, 
    path VARCHAR NOT NULL,  
    EXCLUDE USING btree ((rtrim(path, '/') || '/') WITH ^@)
)

which gets me this error:

Only commutative operators can be used in exclusion constraints.

I know the operator is not commutative, but I (wrongly) expected postgres would see this and do the check both ways (thus excluding both ancestors and descendants). I've tried looking up alternatives but haven't found one. **How can I enforce this constraint at the db level?** I really don't want to do this at the application level, as this would force me to deal with race conditions for something that really should be dealt with on the db side (data integrity concerns). The only other solution I can see is using an insert trigger that select all the rows in the table and throws if any of them matches my condition. This seems inefficient, and a bit ugly (it would essentially be a PL/pgSQL version of that exclude constraint, thus foregoing any performance benefit of an index). Any ideas ? Thanks in advance :)

Suppose I have a system for reserving vacation properties. One of the tables is reservations. Each reservation has a property_id, a start_date, and an end_date. I want to prevent conflicting reservations. Eg, if a property is reserved from June 15-20, nobody can reserve it on June 17, or from June 19-22, or any other overlapping period. I can see two ways to guarantee this: **Option 1**: Lock the table for writes, run a SELECT and check the existing reservations in application code, and if it will not create a conflict, INSERT a reservation or UPDATE the dates of an existing one. **Option 2**: Use an EXCLUDE constraint. ALTER TABLE reservations ADD CONSTRAINT no_overlapping_rentals EXCLUDE USING gist (property_id WITH =, daterange("start_date", "end_date", '[]') WITH &&); I'm fairly sure that the second option is more performant overall, because PostgreSQL doesn't have to wait while the application code examines the existing reservations. But **at the database level, what's the performance difference between these two options**? And are there any other factors I should consider? --- There actually is a third way - use transactions with serializable isolation, and within the transactions, SELECT for conflicting reservations, and if you find none, INSERT your new one. But 1) that also requires the db waiting for the application code to check the results and 2) it can give false positives - see https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e for a simple demo (about uniqueness, not overlap, but the same concept applies) Oh, and another thing about serializable isolation - I think the fact that you have to retry on a false positive means that the time to insert becomes unbounded, as you may have to keep getting in the back of the retry line.

I am refactoring my database constraint for such a table:

CREATE TABLE products (
  name text NOT NULL,
  status text NOT NULL
);

A former engineer working on the project had such a constraint:

ALTER TABLE products
  ADD CONSTRAINT unique_active_name
  EXCLUDE (name WITH =)
  WHERE (status = 'active' AND name  '');

I am wondering if it is equivalent to such a partial unique index:

CREATE UNIQUE INDEX unique_active_name
  ON products(name)
  WHERE (status = 'active' AND name  '');

I did not quite get the documentation for EXCLUDE, and would like to improve things. Thanks in advance!

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate). How do I place a constraint on the table such that bookings are not allowed to overlap? I am trying to implement this in SQLFIDDLE postgreSQL9.3 For example: 101 2016-01-01 2016-01-05 101 2016-01-03 2016-01-06 [This row should not be possible to insert] startdate and enddate are data type date.

I'm following [this answer](https://dba.stackexchange.com/a/268128/112479) to add a NOT NULL constraint to an existing DB table with ~100M rows. However, when I try running the backend process takes out a ShareLock.

ALTER TABLE mytable VALIDATE CONSTRAINT myfield_not_null;

I confirmed this by checking pg_locks in another session, where I can see the following (5447 is the pid of the backend attempting the VALIDATE CONSTRAINT):

mydb=> select l.pid, l.mode, l.granted, l.waitstart, a.xact_start, a.query_start, a.state from pg_locks l join pg_stat_activity a on l.pid = a.pid;
  pid  |           mode           | granted |           waitstart           |          xact_start           |          query_start          | state  
-------+--------------------------+---------+-------------------------------+-------------------------------+-------------------------------+--------
  5447 | ShareLock                | t       |                               | 2023-10-06 14:58:23.133136+00 | 2023-10-06 14:58:23.355743+00 | active 
  5447 | ShareUpdateExclusiveLock | t       |                               | 2023-10-06 14:58:23.133136+00 | 2023-10-06 14:58:23.355743+00 | active

The docs explicitly say: > a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. The ShareLock is blocking some queries that need RowExclusiveLock (update/insert/delete). How can I validate the constraint without taking a ShareLock?

In multiple of my tables, I have a deleted column for tracking things which have been deleted, instead of directly deleting them from the database. As such, I would like to create a constraint for disallowing the *undeletion* of things, preferably all at once. Is that even possible? To exemplify things:

CREATE TABLE IF NOT EXISTS users (
    id UUID DEFAULT uuid_generate_v4 ()
        CONSTRAINT users_pk PRIMARY KEY,
    deleted BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS votes (
    id UUID DEFAULT uuid_generate_v4 ()
        CONSTRAINT votes_pk PRIMARY KEY,
    deleted BOOLEAN NOT NULL DEFAULT FALSE
);

In those tables, the constraint should disallow operations which turn deleted from TRUE to FALSE.

I have a table with user subscriptions in my Postgres 13 DB. I added a GiST index to disallow overlapping time ranges for the same user: CREATE INDEX user_sub_exclusion_index ON public.user_sub USING gist (user_id, tstzrange(sub_start, sub_end)) But today I found it did not work. This is the table DDL: ~~~pgsql -- DROP TABLE public.user_sub; CREATE TABLE public.user_sub ( id int8 NOT NULL GENERATED ALWAYS AS IDENTITY, app_id varchar NOT NULL, product_id int4 NOT NULL, iap_product_id int8 NOT NULL, created_time int8 NOT NULL, updated_time int8 NOT NULL, user_id int8 NOT NULL, sub_start_time int8 NOT NULL DEFAULT 0, sub_end_time int8 NOT NULL DEFAULT 0, enabled int2 NOT NULL DEFAULT 1, order_id varchar NOT NULL, sub_start timestamptz NOT NULL, sub_end timestamptz NOT NULL, CONSTRAINT user_sub_new_pk PRIMARY KEY (id), CONSTRAINT user_sub_new_un UNIQUE (order_id) ); CREATE INDEX user_sub_exclusion_index ON public.user_sub USING gist (user_id, tstzrange(sub_start, sub_end)); CREATE INDEX user_sub_tstzrange_user_id_product_id_excl ON public.user_sub USING gist (tstzrange(sub_start, sub_end, '[]'::text), user_id, product_id); ~~~ Here is a sample of the table data right now: it contains overlapping time ranges for the same user: ~~~pgsql INSERT INTO public.user_sub (app_id, product_id, iap_product_id, created_time, updated_time, user_id, sub_start_time, sub_end_time, enabled, order_id, sub_start, sub_end) VALUES ('vOghoo10L9', 9, 6, 1680251663942, 1680251663942, 77, 1680501039421, 1680587439421, 1, '627599858277646336', '2023-04-03 13:50:39.421', '2023-04-04 13:50:39.421') , ('vOghoo10L9', 9, 6, 1680263287925, 1680263287925, 77, 1680587439422, 1680673839422, 1, '627697298195189760', '2023-04-04 13:50:39.422', '2023-04-05 13:50:39.422') , ('vOghoo10L9', 9, 6, 1680263497256, 1680263497256, 77, 1680673839423, 1680760239423, 1, '627679779371601920', '2023-04-05 13:50:39.423', '2023-04-06 13:50:39.423') , ('vOghoo10L9', 9, 6, 1680263539890, 1680263539890, 77, 1680760239424, 1680846639424, 1, '627680143827259392', '2023-04-06 13:50:39.424', '2023-04-07 13:50:39.424') , ('vOghoo10L9', 9, 6, 1680273609032, 1680273609032, 77, 1680846639425, 1680933039425, 1, '627601223242579968', '2023-04-07 13:50:39.425', '2023-04-08 13:50:39.425') , ('vOghoo10L9', 9, 6, 1680275903068, 1680275903068, 77, 1680933039426, 1681019439426, 1, '627610724383956992', '2023-04-08 13:50:39.426', '2023-04-09 13:50:39.426') , ('vOghoo10L9', 9, 6, 1680276434561, 1680276434561, 77, 1681019439427, 1681105839427, 1, '627612898614681600', '2023-04-09 13:50:39.427', '2023-04-10 13:50:39.427') , ('vOghoo10L9', 9, 6, 1680277832740, 1680277832740, 77, 1681105839428, 1681192239428, 1, '627618880539664384', '2023-04-10 13:50:39.428', '2023-04-11 13:50:39.428') , ('vOghoo10L9', 9, 6, 1680282545888, 1680282545888, 77, 1681192239429, 1681278639429, 1, '627411682153152512', '2023-04-11 13:50:39.429', '2023-04-12 13:50:39.429') , ('vOghoo10L9', 9, 6, 1680327772145, 1680327772145, 77, 1681278639430, 1681365039430, 1, '627601513341616128', '2023-04-12 13:50:39.430', '2023-04-13 13:50:39.430') , ('vOghoo10L9', 9, 5, 1680761228691, 1680761228691, 79, 1680761228687, 1680847628687, 1, '629789245600776192', '2023-04-06 14:07:08.687', '2023-04-07 14:07:08.687') , ('vOghoo10L9', 9, 5, 1680763488691, 1680763488691, 77, 1680763488689, 1680849888689, 1, '629798689575354368', '2023-04-06 14:44:48.689', '2023-04-07 14:44:48.689') , ('vOghoo10L9', 9, 5, 1680763634694, 1680763634694, 77, 1680849888690, 1688712288690, 1, '629799331018653696', '2023-04-07 14:44:48.690', '2023-07-07 14:44:48.690') , ('vOghoo10L9', 9, 6, 1680850885924, 1680850885924, 80, 1680850881709, 1680937281709, 1, '630165239553671168', '2023-04-07 15:01:21.709', '2023-04-08 15:01:21.709') , ('vOghoo10L9', 9, 6, 1681461059268, 1681461059268, 81, 1681461059263, 1681547459263, 1, '632723434021126144', '2023-04-14 16:30:59.263', '2023-04-15 16:30:59.263') , ('vOghoo10L9', 9, 6, 1681483885008, 1681483885008, 82, 1681483885006, 1681570285006, 1, '632820205569245184', '2023-04-14 22:51:25.006', '2023-04-15 22:51:25.006') , ('iYDFo0PQQX', 11, 14, 1682762462986, 1682762462986, 86, 1682762462971, 1682848862971, 1, '638182956129267712', '2023-04-29 18:01:02.971', '2023-04-30 18:01:02.971') , ('iYDFo0PQQX', 11, 14, 1682764832099, 1682764832099, 86, 1682764832090, 1682851232090, 1, '638192942803423232', '2023-04-29 18:40:32.090', '2023-04-30 18:40:32.090') , ('iYDFo0PQQX', 11, 14, 1682765939081, 1682765939081, 86, 1682765939052, 1682852339052, 1, '638197106476421120', '2023-04-29 18:58:59.052', '2023-04-30 18:58:59.052') , ('iYDFo0PQQX', 11, 14, 1682766222551, 1682766222551, 86, 1682766193447, 1682852593447, 1, '638198676681232384', '2023-04-29 19:03:13.447', '2023-04-30 19:03:13.447') ; ~~~ Am I missing something? How to make this constraint work? That is, to prevent the same user from subscribing with overlapping time ranges.

Let the table to be considered is this: create table users( email varchar(50) not null, is_verified boolean default false, ); Now, how can I define the exclusion constraint here so that if the is_verified column is true once for an email, that email ***cannot*** be inserted anymore?

I'm designing a postgres table to record the intervals contained in a weekly schedule. It would hold schedules for multiple businesses, and a simple example dataset might look like: business_id interval ----------- ----------------------------------- 1 Sunday 10:00:00 – Sunday 14:00:00 1 Sunday 22:00:00 – Monday 02:00:00 1 Friday 11:00:00 – Friday 16:00:00 1 Saturday 15:00:00 – Sunday 01:00:00 Note that intervals can cross the boundaries between days. A business should not have overlapping intervals, and I'd like to design the table in a way that lets me enforce this. I was considering mapping these day-of-week + time-of-day values to the corresponding numbers of seconds since the beginning of the week, storing intervals as [int4range](https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN) and using an exclusion constraint to prohibit overlapping integer ranges, but that wouldn't properly address intervals that wrap around the end of the week. Is there a good way to model this kind of cyclical data and prohibit overlaps?

I have a table person which I am planing to normalize to the sixth normal form. The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the change history of these columns and adding additional metadata to them (e.g. the confidence level about the data). I am planning to create new tables person_username, person_email, person_phone and have the person view which brings them all together and has INSTEAD OF triggers so that users can insert and update the view and these changes get directed to the underlying tables. My question in, how to enforce UNIQUE constraints when using the sixth normal form. The structure of the person_email table would look something like this:

CREATE TABLE person_email (
    person_id integer FOREIGN KEY person(id),
    username text NOT NULL,
    valid_from datetime
);

The data in the table might look like this: | person_id | username | valid_from | | --------- | -------- | ---------- | | **1** | **foo** | **2022-01-01** | | 1 | bar | 2021-06-01 | | **2** | **foo** | **2020-08-01** | | 2 | abc | 2020-02-01 | The rows marked as bold hold the most recent (the current) information. Both users 1 and 2 have the current username set to foo which should not be allowed. However, I cannot make username UNIQUE, since the fact that someone had used that name in the distant past does not mean that someone else could not use it now. What I would need would be a UNIQUE (username) constraint which would only look at the most recent row for each user. How to achieve that? Is there a way to make use of the PostgreSQL EXCLUDE constraint?

I want to select all available and bought packs. An user can open two pages to buy one pack, this results in two rows in the payment table. One gets status 'Pending' and the one that is bought gets status 'Paid'. I created the code below to show all available and paid packs for 1 user.

select distinct p.id_pack, pa.status,
	CASE
		WHEN pa.status = 'paid' THEN 'Paid'
		WHEN pa.status = 'pending' THEN 'Available'
		WHEN pa.status ISNUll THEN 'Available'
		END Status2
from packs p
left join payment pa
on p.id_pack = pa.pack
left join users u
on u.id_user = pa.user
and u.cognito_id='x'
order by p.id_pack

**Current result** | id_pack | status2 | | -------- | -------------- | | 1| Paid | | 2| Paid | | 2| Available| | 3| Paid | The third row (id_pack 2, status2 'Available') should **not** be selected because that pack has been bought by the user (That row is created because two buy pages had been opened). **Expected result** | id_pack | status2 | | -------- | -------------- | | 1| Paid | | 2| Paid | | 3| Paid | Anyone knows how to do this? **UPDATE** ---------------------------------------------------------------------------- I ran the code from jjanes. The result is under the image.

`
select p.id_pack,  u.id_user,
    max(CASE
        WHEN pa.status = 'paid' THEN 'Paid'
        WHEN pa.status  'paid' THEN 'Available'
		WHEN pa.status isnull then 'Available'
        END) as Status2
from packs p
left join payment pa
on p.id_pack = pa.pack
left join users u
on u.id_user = pa.user
and u.cognito_id='x'
group by p.id_pack, u.id_user
order by p.id_pack

` Somehow it is creating two entries for each row and it transforms 'available' to 'paid'(?). I'm a bit clueless on why this is.

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); But I get this error: > ERROR: data type integer has no default operator class for access method "gist" > HINT: You must specify an operator class for the index or define > a default operator class for the data type. The PostgreSQL docs use this example which does not work for me: CREATE TABLE room_reservation ( room text, during tsrange, EXCLUDE USING gist (room WITH =, during WITH &&) ); Same error message. And this one , which does not work for me either: CREATE TABLE zoo ( cage INTEGER, animal TEXT, EXCLUDE USING gist (cage WITH =, animal WITH ) ); Same error message. I am able to create this without any problem: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (startend WITH &&) ); and this: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING btree (user_id WITH =) ); I've spent quite a bit of time searching for hints about figuring out how to make this work, or figuring out why it won't work. Any ideas?

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text not null, width double precision not null, height double precision not null ); create table board ( board_id bigserial primary key, width double precision not null, height double precision not null ); create table board_part ( board_id bigint not null references board, part_id bigint not null references part, position point not null ); ([SQL Fiddle](http://sqlfiddle.com/#!15/1cfb7/5) , [Visualization](https://i.sstatic.net/89w7d.jpg)) For b and b2 any board_parts, I want to enforce the following constraints: 1. b lies on the board: box(b.position, point(b.part.width,b.part.height)) <@ box(point(0,0), point(b.board.width,b.board.height)) 2. b and b2 do not overlap if they lie on the same board: b.board_id != b2.board_id or not (box(b.position, point(b.part.width,b.part.height)) && box(b2.position, point(b2.part.width,b2.part.height))) **How can I achieve this (without too much data duplication)?** Changing the schema is fine. Here is [my best attempt (SQL Fiddle)](http://sqlfiddle.com/#!15/5773c/2) , taking inspiration from Erwin's [answer to my previous question](https://dba.stackexchange.com/a/58972/34321) . It enforces the constraints I wanted, but has a lot of duplicate data in the board_part table. I imagine I could write a function to fill in the board_width, board_height, part_width, and part_height fields automatically, but it still feels wrong having so much duplicate data around. Also, keying to the width/height fields feels like a hack.

I have two tables. A client table and a vehicle table, the two are linked in a unit lookup table. My constraint is that a vehicle cannot belong to a client if it already is associated with another client. This is what I have so far:

ALTER TABLE unit 
   ADD CONSTRAINT unit_vehicle_across_client_constraint 
   EXCLUDE USING GIST (client WITH =, vehicle WITH ) DEFERRABLE;

But it doesn't seem to work. I guess I just don't really understand how the exclusion parameters really work. Any suggestions?

I am modelling an application where events for factories can be registered. There are usually a small amount of factories (around 500), but each factory can register a large set of events (millions). It's impossible that two events overlap for the same factory. My idea was to introduce an exclusion constraint based on a GIST index:

CREATE TABLE event
(
   identifier SERIAL NOT NULL PRIMARY KEY
   factory_identifier INTEGER NOT NULL REFERENCES factory,
   period TSTZRANGE NOT NULL,

   EXCLUDE USING gist
   (
      factory_identifier WITH =,
      period             WITH &&
   )
);

In the documentation I read: > A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns. I am not sure how to interpret this. Given that there are only a small amount of factories but a large number of events, should I define the exclusions constraint as following instead?

EXCLUDE USING gist
(
   period             WITH &&
   factory_identifier WITH =,
)

Now, the period is the first column, for which there are a lot of distinct values (almost all values are unique, actually), in contrast to factory_identifier, for which there only a few distinct values. Is this better now? In short, I have difficulties to grasp the quoted documentation above. ---- additional ---- In the slides on a presentation of exclusion constraints on page 16 and a blog post , both coming from the original author of exclusion constraints, I found the following example:

EXCLUDE USING GIST(room WITH =, during WITH &&)

Given the (reasonable) assumption that there much more distinct reservation periods than distinct rooms, I wonder if this should have been (during WITH &&, room WITH =) instead, given the quote regarding GIST column ordering above. This makes me believe that I am not really understanding the quoted documented above.

Here is the table

CREATE TABLE date_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER,
  start_at DATE,
  end_at DATE,
  is_exception BOOLEAN
);

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH &&)
WHERE (NOT is_exception);

I have a table with working ranges and here is a flag is_exception which indicates that this range is an exception from the regular range and I want to make a constraint that all regular ranges do not overlap each other and exception ranges belong to the regular range. I tried to use <@ operator but have found that it is not commutative.

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH <@)
WHERE (is_exception);

Is there any option to do this? Shall I create custom operator for this case? **UPDATE** Test cases:

user_id		start_at		end_at		is_exception	is_day_off
1			2019-03-01		2019-03-05	FALSE			FALSE		// is fine
1			2019-03-01		2019-03-05	FALSE			FALSE		// FAIL. Range intersects with range above
1			2019-03-02		2019-03-03	TRUE			FALSE		// is fine since exception is inside "regular range above"
1			2019-03-04		2019-03-04	TRUE			FALSE		// is fine since exception is inside "regular range above" & does not intersect another exception
1			2019-03-03		2019-03-03	FALSE			TRUE		// is fine since day off is inside "regular range above"
1			2019-03-05		2019-03-05	FALSE			TRUE		// is fine since day off is inside "regular range above" & does not intersect another day off

1			2019-03-02		2019-03-03	TRUE			FALSE		// FAIL. Exception should NOT intersect with another one
1			2019-03-03		2019-03-03	FALSE			TRUE		// FAIL. Day off should NOT intersect with another one

1			2019-03-15		2019-03-15	TRUE			FALSE		// FAIL. Exception is NOT inside "regular range above"
1			2019-03-10		2019-03-10	FALSE			TRUE		// FAIL. Day off is NOT inside "regular range above"

If I create the following table.. CREATE TABLE foo ( x int ); I can EXCLUDE the following, WHERE x = (generic unique constraint) WHERE x = (type of unique constraint reduce values that aren't specific to null) WHERE x ANY OTHER value (EXCLUDE WITH ) But, is there a clever way to permit INSERT INTO foo (x) VALUES (0); INSERT INTO foo (x) VALUES (1); but, not INSERT INTO foo (x) VALUES (1); INSERT INTO foo (x) VALUES (0); Or is there a module would otherwise let me do it as a function of the index and not with a trigger. In summary, you can insert multiple values of 1 (or 0), **but** upon the first insertion of 1 the file is closed and no further insertion of 0 can be made, but 1 can continue to be inserted.

How come when I try to create an [exclusion constraint with GIST](https://dba.stackexchange.com/q/185174/2639) , CREATE TABLE foo ( a tsrange, EXCLUDE (a WITH &&) ); ERROR: operator &&(anyrange,anyrange) is not a member of operator family "range_ops" DETAIL: The exclusion operator must be related to the index operator class for the constraint.

Original goal: create a constraint to ensure that only non-overlapping subnets exist in a particular Postgres table. From reading the docs carefully, I could get as far as this: create table subnets ( subnet cidr, exclude (subnet with &&) ); But this doesn't work. It produces the inscrutable message: ERROR: operator &&(inet,inet) is not a member of operator family "network_ops" DETAIL: The exclusion operator must be related to the index operator class for the constraint. Despite reading up on the doc sections about operator classes and indexes and index types, I retained the feeling that I was missing an entire explanatory section that is taken for granted. I still don't know what IS an operator class, really, nor an operator family. I did find [posted on a mailing list](https://groups.google.com/forum/#!topic/mailing.database.pgsql-admin/9ldVTdZGrp8) a code snippet that led to the following working code: create table subnets ( subnet cidr, exclude using gist (subnet inet_ops with &&) ); But I can't truly understand what the "using gist" is for, nor the "inet_ops." I know that "using gist" relates to types of indexes. I know an index is automatically created for a "unique" constraint, and I *guess* that an index may also be automatically created for an "exclusion" constraint. [The only documentation about "operater classes"](https://www.postgresql.org/docs/9.6/static/indexes-opclass.html) all relates to *indexes,* not constraints. **For this or future queries where I want an exclusion constraint, how can I determine what operator class and access method should be specified for the constraint to work?** Note that even with the working code in hand I'm unable to find *why* it's "gist" and not something else, and *why* it's "inet_ops" and not "network_ops" or nothing. \doS and the queries listed in the operator class documentation were unenlightening. Another error I produced was also unenlightening: vagrant=# create table subnets ( subnet cidr, exclude using gist (subnet with &&) ); ERROR: data type cidr has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. (This question is based on the premise that I should not *need* to resort to copying and pasting incantations; a thorough review of the documentation plus carefully reading error messages *should* let me resolve problems. With Postgres, this has always been true before now.)

I have a current constraint like this: ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((::text) WITH =, numrange(, ) WITH &&); This works fine, but I need to add one more thing to the constraint, it must only be enforced for rows where a boolean column is true (let's call it active). I tried this: ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((::text) WITH =, numrange(, ) WITH && active = TRUE); This throws syntax error: > ERROR: syntax error at or near "active" > SQL state: 42601 Is it possible to have the constraint include the active column as well?