0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

PostgreSQL Generated Column dateを含んだ式を使う方法

Posted at

概要

PostgreSQL12 からGenerated Columnが使えるようになりましたね。
私はdateを含んだ式を仕込んでおこうと思いました。
しかし、 エラーが出て駄目でした。
exampleで使っているものは仮です。実用から読み替えて書いています。

ALTER TABLE diaries
    ADD IF NOT EXISTS subject_date varchar(255)
    GENERATED ALWAYS AS (subject::text || '__' || date(timezone('JST', published_on)) STORED;
ERROR:  生成式は不変ではありません

TL;DR;

CREATE FUNCTION my_immutable_date(date) RETURNS TEXT LANGUAGE sql IMMUTABLE
AS $$
SELECT date(timezone('JST', $1))::text
$$;

ALTER TABLE diaries
    ADD IF NOT EXISTS subject_date varchar(255)
    GENERATED ALWAYS AS (subject::text || '__' || my_immutable_date(published_on)) STORED;

調査

dateをStringに変換するとき、タイムゾーン設定の関係でImmutableとして認識してくれないようです。
StackOverfllowを見ると、タイムゾーンを指定した変換をすると不変関数として動作してGenerated Columnが動くらしいですが、私の環境では動きませんでした。

解決法

https://www.2ndquadrant.com/en/blog/generated-columns-in-postgresql-12/
強そうな人の解説を見ました。
IMMUTABLEな関数を定義して、その中で変換をするラッパー関数を定義すれば動作するとのことです。

CREATE FUNCTION my_immutable_date(date) RETURNS TEXT LANGUAGE sql IMMUTABLE
AS $$
SELECT date(timezone('JST', $1))::text
$$;

ALTER TABLE diaries
    ADD IF NOT EXISTS subject_date varchar(255)
    GENERATED ALWAYS AS (subject::text || '__' || my_immutable_date(published_on)) STORED;
0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?