Return true if all column values are true

Question

Is there a faster way in PostgreSQL to essentially do an if on several rows?

Say I have a table

ticket | row | archived
1      | 1   | true
1      | 2   | true
1      | 3   | true
2      | 1   | false
2      | 2   | true

Is there any way I could do an if statement across down the column where ticket = ? So that where ticket = 1 would be true because

true && true && true = true

and where ticket = 2 would be false because

false && true = false

Or should I just stick with

SELECT ( (SELECT COUNT(*) FROM table WHERE ticket = 1)
       = (SELECT COUNT(*) FROM table WHERE ticket = 1 AND archived = true) )

Answer 1

Aggregate function bool_and()

Simple, short, clear:

SELECT bool_and(archived)
FROM   tbl
WHERE  ticket = 1;

The manual:

true if all input values are true, otherwise false

Subquery expression EXISTS

Assuming archived is defined NOT NULL. Faster, but you have to additionally check whether any rows with ticket = 1 exist at all, or you'll get incorrect results for non-existing tickets:

SELECT EXISTS (SELECT FROM tbl WHERE ticket=1)
       AND NOT
       EXISTS (SELECT FROM tbl WHERE ticket=1 AND NOT archived);

Indices

Both forms can use an index like:

CREATE INDEX tbl_ticket_idx ON tbl (ticket);

.. which makes both fast, but the EXISTS query faster, because this form can stop to scan as soon as the first matching row is found. Hardly matters for only few rows per ticket, but matters for many.

To make use of index-only scans you need a multi-column index of the form:

CREATE INDEX tbl_ticket_archived_idx ON tbl (ticket, archived);

This one is better in most cases and any version of PostgreSQL. Due to data alignment, adding a boolean to the integer in the index will not make the index grow at all. Added benefit for hardly any cost.
Update: this changes in Postgres 13 with index deduplication. See:

• Is a composite index also good for queries on the first field?

However, indexed columns prevent HOT (Heap Only Tuple) updates. Say, an UPDATE changes only the column archived. If the column isn't used by any index (in any way), the row can be HOT updated. Else, this shortcut cannot be taken. More on HOT updates:

• Redundant data in update statements

It all depends on your actual workload.

Answer 2

How about something like:

select not exists (select 1 from table where ticket=1 and not archived)

I think this might be advantageous over comparing the counts, as a count may or may not use an index and really all you need to know is if any FALSE rows exist for that ticket. I think just creating a partial index on ticket could be incredibly fast.

SQL Fiddle

Answer 3

select not false = any (
        select archived
        from foo
        where ticket = 1
    )

SQL Fiddle