🎄teratailクリスマスプレゼントキャンペーン2024🎄』開催中!

\teratail特別グッズやAmazonギフトカード最大2,000円分が当たる!/

詳細はこちら
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

1回答

638閲覧

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

kanon_2155103

総合スコア7

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

0クリップ

投稿2023/12/03 15:39

編集2023/12/03 16:00

前提

お世話になります。

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

MySQL

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

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

PostgreSQLで試したコマンドとそのエラー

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

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

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

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

該当のテーブル

MySQLのmemosテーブルの構造(今まで使っていたほう)

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

PostgreSQLのmemosテーブルの構造(新しいカラムを追加したいほう)

1memo_app=# \d memos; 2 テーブル"public.memos" 3 列 | タイプ | 照合順序 | Null 値を許容 | デフォルト 4------------+-----------------------------+----------+---------------+----------------------------------- 5 id | integer | | not null | nextval('memos_id_seq'::regclass) 6 title | character varying(40) | | | 'Untitled'::character varying 7 content | character varying(10485760) | | | 8 created | timestamp without time zone | | not null | CURRENT_TIMESTAMP 9 modified | timestamp without time zone | | not null | CURRENT_TIMESTAMP 10 is_removed | boolean | | not null | false 11 is_locked | boolean | | not null | false 12インデックス: 13 "memos_pkey" PRIMARY KEY, btree (id) 14トリガー: 15 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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2023/12/04 01:10 編集

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

2023/12/04 15:52 編集

コメントありがとうございます! なるほど、関数などの定義が必要になってくるのですね……。 まだPostgreSQLに関する知識が浅いため、勉強しつついただいた回答を紐解いていこうと思います。ゆっくりになるとは思いますが、結果を確かめることができたらご報告いたします。 アプリに諸事情があるというより、PostgreSQLに移行した後もMySQLのテーブルと比較を続けたく、それにあたってMySQLにもPostgreSQLにも対応するプログラムを残していたいという理由です。 SELECTの際にTo_charを指定することになると、MySQLとの互換性が無くなってしまうので……。 個人的な事情で恐れ入ります。
退会済みユーザー

退会済みユーザー

2023/12/04 20:33

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

2023/12/05 03:34

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

退会済みユーザー

2023/12/05 04:36 編集

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

2023/12/05 05:11

なるほど、企業さんでもそうなんですね! 教えていただきありがとうございます。
退会済みユーザー

退会済みユーザー

2023/12/05 05:18

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

2023/12/05 09:04 編集

自作は自作です。就活にあたってポートフォリオに乗せたくて作っています。 凝ったフォーマットを使っているのは完全に個人の趣味ですね。 とはいえデザイン面には拘っているので、業務のものだと思われるのは嬉しいです。
退会済みユーザー

退会済みユーザー

2023/12/05 08:03

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

2023/12/05 08:36 編集

デザインの話は何かお疑いされていると思ったから持ち出しました。 自身が未熟で実務につかれている方のレベルに及んでいないのは当然承知の上です。 勿論お付き合いいただく方にはご不便を強いてしまっており、その点については恐縮の限りですが……。 だからこそ現在、こうしてプログラム制作の経験を積みながら勉強をしているのです。 また、私を採用するかどうかは、制作物を実際にご覧になった採用担当の方が判断されることです。 考えるまでもなく、私が自分の制作物を抱えてインターンやアルバイトの選考に申し込むこと自体はなんの問題も無いでしょう。
退会済みユーザー

退会済みユーザー

2023/12/05 08:38

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

2023/12/05 08:55 編集

失礼ですが、私に何を求めていらっしゃるのですか?なぜ私がそういう人間である前提で話を進めるのでしょうか。 そのお話を今この場でなさる意図を図りかねております。 本来ここはデータベースに関する質問の場であるはずです。それとも、この投稿にそれほどの問題がございましたか?
退会済みユーザー

退会済みユーザー

2023/12/05 08:51

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

2023/12/05 09:02 編集

そうでしたか。 質問の仕方に失礼があったのかと思いましたが、そうではないようで安心いたしました。 それでは、最初にいただいた回答を試せたらまたご報告いたします。
退会済みユーザー

退会済みユーザー

2023/12/05 09:22 編集

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

2023/12/07 15:24

> 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の関数やトリガーなどにハードルを感じていたのですが、今回その構造を調べたことで少し理解が深まりました。 今回はご助力いただき、誠にありがとうございました。
guest

回答1

0

ベストアンサー

生成列に関しては、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

投稿2023/12/04 00:43

編集2023/12/04 01:02
sazi

総合スコア25313

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kanon_2155103

2023/12/04 15:51

sazi様、重ねがさね本当に恐縮です……。 MySQLとPostgreSQLでは思った以上に仕様が異なっているのですね。 IMMTABLEはじめ、PostgreSQLに関しては分からない概念だらけなので、色々なことを理解しながらいただいた回答と照らし合わせていく形になると思います。 かなり時間がかかる気がするのですが、結果を確認できましたらご報告いたします。 ひとまずですが、長くお付き合いいただいたこと、心よりお礼申し上げます。
kanon_2155103

2023/12/07 15:22

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.36%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問