From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to create unique index on multiple columns where the combination doesn't matter? |
Date: | 2017-03-23 06:01:44 |
Message-ID: | 20170323060144.GA5930@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Glen Huang <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?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Charles Clavadetscher | 2017-03-23 06:57:28 | Wiki editor privilege |
Previous Message | David G. Johnston | 2017-03-23 04:09:59 | Re: How to create unique index on multiple columns where the combination doesn't matter? |