※ この記事にはアフィリエイトリンクが含まれます
データベースの中のデータを外部のシステムで利用したり、Excelなどで分析するために、クエリ結果をCSV形式で出力するのはよく見かけると思います。
本記事では、PostgreSQLでSELECT文を使って取得したデータを、CSV形式で出力する下記の２つの方法について紹介します。
- コマンドラインツール psql を使用する方法
- SQLの COPY コマンドを利用する方法
方法１：psqlコマンドを使用してCSV出力する
psql は、SQLクエリを直接実行できるコマンドラインツールです。ここでは、psql の\COPY コマンドを使って、SELECT文の結果をCSV形式で出力する方法について解説します。
\COPYコマンドの構文
psql で使用する \COPY コマンドの基本的な構文です。
\COPY (SELECT * FROM テーブル名) TO 'output.csv' WITH CSV HEADER;上記構文では、SELECT句で取得したテーブルの全データをoutput.csv に出力しています。また、構文の後ろに WITH CSV HEADER オプションを付けることにより、CSVの最初の行にテーブルのカラム名を含めることができます。
psqlコマンドでの実行例
実際にpsqlコマンドを利用したCSVの出力方法を見ていきましょう。
まず、psqlにログインして、データベースに接続します。
psql -U username -d database_name-U の後ろにPostgreSQLのユーザ名を、 -d の後ろに接続するデータベース名を指定しましょう。
ログインした後、次のコマンドを実行して、テーブルからCSVファイルを作成します。
\COPY (SELECT * FROM employees) TO 'employees_data.csv' WITH CSV HEADER;ここでは、employees テーブルの全データをemployees_data.csv というファイル名でヘッダー付きCSVファイルを出力しています。
上記コマンドの中で出てきたオプションを紹介します。
- WITH CSV
出力形式をCSVとして指定します。デフォルトではカンマ（,）が区切り文字となります。 - HEADER
CSVファイルの最初にカラム名を出力します。これがない場合だと、データだけが出力されるような形になります。
区切り文字をカンマ以外にする方法
区切り文字をカンマ以外に指定することもできます。カンマ以外に指定するには DELIMITER というオプションを使用します。
例えば、区切り文字をセミコロンにしたい場合は、以下のように書きます。
\COPY (SELECT * FROM employees) TO 'employees_data.csv' WITH CSV HEADER DELIMITER ';';※注意点 実行前にパスを確認すること
コマンド内で、出力するファイルをファイル名だけ指定すると、カレントパスにファイルが出力されるので、実行前に、カレントパスを確認するようにしましょう。
保存するファイルパスを下記のように絶対パスで指定することも可能です。
\COPY (SELECT * FROM employees) TO '/home/user/output/employees_data_csv' WITH CSV HEADER DELIMITER ';';方法２：SQLクエリ内でCOPYコマンドを使用する
COPY コマンドを使えば、クエリ内でCSV出力する処理を書くこともできます。
COPYコマンドの構文
サーバ上でCOPY コマンドを使用する場合の基本的な構文は以下の通りです。
COPY (SELECT * FROM テーブル名) TO '/path/to/output.csv' WITH CSV HEADER;上記構文では、SELECT句で取得したテーブルの全データを/path/to/output.csv に出力しています。また、構文の後ろに WITH CSV HEADER オプションを付けることにより、CSVの最初の行にテーブルのカラム名を含めることができます。
COPYコマンドの実行例
employees テーブルからデータをCSVファイルに出力するには、以下のようにします。
COPY (SELECT * FROM employees) TO '/var/lib/postgresql/data/employees_data.csv' WITH CSV HEADER;このコマンドは、サーバ上の指定したパスにemployees_data.csv というファイルを作成し、その中にemployees テーブルの全データを含めています。
構文の後ろで使用しているオプションについては、基本的には\COPYコマンドの実行例で説明したものと同じものを使用することができます。
CSV出力時の注意点
PostgreSQLでCSV形式にデータを出力する際には、以下のいくつかの点に注意する必要があります。
文字コードに注意する
出力したCSVファイルの文字エンコーディングには注意が必要です。デフォルトでは、PostgreSQLではUTF-8を使用しますが、日本語のようなマルチバイト文字を含むデータの場合だと、適切なエンコーディングを指定する必要があります。特に、Windows環境でファイルやソフトを開く場合、UTF-8以外の文字コードで指定しないと文字化けしてしまうことがあります。
CSVファイルをShift-JIS（SJIS）形式で出力する例を見てみましょう。
COPY (SELECT * FROM テーブル名) TO '/path/to/output.csv' WITH CSV HEADER ENCODING 'SJIS';改行コードに注意する
Windows、MacOS、Linuxではそれぞれ改行コードが異なっているため、CSVファイルを別のOSで使用するような場合、互換性の問題が発生することがあります。わかりやすい例だとWindows環境では改行コードが\r\n 、Linuxでは \n です。この点を意識してファイルを処理する必要があります。
PostgreSQLでは改行コードがデフォルトでOSに依存するようになっているため、必要に応じてファイルを変換するツールやエディタを使用するとよいでしょう。
NULLの扱いに注意する
PostgreSQLでは、NULL値はデフォルトで空文字としてCSVに出力されます。これを指定の文字列で置き換えることもできます。例えば、NULLを明示的に出力したい場合には、以下のように書きます。
COPY (SELECT * FROM テーブル名) TO '/path/to/output.csv' WITH CSV HEADER NULL 'NULL';まとめ
本記事では、PostgreSQLを使用して、クエリの結果をCSV形式で出力する方法について、psql コマンドやSQLのCOPY コマンドを用いた手順を詳しく解説しました。データを外部のツールで活用させるために、CSV形式での出力は非常に有用です。
また、CSV出力時には、文字コードや改行コード、NULLの扱いなどに注意が必要です。
これらのポイントを踏まえ、適切な設定を行うことで、より正確なデータ出力が可能になると思います。
PostgreSQLを効率よく学びたい方はUdemyがおすすめ
もっとわかりやすくPostgreSQLを学びたいという方には、Udemyがおすすめです。Udemyは、オンライン学習を手軽に始められる便利なプラットフォームで、初心者から上級者向けまで、様々なレベルのSQLコースがそろっています。ですので自身のスキルや目的にあったコースを選ぶことができます。
また、Udemyはコミュニティのサポートもしており、質問や情報交換も行えるため、学習を進めている中で浮かんだ疑問などを解消する手助けを受けることもできます。
Udemyは定期的に90%OFFなどの規模でセールを開催しているので、そのタイミングを狙うのもありでしょう。今までにセールが開催された時期をまとめました。
- 季節ごとのセール
年末年始や夏季、春季などの特別な時期に大規模なセールを実施 - 祝日セール
バレンタインデー、感謝祭、クリスマスなどの祝日にもセールを実施
コメント