postgresql how to create a exclude constraint on integer array to prevent array value overlap?

Question

When I add an exclude constraint on a table to prevent two rows with same value in int[], I got this error message:

data type integer[] has no default operator class for access method "gist"

I have a table like this:

CREATE TABLE x (
  id SERIAL PRIMARY KEY,
  ref_id INT REFERENCES x,
  purchase_ids INT[],
  EXCLUDE USING GIST(purchase_ids WITH &&) WHERE(ref_id IS NULL)
);

I looked everywhere and it seems like not many people have attempted this before. PostgreSQL's manual page for CREATE OPERATOR CLASS has, at the end, an example for what it seems is exactly what you need (a GIST operator class for int[]), however it fails on my server. Perhaps you'll have better luck! — Ezequiel Tolnay
– Ezequiel Tolnay, Commented May 20, 2016 at 8:08
You can of course very simply solve this by using a table with a PRIMARY KEY for the purchase_ids instead of an int[]. Do you really need to store the data in an array? — Patrick
– Patrick, Commented May 26, 2016 at 4:48
@Patrick It's great if store data in an array. My colleague had figure it out by adding a intarray extension which supports overlap operation in exclude constraint. — dawncold
– dawncold, Commented May 26, 2016 at 6:35

dawncold · Accepted Answer · 2016-05-26 06:44:39Z

4

My colleague figured it out by create an extension:

CREATE EXTENSION IF NOT EXISTS intarray;

After created this extension, I can add exclude constraint for preventing two rows with same element in array.

answered May 26, 2016 at 6:44

dawncold

1831 gold badge1 silver badge13 bronze badges

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

Collectives™ on Stack Overflow

postgresql how to create a exclude constraint on integer array to prevent array value overlap?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related