在PostgreSQL上从JSON创建时间戳索引 [英] Create timestamp index from JSON on PostgreSQL

问题描述

我在PostgreSQL上有一个表，该表的名为data的字段是jsonb，其中包含许多对象，我想创建一个索引以加快查询速度.我正在使用几行来测试数据(仅15行)，但我不想将来出现查询问题.我正在从Twitter API获取数据，所以一周的时间我会收到大约10gb的数据.
如果我进行正常索引

I have a table on PostgreSQL with a field named data that is jsonb with a lot of objects, I want to make an index to speed up the queries. I'm using few rows to test the data (just 15 rows) but I don't want to have problems with the queries in the future. I'm getting data from the Twitter API, so with a week I get around 10gb of data.
If I make the normal index

CREATE INDEX ON tweet((data->>'created_at'));

如果我做的话，我会得到一个文本索引:

I get a text index, if I make:

Create index on tweet((CAST(data->>'created_at' AS timestamp)));

我知道

ERROR: functions in index expression must be marked IMMUTABLE

我尝试通过

date_trunc('seconds', CAST(data->>'created_at' AS timestamp) at time zone 'GMT')

但是我仍然遇到不可变的"错误.那么，如何从JSON完成时间戳索引?我知道我可以用日期创建一个简单的列，因为它可能会在时间中保持不变，但是我想学习如何做到这一点.

but I'm still getting the "immutable" error. So, How can I accomplish a timestamp index from a JSON? I know that I could make a simple column with the date because probably it will remain constant in the time, but I want to learn how to do that.

推荐答案

在索引中也不允许该表达式:

This expression won't be allowed in the index either:

(CAST(data->>'created_at' AS timestamp) at time zone 'UTC')

这不是一成不变的，因为第一次转换取决于您的

It's not immutable, because the first cast depends on your DateStyle setting (among other things). Doesn't help to translate the result to UTC after the function call, uncertainty has already crept in ...

解决方案是通过固定时区(使使不变)的功能(例如

The solution is a function that makes the cast immutable by fixing the time zone (like @a_horse already hinted).

我建议使用 to_timestamp() 代替强制转换(也不是IMMUTABLE)排除某些麻烦源-DateStyle是一个麻烦源.

I suggest to use to_timestamp() instead of the cast (which is also not IMMUTABLE) to rule out some source of trouble - DateStyle being one.

CREATE OR REPLACE FUNCTION f_cast_isots(text)
  RETURNS timestamptz AS
$$SELECT to_timestamp($1, 'YYYY-MM-DD HH24:MI')$$  -- adapt to your needs
  LANGUAGE sql IMMUTABLE;

请注意，这将返回timestamptz.然后:

Note that this returns timestamptz. Then:

CREATE INDEX foo ON t (f_cast_isots(data->>'created_at'));

此相关答案中对此技术的详细说明:

Detailed explanation for this technique in this related answer:

• PostgreSQL是否支持不区分重音"?排序规则?

相关:

• 在时间范围上查询忽略时间戳记的日期

这篇关于在PostgreSQL上从JSON创建时间戳索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！