Using EXCLUDE in 9.0 with <> operator ...

From: Derek Harland <derek(at)chocolate-fish(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using EXCLUDE in 9.0 with <> operator ...
Date: 2010-11-10 00:45:53
Message-ID: 16E1A4F3-6335-4E59-BC88-DA101D528E5C@chocolate-fish.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hallo all,

I have a question about Exclusion constraints in 9.0:
* Lets say I have a table with two columns X and Y (of type varchar or int)
* Is there any way I can add add an EXCLUDE constraint that says, for each value of X the values of Y must be the same.
* So the following rows are ok:

X | Y
----
A , 1
A , 1
B , 1
B , 1
C , 2
C , 2

But adding

A , 3

should fail, as everything with value X = "A" should have the same value as existing data (where Y = 1)

Theoretically the following would be nice if it worked

EXCLUDE (X WITH =, Y WITH <>)

but it complains that

ERROR: operator <>(text,text) is not a member of operator family "text_ops"

because the Btree index method only allows = in an exclude constraint. Or am I missing a simpler way of doing this? (without having to create and then foreign key to another table, which is obviously a possibility)

des.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2010-11-10 01:21:22 Re: Using EXCLUDE in 9.0 with <> operator ...
Previous Message David Boreham 2010-11-10 00:21:26 Re: Why facebook used mysql ?