コマンドラインからPostgreSQLを操作する【psql】

概要

PostgreSQLにコマンドラインでSQLクエリを実行したりデータベースの操作を行うには、psqlコマンドを使用します。
psqlはターミナル型フロントエンドで、CLI（コマンドラインインターフェース）で対話的に問い合わせを入力し結果を確認することができます。

以下の環境で確認しています。

OS	Windows 10 64bit
バージョン	PostgreSQL 16.4

psqlコマンドの構文は以下の通りです。

psql [オプション...] [データベース名 [ユーザ名]]

主なオプションには以下のものがあります。

オプション	内容
-d dbname –database=dbname	接続するデータベース名を指定します。オプション以外の最初の引数として指定する場合と同じです。
-f filename –file=filename	標準入力からではなく、ファイルfilenameからコマンドを読み込みます。
-h hostname –host=hostname	サーバーを実行しているマシンのホスト名（IPアドレス）を指定します。
-L filename –log-file=filename	すべての問い合わせの出力を、標準の出力先とファイルfilenameに出力します。
-o filename –output=filename	すべての問い合わせの出力を、ファイルfilenameへ出力します。
-p port –port=port	サーバが監視を行っているTCPポートを指定します。
-U username –username=username	接続するユーザを指定します。
-V –version	psqlのバージョンを表示し、終了します。
-? –help[=topic]	ヘルプを表示し、終了します。

psqlで入力されるコマンドのうち、バックスラッシュで始まり引用符で囲まれていないものはpsql自身が実行するコマンドでメタコマンドと呼ばれます。
メタコマンドは、バックスラッシュ、コマンド本体、引数の順につなげた形になっており、コマンドと各引数間の間は空白となります。

主なメタコマンドは以下の通りです。

メタコマンド	内容
\cd [directory]	現在の作業ディレクトリをdirectoryに変更します。
\connect [dbname [username]]	新しいデータベースに指定ユーザ名で接続します。
\copy table [….]	フロントエンドコピーを行います。サーバ上の指定ファイルの読み書きではなく、psqlがサーバとローカルファイル間との読み書きを行います。
\d [pattern]	patternに一致するリレーション（テーブル、ビュー等）について、列や型の情報を表示します。
\du [pattern]	データベースロールの一覧を表示します。
\h [command] \help [command]	指定したSQLコマンドの構文に関するヘルプを表示します。
\o [{filename \| command}]	以降の問い合わせの結果をfilenameで指定したファイルに保存するか、シェルに渡しcommandを実行します。
\pset parameter [value]	問い合わせ結果のテーブル出力に影響するオプションを設定します。
\q	psqlを終了します。
\?	バックスラッシュ(“\”)コマンドに関するヘルプを表示します。

psqlコマンドの詳細については、公式ドキュメントを参照してください。

psqlコマンドのオプションやメタコマンドの使用例をいくつかあげてみます。

データベース「testdb1」にユーザ「testuser1」で接続する場合の記述例です。

psql -U testuser1 testdb1

実行すると以下のようになります。

問い合わせ結果などをファイル出力するには、「-L」オプションでファイル名を指定します。
以下の例はファイル名「test.log」に問い合わせ結果を出力します。

psql -L test.log -U testuser1 testdb1

実行すると以下のようになります。

問い合わせ結果が標準出力と、「-L」オプションで指定したファイルに書き込まれます。

問い合わせ結果をファイルに出力するには「-o」オプションでファイル名を指定します。
以下の例ではファイル名「test.txt」に問い合わせ結果を出力します。

psql -o test.txt -U testuser1 testdb1

実行すると以下のようになります。

問い合わせ結果が「-o」オプションで指定したファイルに出力されます。「-L」と違い標準出力には出力されません。

また、メタコマンドの「\o」でも同様にファイル出力の指定が可能です。

\o test2.txt

psqlコマンドでデータベース接続後、「\o」コマンドを実行します。その後の問い合わせ結果が指定されたファイルに出力されています。

コマンドを標準入力ではなく指定したファイルから読み込んで実行するには「-f」オプションを使用しファイル名を指定します。
以下の例では、ファイル「test.sql」からコマンドを読み込んで実行します。

psql -f test.sql -U testuser1 testdb1

ファイル「test.sql」に「testtbl」の内容を取得するクエリ（select）を書いて置き、psqlに「-f」オプションで指定して実行した例です。

「-f」オプションで指定した「test.sql」を読み込み、ファイルに書かれた「select文」を実行し結果を表示しています。
実行が終わるとpsqlを終了します。

\copyコマンドを使用して、テーブルの内容をクライアントのローカルファイルに出力します。
下記の例ではテーブル「testtbl」のデータをCSVフォーマットでヘッダ有りの指定でファイル名「testtbl.csv」に出力します。

\copy testtbl to testtbl.csv with (format csv, header true) ;

以下は実行例です。

テーブル「testtbl」の内容がファイル名「testtbl.csv」にヘッダ付きで出力されています。

次は\copyコマンドを使用してローカルファイルからテーブルへデータを入力します。
下記の例ではフォーマットCSV、ヘッダ有りのファイル「testtbl.csv」を読み込み、テーブル「testtbl」に登録します。

\copy testtbl from testtbl.csv with (format csv, header true) ;

テーブル「testtbl」を空にしておき、前項で出力した「testtbl.csv」を読み込ませた実行例です。

ファイルの内容がテーブルに取り込まれています。

テーブルの一覧を表示するには、「\d」コマンドを使います。
また、引数としてテーブル名を指定するとテーブルの情報が表示されます。
以下の例では「testtbl」を指定しています。

\d
\d testtbl

実行すると以下のようになります。

psqlを終了するには、「\q」コマンドを実行します。

\q

実行例です。

以上でpsqlコマンドの使用例の説明を終わります。