CODEWITHSUNDEEP
postgresqlindexing
morgoth
morgoth

Reputation: 1451

PostgreSQL unique index using gist

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?

Upvotes: 3

Views: 1615

Answers (2)

Jan Z
Jan Z

Reputation: 59

You may use the "exclude" constraint, but this is not a unique index nor a primary key at all. Furthermore an upsert is impossible, because to resolve "on conflict (...) do update" a primary or unique key is required.

Referring https://www.postgresql.org/docs/13/indexes-unique.html it is impossible to use any range type as unique key (or pkey, including violations by overlapping ranges etc.)

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 3

Related Questions