PostgreSQLのCLIのクライアント psql を仕事で使うことはそれなりに（週1回以上）ありますが、毎回叩くコマンド等を私はすぐに忘れてしまいますので、ここで cheat sheet として残しておきたいと思います。
クエリ結果表示でpagerを使わないようにする
デフォルトでは、クエリ結果の見た目行数がある程度（画面行数？）よりも多い場合、less(more?)っぽいpagerが起動しますが、オフにしたいときは \pset pager 0 です。逆にオンにするときは最後の数字を 1 で。
タイムゾーンの設定
サーバのタイムゾーンではなく、クライアント側というか、いま接続しているセッションにおけるタイムゾーンの設定です。東京にするなら SET timezone TO 'Asia/Tokyo'; で。
設定後の確認は show timezone; とか SQL select now(); あたりで。
構築して既に本番稼働してるDBサーバのタイムゾーン設定を変えるのは大変ですからね😅
クエリ結果をCSVに保存する
方法が複数あります。
- SQLの
COPY (query) to 'filepath'...文は、PostgreSQLのサーバが稼働しているところのファイルとして保存されるものであるので、例えばAWSのRDSのような環境ではだいたい使えないでしょう。PostgreSQLのインストールしてるサーバに直接ssh等で接続して…という場合に使うはずです。 - psqlの
\copy (query) to 'filepath'...コマンドは、psqlが稼働している端末のローカルにファイルを保存します。マニュアルによれば、内部ではSQLのCOPY (query) TO STDOUT...を叩いているようです。 - psql起動時のオプションとして stdin とかからSQL
COPY (query) TO STDOUT...を食わせて、シェルのリダイレクトで保存する方法 : スクリプトの中に組み込むのならこれが一番簡単そうに見えますが、そもそもCSVを出力する業務を自動化するのなら、それはアプリケーションサーバの機能として実装してしまうので、結局この方法を使う機会は少なそう😅
私の仕事環境では大抵はAWSのRDS上のPosgreSQLとやりとりするので、\copy コマンドのお世話になります。
例
\copy (select * from table_hoge limit 100) to '/tmp/test.csv' WITH DELIMITER ',' CSV HEADER QUOTE '"';
