【DB移行】 Oracle→PostgreSQL移行によるカラムの型変更

はじめに

PHP(Laravel)で実装したアプリケーションにおいて、OracleからPostgreSQLのデータベース移行案件に携わりました。
SQLの文法が異なるのは勿論ですが、データベースの移行時に思わぬところでカラムの型が変わってしまう箇所がありました。
ソースコードにできるだけ影響がないようにするために、PostgreSQLのカラムの型を適切に変更する必要があります。

環境

• Laravel 6.5
• Oracle 11g
• PostgreSQL 13.3

char型のカラムをboolean型へ変更する

元々boolean型だったカラムが、移行によりchar型に変わってしまったケースです。
データが入っているテーブルでは、単純にデータ型を変えるだけではうまくcastができずにエラーが出てしまいます。
また、カラムによっては、default制約やnot null制約がついている場合もあり、そういったカラムについてはあらかじめ制約を外しておかないと、カラムのデータのキャスト時にエラーが発生します。

制約を外す

対象のカラムに制約がついている場合、データのキャストのために外しておきます。

-- not null制約を外す
alter table <table> alter column <column> drop not null;
-- default制約を外す
alter table <table> alter column <column> drop default;

値のキャストと型の変更

PostgreSQLでは、using句を使用して、キャストとデータ型の変更を行います。

-- 型の変更
alter table <table> alter column <column> type boolean
	using case
		when <column> = '1' then true
		else false
	end;

case式を使用して、文字列の'1'をtrueに、'0'をfalseに変更します。

制約を付与する

外した制約を再度付与します。

-- default値の設定
alter table <table> alter column <column> set default false;
-- not null制約をつける
alter table <table> alter column <column> set not null;

text型のカラムをjson型へ変更する

元々text型だったカラムが、移行によりjso型に変わってしまったケースです。

制約を外す

値のキャストのために制約を外しておきます。

-- default制約を外す
alter table <table> alter column <column> drop default;

値のキャストと型の変更

using句を使用して、キャストとデータ型の変更を行います。

-- 型の変更
alter table <table> alter column <column> type json using to_json(<column>);

to_json を使用して、columnの値をjsonにエンコードしたものでキャストします。

制約を付与する

外した制約を再度付与します。

alter table <table> alter column <column> set default '[]'::json;

LaravelでtoArray()メソッドを使うとTrailin data エラーが出る

LaravelではEloquentやSQLなどで吐き出された長いオブジェクトを連想配列に整形してくれるtoArray()メソッドですが、Trailin dataというエラーが出ることがあります。
このエラーは、selectしたカラムの中に、小数点以下の秒、つまり、マイクロ秒単位で値を持つtimestamp型のデータがあると、表示されてしまいます。
Oralceでは問題なくtoArray()メソッドでtimestamp型のデータをselectできていても、PostgreSQL
に移行すると該当の箇所でTrailin dataが表示されることがあります。
その原因は、PostgreSQLのtimestamp型はデフォルトでマイクロ秒単位でデータを保持するtimestamp型であるためです。
データ型の移行がうまくいっていないことにより、上記のケースが起こり得ます。

データ型の変更

PostgreSQLでは、秒単位で（マイクロ秒単位は保持しない）データを持つ型として、timestamp(0)型とがあるので、こちらに型変換します。

alter table <table> alter column <column> type timestamp(0) using <column>::timestamp(0);

最後に

エラーが出た際に、「型が変わっている」というのは中々気が付きにくい箇所だなと、障害対応をしていて感じました。
今後のためにも、頭の片隅に入れておきたいです。