Thread: not null across multiple columns

here is an interesting question (to me)

suppose you wanted to be certain that either one of two or more columns
were present

Like in a user table, either a username or an email need to be present
to create a row.

You  can't use not null because it's an either or situation.

what's the best method to accomplish this sort of constraint?

several ways to do it, here's one:

  check (coalesce(a,b,c) not null)

if you want one or the other to be present, but not both see this
thread:

http://archives.postgresql.org/pgsql-general/2006-09/msg00207.php


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Matthew Terenzio
> Sent: Thursday, November 02, 2006 4:11 PM
> To: PostgreSQL general
> Subject: [GENERAL] not null across multiple columns
>
>
> here is an interesting question (to me)
>
> suppose you wanted to be certain that either one of two or
> more columns were present
>
> Like in a user table, either a username or an email need to
> be present to create a row.
>
> You  can't use not null because it's an either or situation.
>
> what's the best method to accomplish this sort of constraint?

On Thu, 2006-11-02 at 19:10 -0500, Matthew Terenzio wrote:
> suppose you wanted to be certain that either one of two or more
> columns were present
>
> Like in a user table, either a username or an email need to be present
> to create a row.
>
> You  can't use not null because it's an either or situation.
>
> what's the best method to accomplish this sort of constraint?

See check constraints:
http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN1954

I suppose you seek something like this:
        create table people (
            name text,
            email text,
            constraint valid_name_or_email
            check (name is not null or email is not null)
        );

Cheers,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0