I am attempting to make an application capable of running on both Sql Server and PostgreSQL.
I can not seem to find a common expression that is basically
select * from table where booleancol=false
on SQL Server I must do(which is very confusing because the default value for bit types must be true or false, but you can't assign them to true or false or test against it)
select * from table where booleancol=0
on PostgreSQL I must do
select * from table where booleancol is false
There are quite a lot of queries in our program that does this, so I'd prefer if there was just some universal syntax I could use instead of doing if(dbformat=="postgres")..
type crap..
Also, I'd prefer to leave the columns as boolean/bit types and not change them to integer types.. though that is an option..
Sorry, this part is simply not true; less than half-true ;-)
on PostgreSQL I must do
select * from table where booleancol is false
In fact, all following syntaxes are valid in PostgreSQL:
select * from table where not booleancol
select * from table where booleancol = 'f'
select * from table where booleancol = 'false'
select * from table where booleancol = 'n'
select * from table where booleancol is false
select * from table where booleancol is not true
select * from table where booleancol = false
select * from table where booleancol <> true
select * from table where booleancol != true
select * from table where booleancol <> 'TRUE'
That's example of postgres flexible syntax, and it makes porting apps from other databases quite easy.
See the docs.