pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBest way to simulate Booleans
5+ messages / 5 participants
[nested] [flat]
* Best way to simulate Booleans
@ 2009-07-07 01:22 Peter Headland <pheadland@actuate.com>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Headland @ 2009-07-07 01:22 UTC (permalink / raw)
To: pgsql-sql
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>
--
Peter Headland
Architect - e.Reports
Actuate Corporation
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to simulate Booleans
@ 2009-07-07 06:13 Scott Marlowe <scott.marlowe@gmail.com>
parent: Peter Headland <pheadland@actuate.com>
0 siblings, 2 replies; 5+ messages in thread
From: Scott Marlowe @ 2009-07-07 06:13 UTC (permalink / raw)
To: Peter Headland <pheadland@actuate.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to simulate Booleans
@ 2009-07-07 09:05 Dirk Jagdmann <jagdmann@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dirk Jagdmann @ 2009-07-07 09:05 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Peter Headland <pheadland@actuate.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to simulate Booleans
@ 2009-07-07 09:17 Simon Riggs <simon@2ndQuadrant.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Simon Riggs @ 2009-07-07 09:17 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Peter Headland <pheadland@actuate.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to simulate Booleans
@ 2009-07-07 09:26 Greg Stark <gsstark@mit.edu>
parent: Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Stark @ 2009-07-07 09:26 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Peter Headland <pheadland@actuate.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-07-07 09:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-07 01:22 Best way to simulate Booleans Peter Headland <pheadland@actuate.com>
2009-07-07 06:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-07-07 09:05 ` Dirk Jagdmann <jagdmann@gmail.com>
2009-07-07 09:17 ` Simon Riggs <simon@2ndQuadrant.com>
2009-07-07 09:26 ` Greg Stark <gsstark@mit.edu>
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