From: | Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | Chip Nowacek <chip(at)twostewards(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: two-column primary key (not the typical question) |
Date: | 2012-07-07 02:17:34 |
Message-ID: | CABwTF4X7u=v8wxfBHv_MLTn2Rj-b5yPQbJnEvsUwoudPQKThXw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Jul 6, 2012 at 10:00 PM, Chip Nowacek <chip(at)twostewards(dot)com> wrote:
> I need a suggestion. I need a two-column primary key that does not depend
> on the order of the entries. That is, for the purposes of the key:
>
> PKColA PKColB
> foo bar
> bar foo
>
> is not valid.
>
I don't think it's possible using PKeys. It can be done with unique
expression index combined with NOT NULL constraints.
Here's a working example:
postgres=# create table test3( a varchar, b varchar);
CREATE TABLE
postgres=# create unique index on test3 ((case when a < b then a || b else
b || a end));
CREATE INDEX
postgres=# alter table test3 alter a set not null, alter b set not null;
ALTER TABLE
postgres=# insert into test3 values('foo', 'bar');
INSERT 0 1
postgres=# insert into test3 values('foo', 'bar');
ERROR: duplicate key value violates unique constraint "test3_case_idx"
DETAIL: Key ((
CASE
WHEN a::text < b::text THEN a::text || b::text
ELSE b::text || a::text
END))=(barfoo) already exists.
postgres=# insert into test3 values('bar', 'foo');
ERROR: duplicate key value violates unique constraint "test3_case_idx"
DETAIL: Key ((
CASE
WHEN a::text < b::text THEN a::text || b::text
ELSE b::text || a::text
END))=(barfoo) already exists.
postgres=#
Best regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Gurjeet Singh | 2012-07-07 04:24:39 | Re: Suboptimal query plan fixed by replacing OR with UNION |
Previous Message | Chris Travers | 2012-07-07 02:09:13 | Re: two-column primary key (not the typical question) |