PostgreSQL unique index using gist

Question

I have a table "locations" with "from" and "to" columns with "point" type. I'm only able to use "gist" indexes on those columns as the b-tree is not available for "point" type.

I would like to have a unique index on both of the columns (to ensure there is no same location stored).

This is not possible due to error "access method "gist" does not support unique indexes".

Is it somehow possible to achieve this? I could workaround it by creating regular text column storing "from_lat,from_lng:to_lat,to_lng" and add unique index on it, but is there a better way?

Answer 1

You can use an exclusion constraint. A unique constraint (or index) is essentially just a special case of an exclusion constraint.

An exclusion constraints an be defined using GIST:

alter table locations
  add constraint unique_points
  exclude using gist ("from" with ~=, "to" with ~=);

The operator ~= checks for the equality of two points