From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: constraint -- one or the other column not null |
Date: | 2006-09-06 07:59:03 |
Message-ID: | 20060906095903.1faa8370@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 6 Sep 2006 09:29:23 +0200
"Dawid Kuroczko" <qnex42(at)gmail(dot)com> wrote:
> On 9/6/06, George Pavlov <gpavlov(at)mynewplace(dot)com> wrote:
> > I have two columns, both individually nullable, but a row needs
> > to have a value in one or the other. What is the best way to
> > implement the constraints? I currently have:
> >
> > create table f (
> > a int,
> > b int,
> > check (a + b is null),
> > check (coalesce(a,b) is not null)
> > );
> >
> > Is there a better way to do it?
>
> Personally I woud simply put there
> CHECK(a IS NOT NULL OR b IS NOT NULL)
> which is probably the simplest form of your constraint. :)
in one or the other should stand for xor not xor.
And yeah George's solution seems the most concise for one shot. I don't know if it is faster than:
check ((a is null and b is not null) or ( a is not null and b is null))
You can write a xor function. At least in 7.4, that I'm currently using, there is no xor operator.
create function xor(boolean,boolean)
returns boolean as '
begin
select ($1 and not $2) or (not $1 and $2);
end;
' language 'sql';
check (xor(is null a, is null b))
sort of
Anyway if one of the 2 has to be null, why don't you use a boolean column + an int?
create table f (
ab int,
aorb boolean,
);
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
From | Date | Subject | |
---|---|---|---|
Next Message | Charlie Savage | 2006-09-06 07:59:42 | Re: ERROR: Gin doesn't support full scan due to it's awful |
Previous Message | zeljko | 2006-09-06 07:58:11 | Re: compressed protocol |