PostgreSQL：索引时间戳的日期部分 [英] PostgreSQL: Index the day part of a timestamp

问题描述

考虑下表：

       Column       |           Type           |
--------------------+--------------------------+
 id                 | bigint                   |
 creation_time      | timestamp with time zone |
...

如下所示的查询（更不用说更复杂的JOIN）同时，因为他们需要为每个项目计算creation_time :: DATE：

Queries like the following (let alone more complicated JOINs) takes quite a while, because they needs to calculate creation_time::DATE for each item:

SELECT creation_time::DATE, COUNT(*) FROM items GROUP BY 1;

如何在时间戳的日期创建索引 - creation_time :: DATE ？

How do I create an index on the day part of the timestamp - creation_time::DATE?

我尝试过：

• CREATE INDEX items_day_of_creation_idx ON items（creation_time）:: date;


• CREATE INDEX items_day_of_creation_idx ON项目（creation_time :: date）;

• CREATE INDEX items_day_of_creation_idx ON items (creation_time)::date;
• CREATE INDEX items_day_of_creation_idx ON items (creation_time::date);

但是两者都失败：

But both failed with:

ERROR:  syntax error at or near "::"

推荐答案

在表达式上创建索引时，表达式必须放在括号之间（除了列/表达式列表之外的括号外） ：

When you create an index on an expression that expression must be put between parentheses (in addition to the parentheses that surround the column/expression list:

CREATE INDEX items_day_of_creation_idx ON items ( (creation_time::date) );

这篇关于PostgreSQL：索引时间戳的日期部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！