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
Show quoted text
-----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 columnshere is an interesting question (to me)
suppose you wanted to be certain that either one of two or
more columns were presentLike 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 presentLike 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