【SQL】既存のデータを壊さずに文字列から配列に型変換をする

データベースのテーブルで、すでに定義した文字列のカラムを配列に変更したい。

良いかは別としてやってみたのですが、型変換が少し面倒でした。

この記事では、文字列から配列に型変換する方法を説明します。

型変換がうまくいった方法

UPDATEでカラムの文字列を中括弧で囲むことで、ALTER TABLEにて型変換ができました。

UPDATE <テーブル名> SET <カラム名> = '{'||<カラム名>||'}';
ALTER TABLE <テーブル名> ALTER <カラム名> type varchar(10)[] using <カラム名>::varchar(10)[];

試したテーブル

こんな感じのテーブルを作って動作確認しました。

test=# \d test_table
                    Table "public.test_table"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          | 
 name   | character varying(10) |           |          |

idとnameだけのテーブルです。

データも適当に入れておきます。

test=# select * from test_table;
 id | name  
----+-------
  1 | name1
  2 | name2
  3 | name3
(3 rows)

このテーブルのカラムであるnameを配列にします。

具体的には、varchar(10)→varchar(10)[]に型変換を行います。

色々失敗した経緯

最初はALTER TABLEで型変換すればうまくいくと思っていました。

ALTER TABLE <テーブル名> ALTER <カラム名> TYPE varchar(10)[];

しかし、ALTER TABLEにてデータ型を変更するやり方では以下のエラーが表示されました。

test=# alter table test_table alter name type varchar(10)[];
ERROR:  column "name" cannot be cast automatically to type character varying[]
HINT:  You might need to specify "USING name::character varying(10)[]".

どうやら、自動キャストできないので明示的に型をキャストする必要があるようです。
言われた通りに”USING name::character varying(10)[]”を加えてみると…

test=# alter table test_table alter name type varchar(10)[] using name::varchar(10)[];
ERROR:  malformed array literal: "name1"
DETAIL:  Array value must start with "{" or dimension information.

Arrayは{value}みたいに”{“（中括弧）で囲まれている必要があるようです。
そこで、カラムの文字列全てに中括弧をつけて再施行しました。

test=# update test_table set name = '{'||name||'}';
UPDATE 3
test=# select * from test_table;
 id |  name   
----+---------
  1 | {name1}
  2 | {name2}
  3 | {name3}
(3 rows)

文字列連結の演算子||を使用して、中括弧で囲むようにUPDATEしました。
これで、配列への型変換ができるようになりました！

test=# alter table test_table alter name type varchar(10)[] using name::varchar(10)[];
ALTER TABLE
test=# \d test_table
                     Table "public.test_table"
 Column |          Type           | Collation | Nullable | Default 
--------+-------------------------+-----------+----------+---------
 id     | integer                 |           |          | 
 name   | character varying(10)[] |           |          |

試しにid=1の配列に文字列を追加してみます。

test=# update test_table SET name = array_append(name::varchar(10)[],'add'::varchar(10)) WHERE id = 1;
UPDATE 1
test=# select * from test_table;
 id |    name     
----+-------------
  2 | {name2}
  3 | {name3}
  1 | {name1,add}
(3 rows)

OKですね。

もっといい方法があるのかもしれませんが、以上です。