Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-24 Thread Glen Huang
Yes, the order doesn't matter, and this approach sounds like a good idea. I'll try it out, thanks. > On 23 Mar 2017, at 3:56 PM, Alban Hertroys wrote: > >> >> On 22 Mar 2017, at 17:54, Glen Huang wrote: >> >> Hello, >> >> If I have a table like >> >>

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-23 Thread Alban Hertroys
> On 22 Mar 2017, at 17:54, Glen Huang wrote: > > Hello, > > If I have a table like > > CREATE TABLE relationship ( > obj1 INTEGER NOT NULL REFERENCES object, > obj2 INTEGER NOT NULL REFERENCES object, > obj3 INTEGER NOT NULL REFERENCES object, > ... > ) > > And I

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-23 Thread Andreas Kretschmer
Glen Huang wrote: > Hello, > > If I have a table like > > CREATE TABLE relationship ( > obj1 INTEGER NOT NULL REFERENCES object, > obj2 INTEGER NOT NULL REFERENCES object, > obj3 INTEGER NOT NULL REFERENCES object, > ... > ) > > And I want to constrain that if

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread David G. Johnston
Maybe try combining them into a single array then performing array comparisons... On Wednesday, March 22, 2017, Glen Huang wrote: > Thanks. > > Didn't realize it could be implemented with a exclusion constraint. The > comparing between any two row definitely sounds like the

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread Glen Huang
Thanks. Didn't realize it could be implemented with a exclusion constraint. The comparing between any two row definitely sounds like the right direction. But I'm still having a hard time figuring out how i should write the `exclude_element WITH operator` part, which I think, should detect if

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread David G. Johnston
On Wed, Mar 22, 2017 at 9:54 AM, Glen Huang wrote: > Hello, > > If I have a table like > > CREATE TABLE relationship ( > obj1 INTEGER NOT NULL REFERENCES object, > obj2 INTEGER NOT NULL REFERENCES object, > obj3 INTEGER NOT NULL REFERENCES object, > ... > ) > > And I

[GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread Glen Huang
Hello, If I have a table like CREATE TABLE relationship ( obj1 INTEGER NOT NULL REFERENCES object, obj2 INTEGER NOT NULL REFERENCES object, obj3 INTEGER NOT NULL REFERENCES object, ... ) And I want to constrain that if 1,2,3 is already in the table, rows like 1,3,2 or 2,1,3 shouldn't