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

Like @Mike provided.

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 1 FROM tbl WHERE ticket=1)
       AND NOT
       EXISTS (SELECT 1 FROM tbl WHERE ticket=1 AND archived = FALSE);

Indices

Both forms can and will 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. There is hardly any difference between the two queries with only few rows per ticket, but a substantial difference for many rows per ticket.

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

CREATE index tbl_ticket_archived_idx ON tbl (ticket, archived);

This one is better in any case 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.

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.