為什麼不能在 PostgreSQL 的索引中轉換為時間戳？

我正在處理從外部來源收到的 JSON 文件。在本文件中，有一個timestamp屬性對應於timestampPostgreSQL 數據類型的文本格式。所以換句話說，它可以轉換為一個timestamp類型：(data->>'timestamp')::timestamp。

我想timestamp在索引中使用該屬性。我嘗試了以下方法：

create table t
(
   data    jsonb
);

create index on t(((data->>'timestamp')::timestamp));

我正進入（狀態：

ERROR:  functions in index expression must be marked IMMUTABLE

為什麼是這樣？我知道轉換為 atimestamptz不是一成不變的，因為本地化配置設置不是靜態的，但我不能將該邏輯應用於轉換為timestamp.

還有一種方法可以在這裡將我的timestamp財產放入索引中嗎？

我正在使用幾天前發布的 PostgreSQL 12。

轉換 from texttotimestamp without time zone是通過呼叫類型輸入函式來處理的timestamp_in，即STABLE.

原因是它也timestamp_in支持其他一些格式：

SELECT 'now'::timestamp;

        timestamp          
----------------------------
2019-10-09 09:53:32.026673
(1 row)

如果您知道您的輸入始終是 ISO 時間戳並且這不可能發生，請定義您自己的轉換函式：

CREATE FUNCTION text2ts(text) RETURNS timestamp without time zone
  LANGUAGE sql IMMUTABLE AS
$$SELECT CASE WHEN $1 ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d+)?$'
           THEN CAST($1 AS timestamp without time zone)
      END$$;

您可以在查詢和索引中使用它。

如果您缺乏偏執狂並且確信您的數據質量，您可以省略測試。這將提高性能，但如果有人將類似的東西偷偷now帶入數據，您將面臨索引損壞的風險。

引用自：https://dba.stackexchange.com/questions/250627