OracleのDATE型データをPostgreSQLのtimestamp型カラムに挿入

Oracle DBにあるデータをCSV出力し、PostgreSQLへCOPYで挿入する際、Oracle側のDate型データがPostgreSQLのtimestamp型カラムに正しく入りませんでした。

OracleのDATE型

標準的なOracleのDATEフォーマットは、DD-MON-YYです。

PostgreSQLのtimestamp型

一方PostgreSQLのtimestampフォーマットはyyyy-mm-dd HH:MM:SSです。

Oracle DATE型→PostgreSQL timestamp型

Oracle側のDATE型フォーマットをデフォルトの形式から変更してから出力し、PostgreSQLに取り込みます。上述したように、デフォルトの場合はDD-MON-YY形式であるため、PostgreSQLのtimestamp型に合うようにyyyy-mm-dd HH:MM:SSに変更します。変更はnls_date_formatパラメータに任意の形式を指定します。下記SQLによって、セッション内は指定した形式で出力されるようになります。

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH:MM:SS';

alter sessionの出力が邪魔

今回の場合、あらかじめ出力したい内容をSQLファイルにまとめて、shell経由でCSVを作成していました。従って、SQLファイル上に上記のalter session~を記述しCSV出力を実行しました。しかし出力したCSV内にデータと共に、「セッションが変更されました。」とalter session~の実行結果が入ってしまいました。
そこで下記のように ALTER session実行前後で出力の無効/有効を指定することで、出力したいSELECT結果だけを取得できます。

SET TERMOUT OFF
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH:MM:SS';
SET TERMOUT ON
SELECT * FROM ~