DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=mG3W9yJtdUGTqIhqvM7A2osojJKdnbuseW5tgEB6DA3PwS29Wj5/IoUB8Sl5Gywotj
	z44CdaRMdVbilcGL2yLynsf+m0TK+D6ekijAmZ/0Z8kOkwZxDsphHmR6TyY0NgBVVfWs
	YpO6tTDt4xL/kSFMUOW3bUrhRQyKDeUPemCwA=
MIME-Version: 1.0
In-Reply-To: <4B3A9448.20308@obviously.com>
References: <4B31D619.1090706@obviously.com>
	<4B31D86D.9030000@postnewspapers.com.au>
	<37d451f70912231304i3515e575x69b31638962739a6@mail.gmail.com>
	<4B32DB14.6080308@postnewspapers.com.au>
	<4B3A9448.20308@obviously.com>
Date: Thu, 31 Dec 2009 09:23:49 -0700
Message-ID: <37d451f70912310823y114ff7a7xd511ae4f5d369e2d@mail.gmail.com>
Subject: Re: Using || operator to fold multiple columns into one
From: Rosser Schwarz <rosser.schwarz@gmail.com>
To: Bryce Nesbitt <bryce2@obviously.com>
Cc: sql pgsql <pgsql-sql@postgresql.org>,
	Craig Ringer <craig@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Tue, Dec 29, 2009 at 4:44 PM, Bryce Nesbitt <bryce2@obviously.com> wrote=
:

> Craig is correct in the OP attempt. =A0 All but one field is intended to =
be null

Ah; my misunderstanding, then.  Sorry for the noise.

> Duplicate rows, or data in multiple columns, would wreck havoc on the
> scheme. =A0If there is a better way, I am all eyes.

One thought would be to add a couple of constraints: a CHECK
constraint to ensure that one and only one of the columns is populated
(think "t_number IS NOT NULL AND t_string IS NULL AND t_boolean IS
NULL AND t_date IS NULL OR..."), and a UNIQUE constraint against
(context_key, t_number, t_string, t_boolean, t_date).  That's not
really a "better" way, but it should prevent both duplicate and
"compound" data.

rls

--=20
:wq