时间戳

首页 > 解决方案 > Postgres 在 JSONB 日期时间字符串上创建索引，使用 to_timezone 格式

postgresql - Postgres 在 JSONB 日期时间字符串上创建索引，使用 to_timezone 格式

问题描述

使用 postgres 10.4。

我有一个表my_table1，其中有一列attr_a声明为jsonb

在那个领域我有{ my_attrs: {UpdatedDttm:'<date as iso8601 str>}}

例如：

{ my_attrs: {UpdatedDttm:'2018-09-20T17:55:52Z'}}

我在需要进行排序的查询中使用日期（并与另一个日期进行比较）。

因为日期是字符串，所以我必须使用 to_timestamp 将它们即时转换为时间戳。

但我还需要将该顺序和过滤条件添加到索引中。这样 Postgres 就可以利用这一点。

问题是，我的索引创建失败并显示以下消息：

create index my_table1__attr_a_jb_UpdatedDttm__idx ON my_table1 using btree (((to_timestamp(attr_a->'my_attrs'->>'UpdatedDttm', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')) ) DESC);

错误：索引表达式中的函数必须标记为 IMMUTABLE SQL 状态：42P17

有没有办法让它工作（希望不创建自定义日期格式化函数），我需要保留时区（不能删除它）。

我检查了一个类似的问题

我正在使用那里推荐的 to_timestamp 并且没有 ::timestamptz 类型转换。

如果我使用内置函数，仍然会出现错误。

该答案建议创建我自己的函数（但我试图避免这种情况（如我在底部的问题中所述，因为它需要重写/更改使用该表的所有 sql statemetns）。

标签： postgresqljsonbtimestamp-with-timezone

解决方案

您可以创建一个 INSERT/UPDATE 触发器，将嵌套的 JSONB 值移出到顶级列；然后像平常一样索引。然后，您还将在查询中使用该顶级列，有效地忽略嵌套的 JSONB 值。

推荐阅读