From pheadland@actuate.com Tue Jul  7 01:23:02 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 53B32633BCB
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  6 Jul 2009 22:23:02 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 68163-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  6 Jul 2009 22:22:59 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from wyattearp.actuate.com (mx1.actuate.com [69.1.81.237])
	by mail.postgresql.org (Postfix) with SMTP id 27865633BF6
	for <pgsql-sql@postgresql.org>; Mon,  6 Jul 2009 22:22:58 -0300 (ADT)
Received: from exchange8.actuate.com (Not Verified[192.168.11.22]) by
	wyattearp.actuate.com with MailMarshal (v6, 1, 4, 441)
	id <B4a52a3600000>; Mon, 06 Jul 2009 18:22:40 -0700
X-MimeOLE: Produced By Microsoft Exchange V6.5
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9FEA1.6B81DAD1"
x-cr-puzzleid: {A3299776-3914-40AF-BBD6-DCDF89236C95}
x-cr-hashedpuzzle: AHJC BCsO Bj1r CTZF CTpW DFn0 ECit E2Br E3n4 GXIv Gv5v HAUf
	HEbi HQEi IECv I0/S; 1;
	cABnAHMAcQBsAC0AcwBxAGwAQABwAG8AcwB0AGcAcgBlAHMAcQBsAC4AbwByAGcA;
	Sosha1_v1; 7; {A3299776-3914-40AF-BBD6-DCDF89236C95};
	cABoAGUAYQBkAGwAYQBuAGQAQABhAGMAdAB1AGEAdABlAC4AYwBvAG0A;
	Tue, 07 Jul 2009 01:22:50 GMT;
	QgBlAHMAdAAgAHcAYQB5ACAAdABvACAAcwBpAG0AdQBsAGEAdABlACAAQgBvAG8AbABlAGEAbgBzAA==
Content-class: urn:content-classes:message
Subject: Best way to simulate Booleans
Date: Mon, 6 Jul 2009 18:22:50 -0700
Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C089039@exchange8.actuate.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Best way to simulate Booleans
Thread-Index: Acn+oXFCg9tCM02zR820118jD/CMMw==
From: "Peter Headland" <pheadland@actuate.com>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200907/24
X-Sequence-Number: 32765

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9FEA1.6B81DAD1
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.

<VENT>God I hate Oracle...</VENT>

--=20
Peter Headland
Architect - e.Reports
Actuate Corporation



------_=_NextPart_001_01C9FEA1.6B81DAD1
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7651.59">
<TITLE>Best way to simulate Booleans</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->

<P DIR=3DLTR><SPAN LANG=3D"en-us"><FONT FACE=3D"Calibri">I know, I know, =
PostgreSQL has Booleans that work very nicely. Unfortunately, I have to =
create a schema that will work on Oracle</FONT> <FONT =
FACE=3D"Calibri">as well as PostgreSQL</FONT></SPAN><SPAN =
LANG=3D"en-us"><FONT FACE=3D"Calibri">, by which I mean that a single =
set of Java/JDB</FONT><FONT FACE=3D"Calibri">C code has to work with =
both databases.</FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">I have a</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">n</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri"></FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">XML</FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">meta-schema that enables me to generate appropriate =
DDL</FONT><FONT FACE=3D"Calibri">; that</FONT></SPAN><SPAN =
LANG=3D"en-us"> <FONT FACE=3D"Calibri">handles</FONT> <FONT =
FACE=3D"Calibri">all</FONT> <FONT FACE=3D"Calibri">the INTEGER vs. =
NUMBER(m,n) stuff</FONT></SPAN><SPAN LANG=3D"en-us">.<FONT =
FACE=3D"Calibri"></FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">But</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri"></FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">Oracle</FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">simply</FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">has no</FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">Boolean</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">s</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">, so I will have to resort to</FONT> <FONT =
FACE=3D"Calibri">some more</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri"></FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">or</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri"></FONT></SPAN><SPAN LANG=3D"en-us"> <FONT =
FACE=3D"Calibri">less ugly alternative</FONT></SPAN><SPAN =
LANG=3D"en-us">.<FONT FACE=3D"Calibri"> I am ho</FONT><FONT =
FACE=3D"Calibri">ping that others here have had to deal with this and =
can suggest an approach that will be minimally =
loathsome.</FONT></SPAN></P>

<P DIR=3DLTR><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">&lt;VENT&gt;</FONT><FONT FACE=3D"Calibri">God I hate =
Oracle</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">&#8230;</FONT></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">&lt;/VENT&gt;</FONT></SPAN><SPAN =
LANG=3D"en-us"></SPAN></P>

<P DIR=3DLTR><SPAN LANG=3D"en-us"></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">-- </FONT></SPAN></P>

<P DIR=3DLTR><SPAN LANG=3D"en-us"><FONT FACE=3D"Calibri">Peter =
Headland</FONT></SPAN></P>

<P DIR=3DLTR><SPAN LANG=3D"en-us"><FONT FACE=3D"Calibri">Architect - =
e.Reports</FONT></SPAN><SPAN LANG=3D"en-us"></SPAN><SPAN =
LANG=3D"en-us"></SPAN><SPAN LANG=3D"en-us"></SPAN></P>

<P DIR=3DLTR><SPAN LANG=3D"en-us"></SPAN><SPAN LANG=3D"en-us"><FONT =
FACE=3D"Calibri">Actuate Corporation</FONT></SPAN></P>

<P DIR=3DLTR><SPAN LANG=3D"en-us"></SPAN></P>

</BODY>
</HTML>
------_=_NextPart_001_01C9FEA1.6B81DAD1--



From scott.marlowe@gmail.com Tue Jul  7 06:14:03 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id B550363456A
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 03:14:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 61359-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 03:13:48 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f223.google.com (mail-ew0-f223.google.com
	[209.85.219.223])
	by mail.postgresql.org (Postfix) with ESMTP id 7B4186339A7
	for <pgsql-sql@postgresql.org>; Tue,  7 Jul 2009 03:13:47 -0300 (ADT)
Received: by ewy23 with SMTP id 23so2184440ewy.19
	for <pgsql-sql@postgresql.org>; Mon, 06 Jul 2009 23:13:46 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=UkvMn56/qj3EFFZmZPwshKyDpCcZ8zIJU4MmT9D/jsU=;
	b=bltkQ9ftggVmQuI9cl36Z2DnCCNmKA/pH9pZYJaS/0rItw63Zwaf3A69v8PRhAyq3E
	e/IigMVijsMJQ65P3bbBN5mMSrpz83qVbxVMZwMBsWJQm/DpGUOUCNbo74/UJ8CyCmQ2
	bNe1j50o3wJD2DG5yVEwGTJVGdvwEy9Oh69eY=
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
Received: by 10.211.195.15 with SMTP id x15mr5942735ebp.59.1246947226620;
 Mon,
	06 Jul 2009 23:13:46 -0700 (PDT)
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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200907/26
X-Sequence-Number: 32767

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.



From jagdmann@gmail.com Tue Jul  7 09:05:36 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0DAF1632A22
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 06:05:36 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 95805-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 06:05:21 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-gx0-f210.google.com (mail-gx0-f210.google.com
	[209.85.217.210])
	by mail.postgresql.org (Postfix) with ESMTP id 2FC2F6328EB
	for <pgsql-sql@postgresql.org>; Tue,  7 Jul 2009 06:05:18 -0300 (ADT)
Received: by gxk6 with SMTP id 6so5120914gxk.19
	for <pgsql-sql@postgresql.org>; Tue, 07 Jul 2009 02:05:17 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=apOTcOPnGAHNsem+z7hS1NpRgWfUUamj2xb3g5oEtT0=;
	b=loZiV7ZiZPYRgFGuiigf7ZiaK5vxbI1hvJc4DSRuYUmBIz3pL0PFQKDdhz0tu3Y22u
	CuKom+Y8kW8JqNCvkOyxmYPstPSYrIZB7WnxnzBPOAfTawjn9fGkyFonvHT+TNanSDrJ
	dPcZwTMz3np7KoHnFomGmoMq70AiEUh3KXxxM=
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=JuoGuQn+xrPZwg5BPrfn4u5byRAgcfuMruRSgWU0ZidFF3TIixVE8hjMMqasgP8G7W
	ttmjyeyi5NJhTPHEc4oLFnoW2DwDBKB64I9Pw8U/FFcDVEiQ9A2uAPw98SDzOJ5Dad8i
	9FeyKyALesDm7fURbcx5luKHj3RmlhTQ99oHA=
MIME-Version: 1.0
Received: by 10.100.47.10 with SMTP id u10mr10284154anu.17.1246957516996;
 Tue,
	07 Jul 2009 02:05:16 -0700 (PDT)
In-Reply-To: <dcc563d10907062313j1f0eb27awc2bea6b3f810dc41@mail.gmail.com>
References: <71F491F5DA99604A80DE49424BF3D02B0C089039@exchange8.actuate.com>
	<dcc563d10907062313j1f0eb27awc2bea6b3f810dc41@mail.gmail.com>
Date: Tue, 7 Jul 2009 02:05:15 -0700
Message-ID: <5d0f60990907070205u3189bd01vff17950e63159455@mail.gmail.com>
Subject: Re: Best way to simulate Booleans
From: Dirk Jagdmann <jagdmann@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Peter Headland <pheadland@actuate.com>, pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200907/27
X-Sequence-Number: 32768

> 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)

I would decide depending on the application requirement. If my Oracle
should look similar to PostgreSQL use the char(1). If you have lots of
application code the int is probably better, since you can just use
the created programming language variable (presumably an integer as
well) in your programming language expressions (if, while).

-- 
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org



From simon@2ndQuadrant.com Tue Jul  7 09:17:53 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 428B76322B2
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 06:17:53 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 39680-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 06:17:36 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outmail137146.authsmtp.com (outmail137146.authsmtp.com
	[62.13.137.146])
	by mail.postgresql.org (Postfix) with ESMTP id 5C122632CD3
	for <pgsql-sql@postgresql.org>; Tue,  7 Jul 2009 06:17:41 -0300 (ADT)
Received: from mail-c193.authsmtp.com (mail-c193.authsmtp.com [62.13.128.118])
	by punt9.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id
	n679HVrS007622; Tue, 7 Jul 2009 10:17:31 +0100 (BST)
Received: from [192.168.0.3] (88-111-6-11.dynamic.dsl.as9105.com
 [88.111.6.11])
	(authenticated bits=0)
	by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id n679HUhb027680;
	Tue, 7 Jul 2009 10:17:30 +0100 (BST)
Subject: Re: Best way to simulate Booleans
From: Simon Riggs <simon@2ndQuadrant.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Peter Headland <pheadland@actuate.com>, pgsql-sql@postgresql.org
In-Reply-To: <dcc563d10907062313j1f0eb27awc2bea6b3f810dc41@mail.gmail.com>
References: <71F491F5DA99604A80DE49424BF3D02B0C089039@exchange8.actuate.com>
	<dcc563d10907062313j1f0eb27awc2bea6b3f810dc41@mail.gmail.com>
Content-Type: text/plain
Date: Tue, 07 Jul 2009 10:17:02 +0100
Message-Id: <1246958222.3874.129.camel@ebony.2ndQuadrant>
Mime-Version: 1.0
X-Mailer: Evolution 2.12.0 
Content-Transfer-Encoding: 7bit
X-Server-Quench: ff6825f7-6ad6-11de-98d8-002264978518
X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ
	NksadRtFaAJbZ0xd HGQLW11EUV17WGR/ aAofZQ1DY0tPQQN0
	UUlWQFdQERpoT0IH ARdmN0EgdwdBfnZw K0dkW3QVChJ8fUR4
	FElJEzxSY3phaTRK TUlQIwtJcANIfBlB Y1d3UXIFLwdSbGoL
	JDYXCngbMC5DYDtc U0kRKhoJQEMWGjcg SlgoGjIrEEQDXE0g
X-Authentic-SMTP: 61633235383639.pelican.dmpriest.net.uk:1562/Kp
X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse
X-Virus-Status: No virus detected - but ensure you scan with your own
	anti-virus system.
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL=0.008
X-Spam-Level: 
X-Archive-Number: 200907/28
X-Sequence-Number: 32769


On Tue, 2009-07-07 at 00:13 -0600, Scott Marlowe wrote:
> 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.
> 
> mybool int check (mybool >=0 and <=1)

Integer works best since it converts easily to boolean

mybool smallint check (mybool in (0, 1))

You can use "char" also, but the syntax is less clear.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support




From gsstark@mit.edu Tue Jul  7 09:27:04 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 252BF633D6D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 06:27:04 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 00650-04-7 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  7 Jul 2009 06:26:52 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-fx0-f205.google.com (mail-fx0-f205.google.com
	[209.85.220.205])
	by mail.postgresql.org (Postfix) with ESMTP id 3AF3C633D71
	for <pgsql-sql@postgresql.org>; Tue,  7 Jul 2009 06:26:51 -0300 (ADT)
Received: by fxm1 with SMTP id 1so665299fxm.19
	for <pgsql-sql@postgresql.org>; Tue, 07 Jul 2009 02:26:50 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:sender:received:in-reply-to
	:references:from:date:x-google-sender-auth:message-id:subject:to:cc
	:content-type:content-transfer-encoding;
	bh=lCjyXpWzDZkQIMYQD7rilrKPL9ClZB7eq7wSM4nSvAY=;
	b=G5duhKkF5BjpF9UzhazWMnuR/EfG98yq34FIACpdrQ2YJI1sDDuDsitkMIP5cq+EWC
	f2+QwEzj76DYajot4Wysm3Yb7CrbjrX3WxUntsiaUNtmTMQoVS7p6kyyEPqmU//rHpzT
	iF9412MBZdTePHb3XlYhrGBa6uwrLc/+aLe0Q=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:sender:in-reply-to:references:from:date
	:x-google-sender-auth:message-id:subject:to:cc:content-type
	:content-transfer-encoding;
	b=Dbu4t6gLlyXeUxWwVmb0Fr6UNJQqQiK+yRzMdsKmdHlT11ogYA0VdlyCMsaS3j+GIZ
	fH27/j5N9guRzsdSl42uJffnVdKcNtBB6xqcYNCtsWt7AvFHhyc4Vkbk8HIMvFbePYPB
	00/TQVNc0XE/xJM8mgDby+NFPtf+T0+i/OgC8=
MIME-Version: 1.0
Received: by 10.103.197.17 with SMTP id z17mr3218030mup.19.1246958810216;
 Tue,
	07 Jul 2009 02:26:50 -0700 (PDT)
In-Reply-To: <1246958222.3874.129.camel@ebony.2ndQuadrant>
References: <71F491F5DA99604A80DE49424BF3D02B0C089039@exchange8.actuate.com>
	<dcc563d10907062313j1f0eb27awc2bea6b3f810dc41@mail.gmail.com>
	<1246958222.3874.129.camel@ebony.2ndQuadrant>
From: Greg Stark <gsstark@mit.edu>
Date: Tue, 7 Jul 2009 10:26:30 +0100
X-Google-Sender-Auth: c4e40520c59b246b
Message-ID: <407d949e0907070226p723915c1u8c74a64b4d66593@mail.gmail.com>
Subject: Re: Best way to simulate Booleans
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>,
	Peter Headland <pheadland@actuate.com>, pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200907/29
X-Sequence-Number: 32770

On Tue, Jul 7, 2009 at 10:17 AM, Simon Riggs<simon@2ndquadrant.com> wrote:
>
>
> Integer works best since it converts easily to boolean
>
> mybool smallint check (mybool in (0, 1))
>
> You can use "char" also, but the syntax is less clear.

Hm, I was going to suggest using boolean in postgres and making a
"boolean" domain in Oracle for char(1) and then write all sql to
compare with = 'f' and = 't'. It's annoying you can't use "WHERE
foo_flag" and have to write "WHERE foo_flag = 't'" but otherwise that
would give you the same sql in both flavours.


-- 
greg
http://mit.edu/~gsstark/resume.pdf