Thread: Best way to simulate Booleans

<p dir="LTR"><span lang="en-us"><font face="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="Calibri">as well as
PostgreSQL</font></span><spanlang="en-us"><font face="Calibri">, by which I mean that a single set of
Java/JDB</font><fontface="Calibri">C code has to work with both databases.</font></span><span lang="en-us"> <font
face="Calibri">Ihave a</font></span><span lang="en-us"><font face="Calibri">n</font></span><span lang="en-us"><font
face="Calibri"></font></span><spanlang="en-us"> <font face="Calibri">XML</font></span><span lang="en-us"> <font
face="Calibri">meta-schemathat enables me to generate appropriate DDL</font><font face="Calibri">;
that</font></span><spanlang="en-us"> <font face="Calibri">handles</font> <font face="Calibri">all</font> <font
face="Calibri">theINTEGER vs. NUMBER(m,n) stuff</font></span><span lang="en-us">.<font
face="Calibri"></font></span><spanlang="en-us"> <font face="Calibri">But</font></span><span lang="en-us"><font
face="Calibri"></font></span><spanlang="en-us"> <font face="Calibri">Oracle</font></span><span lang="en-us"> <font
face="Calibri">simply</font></span><spanlang="en-us"> <font face="Calibri">has no</font></span><span lang="en-us">
<fontface="Calibri">Boolean</font></span><span lang="en-us"><font face="Calibri">s</font></span><span
lang="en-us"><fontface="Calibri">, so I will have to resort to</font> <font face="Calibri">some more</font></span><span
lang="en-us"><fontface="Calibri"></font></span><span lang="en-us"> <font face="Calibri">or</font></span><span
lang="en-us"><fontface="Calibri"></font></span><span lang="en-us"> <font face="Calibri">less ugly
alternative</font></span><spanlang="en-us">.<font face="Calibri"> I am ho</font><font face="Calibri">ping that others
herehave had to deal with this and can suggest an approach that will be minimally loathsome.</font></span><p
dir="LTR"><spanlang="en-us"><font face="Calibri"><VENT></font><font face="Calibri">God I hate
Oracle</font></span><spanlang="en-us"><font face="Calibri">…</font></span><span lang="en-us"><font
face="Calibri"></VENT></font></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">-- </font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Peter
Headland</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">Architect - e.Reports</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">Actuate Corporation</font></span><p dir="LTR"><span lang="en-us"></span> 

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.

> 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

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.comPostgreSQL Training, Services and Support

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