Someway to do `where booleanvalue=false` on both Sql Server and PostgreSQL?

Question

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

Antebi

Answer 1

SQL Server will automatically change the bit value to the varchar value of true or false. So the following works there:

select * from table where booleancol = 'false'

I have no idea if postgre does the same thing.

Internist

Answer 2

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.

Balcony

yes, but out of your list, only 2 syntaxes are also supported by Sql Server and I happened to be using what was only valid in SQL Server: select * from table where booleancol=0 – Antebi

That list isn't exhaustive, but the docs link is: The syntax you're using (0/1 for false/true) is also valid PostgreSQL. – Rivalry

Answer 3

At work, we use 'T' and 'F' in char(1) columns to represent booleans in SQL Server. I'm not sure if this sort of compatibility was the reason, but it does mean that "booleancol = 'F'" would work on either flavour of database.

Pauperize

Answer 4

If it's an option, take a look at an Object Relational Mapping framework, which could handle the problem of translating SQL from one RDBMS to another.

Loner

Answer 5

Use an ORM, there is no need today to hand code SQL. They exist so solve the very problem you have run into.

Harem

It was downvoted because it (a) entirely fails to answer the question, and (b) pretends to know everything about OPs application. – Hurleigh