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=lzAM4wJBCYIAED9gGgK89Soq31WTCzuXl16VhIc35LllqBHKr/XFjZMtBs6OZc7i99
	CuLIC8JNF4yx6GIGYQFD0wrBfqy8PDg2WMgbkQ+bW4254dJ77KzXaWlqHus503ux+N0I
	6Sk+KNlGRdVj7NGNXCfY8pfqtB17hnx7ABBaQ=
MIME-Version: 1.0
In-Reply-To: <71F491F5DA99604A80DE49424BF3D02B0C089039@exchange8.actuate.com>
References: <71F491F5DA99604A80DE49424BF3D02B0C089039@exchange8.actuate.com>
Date: Tue, 7 Jul 2009 00:13:46 -0600
Message-ID: <dcc563d10907062313j1f0eb27awc2bea6b3f810dc41@mail.gmail.com>
Subject: Re: Best way to simulate Booleans
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Peter Headland <pheadland@actuate.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Mon, Jul 6, 2009 at 7:22 PM, Peter Headland<pheadland@actuate.com> wrote:
> I know, I know, PostgreSQL has Booleans that work very nicely.
> Unfortunately, I have to create a schema that will work on Oracle as well as
> PostgreSQL, by which I mean that a single set of Java/JDBC code has to work
> with both databases. I have an XML meta-schema that enables me to generate
> appropriate DDL; that handles all the INTEGER vs. NUMBER(m,n) stuff. But
> Oracle simply has no Booleans, so I will have to resort to some more or less
> ugly alternative. I am hoping that others here have had to deal with this
> and can suggest an approach that will be minimally loathsome.

The most transportable method would be to use either a char(1) or an
int with a check constraint.

mybool char(1) check (mybool in ('t','f'))
mybool int check (mybool >=0 and <=1)

Or something like that.