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?

So you want to prevent rows with the same from and to values? — user330315
– user330315, Commented Apr 1, 2021 at 9:22

user330315 · Accepted Answer · 2021-04-01 09:27:37Z

3

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

answered Apr 1, 2021 at 9:27

user330315

Sign up to request clarification or add additional context in comments.

Comments

Jan Z · Accepted Answer · 2021-05-05 13:52:42Z

0

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.)

edited May 5, 2021 at 13:52

answered May 5, 2021 at 13:44

Jan Z

592 bronze badges

Collectives™ on Stack Overflow

PostgreSQL unique index using gist

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related