JSON嵌套timestamptz

一个timestamptz（json列中的timestamp with time zone)字段
JSON中没有 ”timestamptz字段” 这回事。JSON只理解：object，array，string，number，boolean，null。
我假设这是正确的处理与ISO 8601 timestamptz * 字符串 * 在外地排他性。否则，有更多的问题要解决。
相关博客：

• https://jsoneditoronline.org/indepth/parse/json-date-format/

伪IMMUTABLE

从text到timestamptz的转换本身并不是IMMUTABLE，它取决于像datestyle这样的volatile设置。
在您的特定情况下，我们可以将强制转换移动到函数中作为解决方案：

CREATE OR REPLACE FUNCTION to_text(text) 
  RETURNS text
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT to_char($1::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US') 
$func$;

字符串
由于只有ISO 8601日期，并且没有依赖于本地化设置的模板模式，这实际上是不可变的。

更好的索引

但是，现在没有必要强制转换为text并返回。直接索引返回的timestamptz值！（您最初假设这是不可能的，这不成立！）

CREATE OR REPLACE FUNCTION to_tstz_immutable(text) 
  RETURNS timestamptz
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN $1::timestamptz;

-- then simply:
CREATE INDEX matches_start_time_idx ON matches (to_tstz_immutable(match->>'start_time'));

型
使用一个“SQL标准”函数（随Postgres 14添加）。参见：

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?的

在Postgres 9.6或更高版本中添加PARALLEL SAFE。请参阅：

• PARALLEL标签用于带有SELECT和SELECT的函数

更好

实际上，对该表的所有选择查询都将按 start_time 排序。
根据上面的函数添加一个生成的列（Postgres 12添加的），并在其上添加一个普通索引：

ALTER TABLE matches
ADD COLUMN start_time timestamptz GENERATED ALWAYS AS (to_tstz_immutable(match->>'start_time')) STORED

CREATE INDEX matches_start_time_idx ON matches (start_time);

型
甚至更简单的日常处理，和更有效.增加8字节支付时使用了很多.见：

• PostgreSQL中的计算/计算/虚拟/派生/生成列
  或者将时间戳从JSON文档中移到它自己的列中开始开始，这样最好。