PostgreSQLクエリ結果をCSV形式で出力：psqlコマンドのオプションを使いこなす
PostgreSQL のクエリ結果を CSV 形式でコマンドラインから出力する方法
COPY コマンドは、PostgreSQL からデータを抽出するための標準的な方法です。CSV 形式で出力するには、以下のようなオプションを指定します。
COPY table_name TO 'file_path' WITH CSV DELIMITER ',';
table_name: 出力するテーブル名file_path: 出力する CSV ファイルのパスCSV DELIMITER：CSV ファイルの区切り文字。カンマ (,) 以外にも指定できます。
例:
COPY users TO '/tmp/users.csv' WITH CSV DELIMITER ',';
このコマンドは、users テーブルのすべてのデータをカンマ区切りの CSV ファイル /tmp/users.csv に出力します。
オプション:
FORCE QUOTE *: すべての値を二重引用符で囲みます。NULL AS '': NULL 値を空文字 ('') に変換します。HEADER: 最初の行にカラム名を出力します。
詳細は、PostgreSQL のドキュメントを参照してください: https://www.postgresql.org/docs/current/sql-copy.html
psql コマンドの出力オプションを使用すると、クエリ結果を様々な形式で出力できます。CSV 形式で出力するには、以下のようなオプションを指定します。
psql -A -F ',' -c 'SELECT * FROM users' > '/tmp/users.csv'
-A: テーブル形式で出力しない-F ',': フィールド区切り文字をカンマ (,) に指定-c 'SELECT * FROM users': 実行する SQL クエリ
psql -A -F ',' -c 'SELECT * FROM users' > '/tmp/users.csv'
-h host: ホスト名-U username: ユーザ名-d database: データベース名
TSV 形式で出力する場合は、CSV DELIMITER または -F オプションでタブ (\t) を区切り文字として指定します。
補足
- 出力する前に、必要な権限を持っていることを確認してください。
- 上記は基本的な例です。状況に応じてオプションを調整する必要があります。
- より複雑なフォーマットで出力したい場合は、
UNLOADコマンドを使用することもできます。
PostgreSQLクエリ結果をCSV形式で出力するサンプルコード
COPYコマンドを使用する
-- PostgreSQLにログイン
psql -h localhost -U postgres -d mydb
-- テーブル"users"のすべてのデータをCSVファイル"/tmp/users.csv"に出力
COPY users TO '/tmp/users.csv' WITH CSV DELIMITER ',';
-- ログアウト
\q
このコードは、以下のことを行います。
psqlコマンドを使用して、ホスト名localhost、ユーザ名postgres、データベース名mydbで PostgreSQL にログインします。\qコマンドを使用して、PostgreSQL からログアウトします。
psqlコマンドの出力オプションを使用する
psql -h localhost -U postgres -d mydb -A -F ',' -c 'SELECT * FROM users' > '/tmp/users.csv'
-Aオプションを指定して、テーブル形式で出力を抑制します。-c 'SELECT * FROM users'オプションを指定して、usersテーブルのすべてのデータを選択する SQL クエリを実行します。>演算子を使用して、クエリ結果を CSV ファイル/tmp/users.csvに出力します。
この 2 つの例は、PostgreSQL クエリ結果を CSV 形式でコマンドラインから出力する方法を示す基本的な例です。状況に応じて、オプションやクエリを変更する必要があります。
その他の注意事項
関数を使用する
PostgreSQL には、クエリ結果を CSV 形式に変換するいくつかの組み込み関数があります。例えば、以下のような関数があります。
csv_export関数: クエリ結果を CSV 形式の文字列に変換します。
これらの関数は、より複雑なフォーマットの CSV ファイルを生成したり、クエリ結果を他の処理で使用したりする場合に役立ちます。
外部ライブラリを使用する
pgcsv: PostgreSQL から CSV 形式のデータを出力するためのライブラリpython-psycopg2: PostgreSQL とやり取りするための Python ライブラリ。CSV 形式へのデータ変換機能を含む
これらのライブラリを使用すると、より柔軟な制御と機能を利用することができます。
GUI ツールを使用する
pgAdmin: PostgreSQL を管理するためのグラフィカルツール。クエリ結果を CSV 形式でエクスポートする機能を含むDBeaver: 様々なデータベースを管理するためのグラフィカルツール。PostgreSQL のクエリ結果を CSV 形式でエクスポートする機能を含むSequel Pro: Mac 用の PostgreSQL クライアント。クエリ結果を CSV 形式でエクスポートする機能を含む
これらのツールを使用すると、コードを記述せずに CSV ファイルを簡単に生成することができます。
最適な方法の選択
使用する方法は、ニーズとスキルレベルによって異なります。
- シンプルで直感的な方法が必要な場合は、
COPYコマンドまたはpsqlコマンドの出力オプションを使用するのが良いでしょう。 - より柔軟な制御と機能が必要な場合は、外部ライブラリを使用するのが良いでしょう。
- コードを記述せずに CSV ファイルを簡単に生成したい場合は、GUI ツールを使用するのが良いでしょう。
postgresql
