PostgreSQLで、生成式にTo_charを使いたい

Question

前提

お世話になります。

自作のメモアプリにて以前はMySQLを接続していたのですが、この度PostgreSQLに移行することになりました。
今まで使用していたMySQLのテーブルには以下のように定義したカラムが存在しており、PostgreSQLでもこれと同様に機能するカラムを作りたいと考えています。

ALTER TABLE memos ADD modified_stamp VARCHAR(15) GENERATED ALWAYS AS (DATE_FORMAT(modified, '%b %d %y %k:%i')) STORED;

調べてみたところTo_charという関数を見つけたため、以下のように定義してみたところ、「生成式は不変ではありません」というエラーが起こり上手くいきませんでした。

// 実行コマンド
ALTER TABLE memos ADD modefied_stamp VARCHAR(15) GENERATED ALWAYS AS (To_char(modified, 'Mon DD YY HH24:MI')) STORED;
// エラー文
ERROR:  生成式は不変ではありません

検索してみたのですが、このエラーについての情報がほとんど出回っておらず、対処法を見つけることができませんでした。'GENERATED ALWAYS AS ~ STRED'という書き方自体はPostgreSQLにも存在しており、見たところ書式も間違ってはいないようなのですが……。
SELECT時に指定すればいい話ではあるのですが、諸事情があり難しいです。

このエラーを解決する方法はありますか？
もしくは、同様の結果を望める代替の方法はあるでしょうか。

お詳しい方、お力添えをいただけますと幸いです。

該当のテーブル

mysql> desc memos;
+----------------+----------------+------+-----+-------------------+-----------------------------------------------+
| Field          | Type           | Null | Key | Default           | Extra                                         |
+----------------+----------------+------+-----+-------------------+-----------------------------------------------+
| id             | int            | NO   | PRI | NULL              | auto_increment                                |
| title          | varchar(40)    | YES  |     | Untitled          |                                               |
| content        | varchar(16383) | YES  |     | NULL              |                                               |
| created        | timestamp      | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| modified       | datetime       | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| modified_stamp | varchar(15)    | YES  |     | NULL              | STORED GENERATED                              |
| is_removed     | tinyint(1)     | NO   |     | 0                 |                                               |
| is_locked      | tinyint(1)     | NO   |     | 0                 |                                               |
+----------------+----------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.05 sec)

memo_app=# \d memos;
                                         テーブル"public.memos"
     列     |           タイプ            | 照合順序 | Null 値を許容 |            デフォルト
------------+-----------------------------+----------+---------------+-----------------------------------
 id         | integer                     |          | not null      | nextval('memos_id_seq'::regclass)
 title      | character varying(40)       |          |               | 'Untitled'::character varying
 content    | character varying(10485760) |          |               |
 created    | timestamp without time zone |          | not null      | CURRENT_TIMESTAMP
 modified   | timestamp without time zone |          | not null      | CURRENT_TIMESTAMP
 is_removed | boolean                     |          | not null      | false
 is_locked  | boolean                     |          | not null      | false
インデックス:
    "memos_pkey" PRIMARY KEY, btree (id)
トリガー:
    update_tri BEFORE UPDATE ON memos FOR EACH ROW EXECUTE FUNCTION set_update_time()

補足情報（FW/ツールのバージョンなど）

実行環境はWindows11です。

MySQL: v8.0.3
PostgreSQL: v16.1

以下、'GENERATED ALWEIS AS ~ STRED'についての参照↓
https://www.postgresql.org/docs/current/ddl-generated-columns.html

どうぞよろしくお願いいたします。

Comments

[dameo]

to_charはtimezoneとlocaleに依存するmutableな関数なので、ユーザー定義しないとダメです。

select To_char(now() at time zone 'JST', 'Mon DD YY HH24:MI');

CREATE OR REPLACE FUNCTION my_to_char(some_time timestamp)
RETURNS text
AS
$BODY$
select to_char($1, 'Mon DD YY HH24:MI');
$BODY$
LANGUAGE sql
IMMUTABLE;

CREATE TABLE example
(
modified TIMESTAMP NOT NULL,
modefied_stamp VARCHAR(15) GENERATED ALWAYS AS (my_to_char(modified)) STORED
);

insert into example values(now() at time zone 'JST');
select * from example;

-- to_char
-- -----------------
-- Dec 04 23 09:09
-- (1 row)
--
-- CREATE FUNCTION
-- CREATE TABLE
-- INSERT 0 1
-- modified | modefied_stamp
-- ----------------------------+-----------------
-- 2023-12-04 09:09:02.512622 | Dec 04 23 09:09
-- (1 row)

※諸事情のある自作のメモアプリって何だろう？

[kanon_2155103]

コメントありがとうございます！

なるほど、関数などの定義が必要になってくるのですね……。
まだPostgreSQLに関する知識が浅いため、勉強しつついただいた回答を紐解いていこうと思います。ゆっくりになるとは思いますが、結果を確かめることができたらご報告いたします。

アプリに諸事情があるというより、PostgreSQLに移行した後もMySQLのテーブルと比較を続けたく、それにあたってMySQLにもPostgreSQLにも対応するプログラムを残していたいという理由です。
SELECTの際にTo_charを指定することになると、MySQLとの互換性が無くなってしまうので……。
個人的な事情で恐れ入ります。

[dameo]

そんな個人的な事情って…業務ならよくある話
大体'Mon DD YY HH24:MI'っていうフォーマットを個人で使いたい日本語話す人って…

[kanon_2155103]

業務では同じアプリにMySQLとPostgreSQLを併用することがよくあるのですね。
もし良かったらなのですが、参考までにその理由をご教示願えませんか……？

[dameo]

移行した後もしばらく昔のテーブルと比較して整合性の確認をするのが普通です
DBを利用するシステムは1つとは限りませんしね
DBを変える理由は特性や利用するアプリやツールやシステムや値段の都合などいくらでもありますよ

[kanon_2155103]

なるほど、企業さんでもそうなんですね！
教えていただきありがとうございます。

[dameo]

わざわざ自作のメモアプリと偽るくらいなら、業務と記載して聞きましょう
本来仕事ならお金を払うべきですけどね

[kanon_2155103]

自作は自作です。就活にあたってポートフォリオに乗せたくて作っています。
凝ったフォーマットを使っているのは完全に個人の趣味ですね。

とはいえデザイン面には拘っているので、業務のものだと思われるのは嬉しいです。

[dameo]

デザインなどは見ていませんし、質問内容や質問の仕方も、およそ業務でプログラミングをしている人のレベルには遠く及びません。

しかし実力も不十分で他人任せなのに、個人かつ格安で稚拙な仕事を請け負う人が跡を絶たず、業界全体のレベルを落としている気がします。今採用担当が頭を悩ませているのは、そういう身勝手で他人の迷惑を顧みないモラル不足な人を入れないためであることをお考えください。努力を惜しまず、勤勉に働いてくれる人なら、多少能力が劣っていたとしてもそんな篩に掛ける必要もないので。

[kanon_2155103]

デザインの話は何かお疑いされていると思ったから持ち出しました。

自身が未熟で実務につかれている方のレベルに及んでいないのは当然承知の上です。
勿論お付き合いいただく方にはご不便を強いてしまっており、その点については恐縮の限りですが……。
だからこそ現在、こうしてプログラム制作の経験を積みながら勉強をしているのです。

また、私を採用するかどうかは、制作物を実際にご覧になった採用担当の方が判断されることです。
考えるまでもなく、私が自分の制作物を抱えてインターンやアルバイトの選考に申し込むこと自体はなんの問題も無いでしょう。

[dameo]

残念ながら皆さんそう言いながら、肝心の勉強を疎かにし、申し込んだ某のために時間を浪費し、稚拙な仕事をしつつ、他人任せで進めた上で自分の成果と偽り採用を困らせるわけです。採ってみてこんなはずじゃなかった案件が続出し、来年にはさらに狭き門になり…という悪循環ですね。

[kanon_2155103]

失礼ですが、私に何を求めていらっしゃるのですか？なぜ私がそういう人間である前提で話を進めるのでしょうか。
そのお話を今この場でなさる意図を図りかねております。

本来ここはデータベースに関する質問の場であるはずです。それとも、この投稿にそれほどの問題がございましたか？

[dameo]

私は必要なことがあなたに伝わればそれで大丈夫ですよ。
> ※諸事情のある自作のメモアプリって何だろう？
に端を発し、あなたの言動の不自然な点を中心にお話してただけなので。。。

[kanon_2155103]

そうでしたか。
質問の仕方に失礼があったのかと思いましたが、そうではないようで安心いたしました。

それでは、最初にいただいた回答を試せたらまたご報告いたします。

[dameo]

不自然さは増す一方で一向に全く解消されなかったですからね
コメントに時間を浪費するより、問題解決に全力でないと諸事情で困るかもしれません
頑張ってください

[kanon_2155103]

ありがとうございます。

[kanon_2155103]

> dameo様
お世話になっております。
いただいた回答と同じような形ではあるのですが、おかげをもちましてカラムの生成に成功いたしました。

CREATE FUNCTION
custom_to_char(timestamp)
RETUENS TEXT
AS
$BODY$
select to_char($1, 'Mon DD YY HH24:MI');
$BODY$
LANFUAGE sql
IMMUTABLE;

ALTER TABLE memos ADD modified_stamp GENERATED ALWAYS AS (custom_to_char(modified)) STORED;

MySQLでは関数を作るまでもなく色々な機能が存在していたため、PostgreSQLの関数やトリガーなどにハードルを感じていたのですが、今回その構造を調べたことで少し理解が深まりました。
今回はご助力いただき、誠にありがとうございました。

Answer 1

生成列に関しては、IMMUTABLEで無い関数(to_char())は使用できません。
代替案としては、生成列ではないカラムに変更して、トリガーで更新する事です。
以下create table のリファレンスから引用

GENERATED ALWAYS AS ( generation_expr ) STORED
この句は列を生成列として作成します。 この列には書き込みできず、読むときには指定された式の結果が返されます。
キーワードSTOREDは列が書き込み時に計算されてディスクに格納されることをあらわすのに必要とされます。
生成式はそのテーブルの他の列を参照できますが、他の生成列は参照できません。 使われる全ての関数と演算子はIMMUTABLEでなければなりません。 他テーブルを参照することはできません。

ただ、IMMUTABLEであるラッパーを自作すればエラー回避はできるようです。
https://dba.stackexchange.com/questions/77272/why-isnt-to-char-immutable-and-how-can-i-work-around-it

Comments

[kanon_2155103]

sazi様、重ねがさね本当に恐縮です……。

MySQLとPostgreSQLでは思った以上に仕様が異なっているのですね。
IMMTABLEはじめ、PostgreSQLに関しては分からない概念だらけなので、色々なことを理解しながらいただいた回答と照らし合わせていく形になると思います。
かなり時間がかかる気がするのですが、結果を確認できましたらご報告いたします。

ひとまずですが、長くお付き合いいただいたこと、心よりお礼申し上げます。

[kanon_2155103]

> sazi様
お世話になっております。
いただきました回答を参考に、こちらの方法で無事にカラムの生成ができました。

CREATE FUNCTION
custom_to_char(timestamp)
RETUENS TEXT
AS
$BODY$
select to_char($1, 'Mon DD YY HH24:MI');
$BODY$
LANFUAGE sql
IMMUTABLE;

ALTER TABLE memos ADD modified_stamp GENERATED ALWAYS AS (custom_to_char(modified)) STORED;

ひとつめに挙げていただいたトリガーの作成には現在躓いている最中なのですが、関数の書式についてはおかげさまで苦手意識が小さくなりました。
トリガーを使う方法も、学習をを重ねていく中で理解を及ぼすことができるよう精進いたします。
この度は長らくお付き合いいただき、誠にありがとうございました。