Search Postgresql Archives

Re: constraint -- one or the other column not null

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 6 Sep 2006 09:29:23 +0200
"Dawid Kuroczko" <qnex42@xxxxxxxxx> wrote:

> On 9/6/06, George Pavlov <gpavlov@xxxxxxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux