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.