Compatible boolean data type of various relational databases

Question

I am trying to create a select statement that is compatible with all major relational databases (MySQL, PostgreSQL, Derby, SQLite, ...).

Here the simple select statement: SELECT * FROM taggings WHERE public IS TRUE

The problem is that SQLite for example does not support the boolean data type, so I rewrote my statement to: SELECT * FROM taggings WHERE public = 1

As far as I know it is valid for SQLite (wich uses some kind of byte for the public field) and also MySQL (which interprets the 1 as true, cause it uses the boolean type for the public field).

How about PostgreSQL when also uses a boolean public field? From the manual it looks like that I have to write SELECT * FROM taggings WHERE public = '1'. Or is that equivalent in that case to the above statement? (I don't have a chance to setup a PostgreSQL database here.)

Thanks for the help!

Answer 1

Two options:

• CHAR(1), storing 'Y' or 'N'
• INT, storing 1 or zero

In either case, you'd be looking at a CHECK constraint to enforce the values -- on MySQL, that'd mean using a trigger because MySQL has CHECK syntax, but doesn't enforce it.