Is it possible to have an exclusion constraint using `st_intersects` in PostGIS?

I'm using PostGIS to store geographical polygons:

create table Polygons(id primary key, position geography);

I would like to have the DBMS throw an error if someone attempts to create a situation where two polygons overlap each other.

So far, I have attempted to do this with an exclusion constraint:

alter table polygons
add constraint polygons_overlapping
exclude using gist (
    position with &&
) where (...);

However, && only checks if the axis-aligned bounding boxes intersect. I would like to have the same exclusion constraint but with using st_intersects, as it does the intersection check using the actual geometries.

So, something like:

alter table polygons
add constraint polygons_overlapping
exclude using gist (
    position with st_intersects
) where (...);

In some cases it is possible to do this kind of stuff by promoting the field to some other type that has an appropriate operator. However, as far as I know, no such type exists for this case.

And to be clear, it doesn't have to be an exclusion constraint in the end – if you have something else in mind that would accomplish my need, I'm happy to hear about it!

Solution

Use a constraint trigger where you can do pretty arbitrary checks on your values - in your case, reject them if they st_intersect() anything in the table. Make sure to set up a GiST index on the column.

Unfortunately the exclusion constraint idea, while intuitively simple, isn't something you'll be able to configure or add easily. All PostGIS boolean operators use bounding boxes, and there's no operator for a full ST_Intersects(). Each of those operators has a corresponding function, but not all functions have an equivalent operator defined. All functions that can speed up thanks to R-Tree-over-GiST indexes actually just add a bbox operator to their actual call that's supported by the index and use it to pre-filter rows, then do their main operation on what remains.

The comparison you need to specify in the exclusion constraint definition has to use an operator, not a function directly. Also, that operator has to belong to an operator class supported by the index, which in turn has to be supported by exclusion constraints. You'd basically have to wrap st_intersects() in an operator and add that to an opclass supported by GiST, which isn't really straightforward.
38.16. Interfacing Extensions to Indexes can give you an idea how much stuff you need to cover just to persuade exclusion constraints to do the job you can set up in a minute using the constraint trigger.