From: PFC <lists@boutiquenumerique.com>
To: j.random.programmer <javadesigner@yahoo.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL design question: null vs. boolean values
Date: Sun, 16 Jan 2005 19:47:33 +0100
Message-ID: <opskpk5jr6th1vuj@musicbox> (raw)
In-Reply-To: <20050116135936.28081.qmail@web14206.mail.yahoo.com>
References: <20050116135936.28081.qmail@web14206.mail.yahoo.com>
> create table xyz
> (
> field_foo char(1) check (field_foo in 'y', 'n'),
> foo_detail varchar(255),
> check (
> case
> when field_foo='y' and foo_detail is null
> then false
> else true
> end
> )
> );
A simpler check would be :
CHECK(
(field_foo = 'y' AND foo_detail IS NOT NULL)
OR ( (field_foo = 'n' OR field_foo IS NULL) AND foo_detail IS NULL)
)
Which means " field_foo can be y, n, or NULL, and foo_detail should be
null except if field_foo is 'y' "
Also, IMHO, the Y/N/unknown case should have three values, NULL meaning
'the user has not answered this question'. Because if you insert a blank
row in the table and fill it afterwards, you'll know if it was 'answered
unknown' or 'unanswered'.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@boutiquenumerique.com, javadesigner@yahoo.com, dev@archonet.com
Subject: Re: SQL design question: null vs. boolean values
In-Reply-To: <opskpk5jr6th1vuj@musicbox>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox