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

[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
>> 
>> 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?
> 
> Does the order of the values of (obj1, obj2, obj3) in relationship matter? If 
> not, you could swap them around on INSERT/UPDATE to be in sorted order. I'd 
> probably go with a BEFORE INSERT OR UPDATE trigger.
> 
> In addition, to prevent unsorted entry, on obj2 add CHECK (obj2 > obj1) and 
> on obj3 add CHECK (obj3 > obj2).
> 
> Now you can create a normal PK or unique key on (obj1, obj2, obj3) as the 
> order of their values is not variable anymore.
> 
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.

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

[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 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?

Does the order of the values of (obj1, obj2, obj3) in relationship matter? If 
not, you could swap them around on INSERT/UPDATE to be in sorted order. I'd 
probably go with a BEFORE INSERT OR UPDATE trigger.

In addition, to prevent unsorted entry, on obj2 add CHECK (obj2 > obj1) and on 
obj3 add CHECK (obj3 > obj2).

Now you can create a normal PK or unique key on (obj1, obj2, obj3) as the order 
of their values is not variable anymore.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[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 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?

Sure.

test=*# create extension intarray;
CREATE EXTENSION
test=*# create table foo(c1 int, c2 int, c3 int);
CREATE TABLE
test=*# create unique index index_unique_foo on
foo(sort(array[c1,c2,c3],'asc'));
CREATE INDEX
test=*# insert into foo values (1,2,3);
INSERT 0 1
test=*# insert into foo values (3,2,1);
FEHLER:  doppelter Schlüsselwert verletzt Unique-Constraint
»index_unique_foo«
DETAIL:  Schlüssel »(sort(ARRAY[c1, c2, c3], 'asc'::text))=({1,2,3})«
existiert bereits.
test=*# 

(sorry for german messages, it means error, dublicate entry ...)


Regards, Andreas Kretschmer
-- 
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[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 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  > wrote:
>
> 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 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.
>
>
>

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

[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 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  
> wrote:
> 
> 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 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.

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

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

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

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general