Computer広告を含む記事です

PostgreSQLでCSVファイルをインポート/エクスポートする

PostgreSQLでは、COPYコマンドでCSVファイルのインポートとエクスポートができます。COPYコマンドは、COPYと\copyの2種類あります。この2種類のコマンドの違いは、サーバー側とクライアント側、どちらに置いてあるファイルをインポート（エクスポート）するか、の違いです。

関連記事WindowsのpsqlでUTF8が文字化けする問題に対処する

COPYと\copy

2つのコマンドはわかりやすいように大文字と小文字に分けていますが、どちらのコマンドも大文字でも小文字でも使えます。copyでも\COPYでも大丈夫です。データベースサーバーはDebian、クライアントはWindows10の場合で説明していきます。

コマンドファイルの場所
COPYサーバー
\copyクライアント

SSHやpsqlでデータベースに接続し、サーバー側（今回は、Debian）に置いてあるCSVファイルをインポートする場合は、COPYコマンドを使います。

psqlでデータベースに接続し、クライアント側（今回は、Windows)に置いてあるCSVファイルをインポートする場合は、\copyコマンドを使います。

注意しておきたいのは、SSHでサーバーに接続しデータベースを操作する場合は、クライアント側（Windows）に置いてあるファイルをインポートすることはできない、ということです。

SSH接続では、サーバーのPostgreSQLを直接操作しているので、Windows側はクライアントではない、ということなのでしょうか。Linux初心者なので、この辺のイメージがつかみにくく、何故\copyコマンドでクライアント側のファイルが使えないのか、分からなくて少し苦労しました。

CSVファイルのインポート

COPY
COPY テーブル名 FROM 'CSVファイルの絶対PATH' WITH csv;
  • 絶対PATHは、サーバー側のCSVファイルを指定
\copy
\copy テーブル名 FROM 'CSVファイルの絶対PATH' WITH csv

絶対PATHは、クライアント側のCSVファイルを指定

headerを削除　エンコードがShift-JIS　の場合

CSVファイルにヘッダーが付いている場合は、headerオプションを付けることで、ヘッダーを読み込まないようにできます。

エンコードを指定する場合は、encodingオプションでエンコードを指定します。

COPY
COPY テーブル名 FROM 'CSVファイルの絶対PATH' WITH csv header encoding 'sjis';
  • 絶対PATHは、サーバー側のCSVファイルを指定
\copy
\copy テーブル名 FROM 'CSVファイルの絶対PATH' WITH csv header encoding 'sjis'
  • 絶対PATHは、クライアント側のCSVファイルを指定

使用例（インポート）

WindowsのWSLで入力する場合は、こんな感じです。

ファイルの場所ファイルの絶対PATHファイルのエンコード
サーバー/home/dattesar/data01-utf8.csvUTF-8
クライアント/mnt/d/data02-sjis.csvShift-JIS
COPY test_table FROM '/home/dattesar/data01-utf8.csv' WITH csv header;

\copy test_table FROM '/mnt/d/data02-sjis.csv' WITH csv header encoding 'sjis'

CSVファイルのエクスポート

COPY
COPY テーブル名 TO 'ファイル名（絶対PATH）' WITH csv;
  • 絶対PATHは、サーバー側を指定
  • 出力先のディレクトリには、書込できる権限（パーミッション）が必要
\copy
\copy テーブル名 TO 'ファイル名（絶対PATH）' WITH csv
  • 絶対PATHは、クライアント側を指定
  • 出力先のディレクトリには、書込できる権限（パーミッション）が必要

headerを追加　エンコードをShift-JISにする場合

COPY
COPY テーブル名 TO 'ファイル名（絶対PATH）' WITH csv header encoding 'sjis';
  • 絶対PATHは、サーバー側を指定
  • 出力先のディレクトリには、書込できる権限（パーミッション）が必要
\copy
\copy テーブル名 TO 'ファイル名（絶対PATH）' WITH csv header encoding 'sjis'
  • 絶対PATHは、クライアント側を指定
  • 出力先のディレクトリには、書込できる権限（パーミッション）が必要

使用例（エクスポート）

COPY test_table TO '/home/dattesar/copydata01-utf8.csv' WITH csv header encoding 'utf8';

\copy test_table TO '/mnt/d/copydata02-sjis.csv' WITH csv header encoding 'sjis'

SSH接続では少し注意

よく分からないのですが、SSH接続や、サーバーを直接操作している場合は、\copyでもCOPYと同じように使えます。（クライアント側のファイルは扱えません。）ただ、自分でサーバーかクライアント、どちらのファイルを扱っているのか混乱しやすくなるので、

  • サーバー側のファイルを扱う時はCOPY
  • クライアント側のファイルを扱う時は、\copy

に統一して使っています。

関連記事小型PCのHeroBoxにLinux(Debian)をインストールする