postgresql - 在 PostgreSQL 上从 JSON 创建时间戳索引

Question

我在 PostgreSQL 上有一个表，其中有一个名为 data 的字段，即 jsonb 和很多对象，我想建立一个索引来加速查询。我使用几行来测试数据(只有 15 行)，但我不希望将来出现查询问题。我从 Twitter API 获取数据，所以一周后我获取了大约 10gb 的数据。
如果我做的是普通索引

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

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

在推文上创建索引((CAST(data->>'created_at' AS timestamp)));

我明白了

错误:索引表达式中的函数必须标记为 IMMUTABLE

我试图通过设置时区使其“不可变”

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

但我仍然收到“不可变”错误。那么，如何从 JSON 完成时间戳索引？我知道我可以用日期制作一个简单的列，因为它可能会在一段时间内保持不变，但我想学习如何做到这一点。

Answer 1

这个表达式也不允许出现在索引中:

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

它不是不可变的，因为第一次转换取决于你的 DateStyle设置(除其他事项外)。函数调用后 将结果转换为 UTC 无济于事，不确定性已经悄悄进入 ...

解决方案是一个函数，它通过固定时区(如 @a_horse already hinted)使转换不可变。

我建议使用 to_timestamp() (这也只是 STABLE，而不是 IMMUTABLE)而不是强制转换以排除一些问题来源 - DateStyle 是一个。

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。然后:

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

此相关答案中对此技术的详细解释:

• Does PostgreSQL support "accent insensitive" collations?

相关:

• Query on a time range ignoring the date of timestamps