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

Maybe try combining them into a single array then performing array
comparisons...

On Wednesday, March 22, 2017, Glen Huang <hey(dot)hgl(at)gmail(dot)com> wrote:

> 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
> specified columns consist of the same items, regardless the order? could
> `exclude_element` contains multiple columns? (from the syntax it looks like
> it's impossible) And is there such an operator to compare multiple columns?
>
> On 23 Mar 2017, at 1:04 AM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com
> <javascript:_e(%7B%7D,'cvml','david(dot)g(dot)johnston(at)gmail(dot)com');>> wrote:
>
> On Wed, Mar 22, 2017 at 9:54 AM, Glen Huang <hey(dot)hgl(at)gmail(dot)com
> <javascript:_e(%7B%7D,'cvml','hey(dot)hgl(at)gmail(dot)com');>> 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 1,2,3 is already in the table, rows like
>> 1,3,2 or 2,1,3 shouldn't be allowed.
>>
>> Is there a general solution to this problem?
>>
>> Sorry if the question is too basic, but I couldn't find the answer in the
>> doc, at least not in the chapter on unique index.
>>
>
> The most direct option to consider is a exclusion constraint.
>
> https://www.postgresql.org/docs/current/static/ddl-constraints.html
> (bottom of page)
>
> David J.
>
>
>