Re: [GENERAL] How to create unique index on multiple columns wherethe combination doesn't matter? - Mailing list pgsql-general

From Andreas Kretschmer
Subject Re: [GENERAL] How to create unique index on multiple columns wherethe combination doesn't matter?
Date
Msg-id 20170323060144.GA5930@tux
Whole thread Raw
In response to [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?  (Glen Huang <hey.hgl@gmail.com>)
List pgsql-general
Glen Huang <hey.hgl@gmail.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?

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


pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: [GENERAL] How to create unique index on multiple columns wherethe combination doesn't matter?
Next
From: Charles Clavadetscher
Date:
Subject: [GENERAL] Wiki editor privilege