Question 205934 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Check whether any boolean column has value TRUE

I have a table with a boolean column and want know if atleast one record has a TRUE value in that column.

This query works as expected with postgresql:

SELECT sum(coalesce(CAST(boolean_column AS INT),0)) <> 0
FROM entity
WHERE correlation_id = 123;

But in case of Microsoft SQL Server i get a syntax error near ‘<‘!

Background is that my application should support both: PostgreSQL and SQL-Server and I don’t want to create different SQL scripts for each database backend.

A work-around may be to use an INT column and handle the boolean expression at application level.

Any hints welcome!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This query solved my problem for both: PostgreSQL and SQL-Server:

    SELECT DISTINCT 1
FROM entity
WHERE correlation_id = 123
  AND CAST(boolean_column AS INT) = 1

    Because in PostgreSQL the boolean_column is of type BOOLEAN and in SQL-Server it is of type BIT. PostgreSQL interprets 1 as TRUE and that is all I am interested in (see PostgreSQL - Boolean Type).


  2. 0

    You can try using EXISTS which is supported by both PostgreSQL and SQL Server, and is generally more performant than a full count of matching rows. The query stops as soon as it finds a single matching row:

    SELECT EXISTS(SELECT 1 FROM entity WHERE boolean_column = TRUE AND correlation_id = 123);

    SQL Server does not have a TRUE literal and uses 1 for BIT columns, which are typically used to represent boolean values.

    In SQL Server, you can modify the query like this:

    SELECT CASE 
   WHEN EXISTS(SELECT 1 FROM entity WHERE boolean_column = 1 AND correlation_id = 123) 
   THEN 1 
   ELSE 0 
   END;

    In PostgreSQL,

    SELECT CASE 
   WHEN EXISTS(SELECT 1 FROM entity WHERE boolean_column = TRUE AND correlation_id = 123) 
   THEN 1 
   ELSE 0 
   END;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search