Postgres, return 0 rows if any row matches condition, otherwise return all rows

Question

I have a table with ten rows. One of the columns is a boolean, let's just say for argument's sake:

email text
is_activated boolean

I'd like to write a query that returns zero rows if is_activated is true for any of the rows. If none of the rows have is_activated set to true, I'd like to return all of the rows in the table.

How can I do that?

Answer 1

SELECT * FROM table_name 
WHERE NOT EXISTS 
(
 SELECT 1 FROM table_name WHERE is_activated IS TRUE
);

Answer 2

Another option is to use a filtered window aggregate COUNT() to get the number of rows matching the required condition, then use the result in WHERE to decide whether the rows should be returned, like this:

SELECT
 email_text
FROM
 (
 SELECT
 email_text,
 COUNT(*) FILTER (WHERE is_activated) OVER () AS activated_count
 -- or: (WHERE is_activated IS TRUE)
 FROM
 your_table
 ) AS counted
WHERE
 activated_count = 0
;

You can also try using a common table expression (CTE) instead of the derived table:

WITH
 counted AS
 (
 SELECT
 email_text,
 COUNT(*) FILTER (WHERE is_activated) OVER () AS activated_count
 FROM
 your_table
 )
SELECT
 email_text
FROM
 counted
WHERE
 activated_count = 0
;

There is no logical difference between the two queries, but due to the fact that CTEs in PostgreSQL are always materialised during query execution, there may be difference in performance depending on your setup. Therefore it would make sense to try both variations.