postgresql 如果所有列值都为真，则返回真

Return true if all column values are true

提问by mouckatron

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

在 PostgreSQL 中是否有一种更快的方法可以在多行上执行 if ？

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

有什么方法可以在ticket = 的列中执行if 语句吗？因此，ticket = 1 的位置为真，因为

true && true && true = true

and where ticket = 2 would be false because

并且其中 ticket = 2 将是错误的，因为

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) )

回答by Erwin Brandstetter

Aggregate function bool_and()

聚合函数 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

子查询表达式 EXISTS

Like @Mike provided.

就像@Mike 提供的一样。

Faster. But you have to additionally check whether any rows with ticket = 1exist at all, or you'll get incorrect results for non-existing tickets:

快点。但是您必须另外检查是否ticket = 1存在任何行，否则对于不存在的票证您会得到不正确的结果：

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 EXISTSquery 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 substantialdifference for many rows per ticket.

.. 这使得两者都很快，但EXISTS查询速度更快，因为一旦找到第一个匹配的行，这个表单就可以停止扫描。两个查询之间几乎没有任何区别，每张票只有几行，但每张票的多行有很大差异。

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

要在 pg 9.2 中使用仅索引扫描，您需要以下形式的多列索引：

CREATE index tbl_ticket_archived_idx ON tbl (ticket, archived);

This one is betterin any casemost cases and any version of PostgreSQL. Due to data alignment, adding a booleanto the integerin the index will not make the index grow at all. Added benefit for hardly any cost.

这是一个更好的在任何情况下，大多数情况下和PostgreSQL的任何版本。由于数据排列，添加boolean到integer索引中不会使所有的指数增长。几乎不需要任何成本就增加了好处。

However, indexed columns prevent HOT (Heap Only Tuple) updates. Say, an UPDATEchanges 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:

但是，索引列会阻止 HOT（仅堆元组）更新。说， anUPDATE只更改 column archived。如果该列未被任何索引（以任何方式）使用，则该行可以被热更新。否则，这条捷径不能走。更多关于热更新：

• Redundant data in update statements

• 更新语句中的冗余数据

It all depends on your actual workload.

这一切都取决于您的实际工作量。

回答by Mike Christensen

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 countmay or may not use an indexand really all you need to know is if anyFALSErows exist for that ticket. I think just creating a partial index on ticketcould be incredibly fast.

我认为这可能比比较计数更有优势，因为count可能会或可能不会使用索引，并且您真正需要知道的是该票证是否存在任何FALSE行。我认为仅创建部分索引ticket可能会非常快。

SQL Fiddle

SQL小提琴

回答by Clodoaldo Neto

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

SQL Fiddle

SQL小提琴