これは、PostgreSQL Advent Calendar 2020の19日目の記事となります。
昨日は@nuko_yokohamaさんのPostgreSQLと麺に関する記事でした。
PosgreSQLの柔軟な活用事例を紹介されており、PostgreSQLやラーメンに興味がある方はぜひ熟読してみてください。特にジロリアン必見？
（ところで週3ラーメンって、控えめなんだろうか？？？）
はじめに
今回は皆さんも毎日使っているであろうpsqlコマンドについて書いてみようと思います。
選んだ理由は単純で、自身の勉強目的です！（なので、他の方のように面白い記事にはなっていないです。）
PostgreSQL初心者の方に読んでいただけるとちょうど良いかと思います。
なぜ勉強？
psqlはメジャーバージョンアップごとに多くの機能追加や改善が入っています。
ただ、お恥ずかしいことに自分は普段から決まったオプションしか使用できておらず、psqlの浦島太郎状態です。
PostgreSQL 13でもリリースノートで8項目ほど上がっていますが、どれも確認できていない。。。。
PostgreSQLを使っているなら、psqlはちゃんと使いたい！
というわけで、この機会を利用して、オプションや提供されている機能についてさらってみようと思ったというわけです。
本記事の構成はコチラ
- 1. PostgreSQL 13での更新内容
-
2. psqlのオプション
- 2-1. サマリ（オプション） 個人的なおすすめをピックアップしたものです。
- 2-2. 実行例（オプション）
-
3. psqlのメタコマンド
- 3-1. サマリ（メタコマンド） 個人的なおすすめをピックアップしたものです。
- 3-2. 実行例（メタコマンド）
1. PostgreSQL 13での更新内容
まずはリリースノートから最近入った機能を確認してみます。
1-1. Add transaction status (%x) to psql's default prompts (Vik Fearing)
psqlのプロンプトでトランザクション状態がデフォルトで表示されるようになりました。
<database名>=<トランザクション状態>#という出力になります。
ちなみにPG12、PG13のPROMPTは以下のとおりです。(\echo :PROMPT1で確認できます。)
| PG12 | PG13 |
|---|---|
| %/%R%# | %/%R%x%# |
| トランザクション状態 | PG12 | PG13 |
|---|---|---|
| トランザクション開始前 | postgres=# | postgres=# |
| トランザクションブロック内 | postgres=# | postgres=*# |
| 失敗したトランザクション | postgres=# | postgres=!# |
以下は実行例です。
# 以前までの出力
postgres=# BEGIN;
BEGIN
postgres=# SELECT aaa;
ERROR: column "aaa" does not exist
LINE 1: SELECT aaa;
^
postgres=#
# PG13での出力
postgres=# BEGIN;
BEGIN
postgres=*# SELECT aaa;
ERROR: column "aaa" does not exist
LINE 1: SELECT aaa;
^
postgres=!#
こういうのは地味にありがたいですね。すごい嬉しいというものでは無いけど、見た目でわかりやすいのは素晴らしい。
1-2. Allow the secondary psql prompt to be blank but the same width as the primary prompt (Thomas Munro)
これはPROMPT2のインデントを%wによって、PROMPT1と合わせられるようになったというものです。
この設定をしておけば、SQLをコピペしたりする場合にも便利なので地味に嬉しい機能ですね。
ちなみにPROMPTは以下のものがあります。
| プロンプト | 説明 | デフォルトの表示 |
\set PROMPT2 '%w'実行後の表示 |
|---|---|---|---|
| PROMPT1 | SQL受付前（最初）の表示 | postgres=# | postgres=# |
| PROMPT2 | SQL入力中の表示 | postgres=# SELECT 'PROMPT1' postgres-# ,'PROMPT2'; |
postgres=# SELECT 'PROMPT1', 'PROMPT2'; |
上記以外にも、PROMPT3があり、そちらはCOPY FROM STDINコマンドで入力が必要な場合の表示となります。
よくSQLをコピペする機会は多いので、これは覚えておいたほうが良さそうな気がする。
早速使ってみたいという人は~/.psqlrcに以下を追記しましょう。
\set PROMPT2 '%w'
1-3. Allow psql's \g and \gx commands to change \pset output options for the duration of that single command (Tom Lane)
単一コマンド内でも、\g, \gxコマンドで/pset出力オプションを変更することができるようになった。（PG12以前のバージョンでやると当然エラーになります。）
postgres=# SELECT * FROM foo;
id | val
----+-----
1 | a
2 |
(2 rows)
postgres=# SELECT * FROM foo \g (null=NULL)
id | val
----+------
1 | a
2 | NULL
(2 rows)
出力を変更する場合、SQLごとの変更が必要だったケースはあまりないので、コレは使わないような気がする。
ちなみに、「;(セミコロン)」を付けたらダメなのでご注意を！
postgres=# SELECT * FROM foo \g (null=NULL);
\g: missing right parenthesis
1-4. Add psql commands to display operator classes and operator families (Sergey Cherkashin, Nikita Glukhov, Alexander Korotkov)
\dAc, \dAf, \dAo, \dApが新たに追加されたというもの。
1-5. Show table persistence in psql's \dt+ and related commands (David Fetter)
\dt+ 実行時にテーブルの永続性を表示するようになった。
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-----------+------+-------+----------+-------------+---------+-------------
pg_temp_3 | bar | table | postgres | temporary | 0 bytes |
public | baz | table | postgres | unlogged | 0 bytes |
public | foo | table | postgres | permanent | 16 kB |
(3 rows)
| Persistence | 説明 | 定義例 |
|---|---|---|
| temporary | 一時テーブル | CREATE TEMPORARY TABLE bar (id int); |
| unlogged | unloggedテーブル | CREATE UNLOGGED TABLE baz (id int); |
| permanent | 永続テーブル | CREATE TABLE foo (id int);` |
1-6. Improve output of psql's \d for TOAST tables (Justin Pryzby)
toastテーブルに対する\dの出力が改善したとのこと。
紐づくテーブルやインデックス情報も合わせて出力されるようになりました。
- PostgreSQL 13
postgres=# \d pg_toast.pg_toast_16401
TOAST table "pg_toast.pg_toast_16401"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
Owning table: "public.foo"
Indexes:
"pg_toast_16401_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
- PostgreSQL 12
postgres=# \d pg_toast.pg_toast_16384
TOAST table "pg_toast.pg_toast_16384"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
1-7. Fix redisplay after psql's \e command (Tom Lane)
実行中のコマンドの再確認後の表示が若干変更されました。
PG13からは\eを抜けた後に、以前に実行中のSQLを再表示してくれるようです。まぁ見やすくなった言える。
- PostgreSQL 13
postgres=# select now(),
postgres-# \e
postgres=# select now(),
postgres-#
- PostgreSQL 12
postgres=# select now(),
postgres-# \e
postgres-#
1-8. Add \warn command to psql (David Fetter)
\echoの標準エラー出力版が追加されたようです。
2. psqlのオプション
psqlのオプションはPostgreSQL 13時点で $\huge{35種類}$ あります。結構ありますね。。。
マニュアルは コチラ（English）か コチラ（日本語 PG12）
ここからマニュアルに沿って紹介していくので、「細かい話はいらん。」というかたはサマリだけ見ていただければと思います。
サマリ（オプション）
個人的に使ってみようと思ったものや、psqlを業務やプライベートで使っているなら、押さえておいたほうが良いかもというものをピックアップしてみました。
| オプション | 概要 | 主な用途 | 備考 |
|---|---|---|---|
| -A | 位置揃えなしの出力 | 出力結果の整形 | |
| -c | 指定コマンド文字列の実行 | いろんな場面で利用可能 | 複数コマンドを実行する場合はそれぞれ-cで指定する |
| --csv | 出力をCSVモードに変更 | 出力結果の整形 | |
| -E | メタコマンドで実行されるSQLを出力 | PostgreSQLの勉強 | |
| -f | ファイルによるコマンド実行 | いろんな場面で利用可能 | 1コマンドで複数指定可能。 |
| -F | セパレータの指定 | 出力結果の整形 | |
| -L | 結果のファイル出力 | 情報共有等 | 実行したSQL自体もログとして記録するのが-oとは違う |
| -P | 表示オプション指定 | いろんな場面で利用可能 | nullを表示させたい場合とかに使える |
| -t | タプルのみの出力 | 出力結果の整形 | |
| -1 | 複数コマンドの1トランザクション化 | いろんな場面で利用可能 | 複数の-cを1つのトランザクションとして扱える |
実行例（オプション）
最後のSTATEMENTのログが標準エラー出力に出力されるようになった。 複数のコマンドを書く場合は オプションなしの引数もdbnameとして扱われる。 接続先のサーバを指定する。 ポート番号を指定する。 デフォルトはOSユーザ名で接続するので、任意のユーザ名で指定するときに使用する。 パスワードが設定されている場合、.pgpass等でパスワードが設定されていないと接続に失敗する。 起動用の 複数のコマンドを実行する際に、それを一つのトランザクションとして扱う。 オプションとコマンドと変数を確認することができます。各オプションの実行例なのでとても長いです。興味がある方はぜひクリックしてみてください。
-a (--echo-all) 入力を標準出力へ出力する
$ psql postgres -a -c 'SELECT * FROM foo'
SELECT * FROM foo
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
(3 rows)
-A 位置揃えなしでの出力
$ psql postgres -A -c 'SELECT * FROM foo'
id|time
1|2020-12-14
2|2020-12-17
3|2020-12-24
(3 rows)
-b (--echo-errors) 失敗したSQLを標準エラー出力に出力
$ psql postgres -b -c 'SELEC * FROM foo' > stdout.log 2>stderr.log
$ cat stdout.log
$ cat stderr.log
ERROR: syntax error at or near "SELEC"
LINE 1: SELEC * FROM foo
^
STATEMENT: SELEC * FROM foo ★ここが新たに出力された
-c (--command=command) 指定コマンド文字列の実行
$ psql postgres -c 'select now()'
now
-------------------------------
2020-12-15 00:02:01.174813+09
(1 row)
-cオプションを複数使用するかヒアドキュメントが推奨。$ psql postgres -c "\d" -c "SELECT * FROM foo"
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | foo | table | postgres
(1 row)
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
(3 rows)
$ psql postgres <<EOF
\d
SELECT * FROM foo;
EOF
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | foo | table | postgres
(1 row)
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
(3 rows)
--csv 出力をCSVモードに変更 （PG12で追加されたオプション）
$ psql postgres --csv -c "SELECT * FROM foo"
id,time
1,2020-12-14
2,2020-12-17
3,2020-12-24
-d (--dbname=dbname) DB名指定
$ psql -d postgres
psql (13.1)
Type "help" for help.
postgres=# ★オプションの有無で接続先のデータベースは変わらない
$ psql postgres
psql (13.1)
Type "help" for help.
postgres=# ★オプションの有無で接続先のデータベースは変わらない
-e (--echo-queries) SQLコマンドを標準出力へ表示
$ psql postgres -e -c 'SELECT now()'
SELECT now() ★実行したSQLコマンドが出力されるようになる
now
-------------------------------
2020-12-15 00:19:31.209496+09
(1 row)
-E (--echo-hidden) メタコマンドで実行されるSQLを表示
$ psql postgres -E -c '\d'
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | foo | table | postgres
(1 row)
-f (--file=filename) ファイルからのコマンド実行
$ echo "SELECT now()" > sample.sql
$ psql postgres -f sample.sql
now
-------------------------------
2020-12-15 00:30:21.299974+09
(1 row)
-f -と指定することで対話的に入力することも可能。ファイルからの入力と対話での入力を混在させるために使用するらしいが、readlineも使えないし、今後も使うことはなさそうだな。
-F (--field-separator=separator) セパレータの指定
$ psql postgres -F',' -A -c 'SELECT * FROM foo'
id,time
1,2020-12-14
2,2020-12-17
3,2020-12-24
(3 rows)
-h (--host=hostname) 接続先ホスト名の指定
-H (--html) 出力をHTMLモードに変更
$ psql postgres -H -c "SELECT * FROM foo"
<table border="1">
<tr>
<th align="center">id</th>
<th align="center">time</th>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">2020-12-14</td>
</tr>
<tr valign="top">
<td align="right">2</td>
<td align="left">2020-12-17</td>
</tr>
<tr valign="top">
<td align="right">3</td>
<td align="left">2020-12-24</td>
</tr>
</table>
<p>(3 rows)<br />
</p>
-l (--list) DB一覧表示
$ psql postgres -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
-L (--log-file=filename) 出力結果をファイルに出力
$ psql postgres -L result.log -c 'SELECT * FROM foo'
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
(3 rows)
$ cat result.log
********* QUERY **********
SELECT * FROM foo
**************************
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
(3 rows)
-n (--no-readline) Readlineの無効化
$ psql postgres
psql (13.1)
Type "help" for help.
postgres=# SELECT 'readline';
?column?
----------
readline
(1 row)
postgres=# \q
$ psql postgres -n
psql (13.1)
Type "help" for help.
postgres=# SELECT 'no-readline';
?column?
-------------
no-readline
(1 row)
postgres=# \q
$ cat .psql_history | tail -n 5
\echo :PROMPT1
\echo :PROMPT2
\q
SELECT 'readline';★以降で実行した情報はno-readlineなので
\q ★ historyにも残っていない。
-o (--output=filename) 実行結果をファイル出力
$ psql postgres -o output.log -c 'SELECT * FROM foo'
$ cat output.log
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
(3 rows)
-p (--port=port) ポート番号指定
-P (--pset=assignment) 表示オプション指定
$ psql postgres -P null=*NULL* -c 'SELECT * FROM foo'
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
4 | *NULL*
(4 rows)
-q (--quiet) メッセージ出力無効
$ psql postgres
psql (13.1) ★ここが
Type "help" for help. ★消える
postgres=# \q
$ psql postgres -q
postgres=#
-R (--record-separator=separator) レコード区切り文字の指定
$ psql postgres -R'*' -A -c 'SELECT * FROM foo'
id|time*1|2020-12-14*2|2020-12-17*3|2020-12-24*4|*(4 rows)
-s (--single-step) シングルステップモード有効
$ psql postgres -s -c 'SELECT * FROM foo'
***(Single step mode: verify command)*******************************************
SELECT * FROM foo
***(press return to proceed or enter x and return to cancel)********************
★そのままEnterで実行
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
4 |
(4 rows)
$ psql postgres -s -c 'SELECT * FROM foo'
***(Single step mode: verify command)*******************************************
SELECT * FROM foo
***(press return to proceed or enter x and return to cancel)********************
x ★コレでSQLの実行をキャンセルできる
-S (--single-line) シングル行モード有効
$ psql postgres -S
psql (13.1)
Type "help" for help.
postgres^# select now() ★セミコロンなしで実行可能！
now
-------------------------------
2020-12-16 22:59:32.738366+09
(1 row)
-t (--tuples-only) カラム名等の出力無効
$ psql postgres -t -A -c 'SELECT * FROM foo'
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
-T (--table-attr=table_options) HTMLのtableタグのオプション指定
$ psql postgres -H -T width=200 -c "SELECT * FROM foo"
<table border="1" width=200>
<tr>
<th align="center">id</th>
<th align="center">time</th>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">2020-12-14</td>
</tr>
<tr valign="top">
<td align="right">2</td>
<td align="left">2020-12-17</td>
</tr>
<tr valign="top">
<td align="right">3</td>
<td align="left">2020-12-24</td>
</tr>
<tr valign="top">
<td align="right">4</td>
<td align="left"> </td>
</tr>
</table>
<p>(4 rows)<br />
</p>
-U (--username=username) 接続ユーザ名を指定
-v (--set=assignment) (--variable=assignment) 変数の設定
$ psql postgres -v TARGET=foo
psql (13.1)
Type "help" for help.
postgres=# SELECT * FROM :TARGET; ★変数が設定された状態になっている。
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
4 |
(4 rows)
-V (--version) バージョン情報を出力
$ psql -V
psql (PostgreSQL) 13.1
-w (--no-password) パスワード入力無効
-W (--password) パスワード入力強制
$ psql postgres -W
Password: ★必ずパスワード入力を求められる
psql (13.1)
Type "help" for help.
postgres=#
-x (--expanded) 拡張テーブル形式出力を有効
$ psql postgres -x -c "SELECT * FROM foo"
-[ RECORD 1 ]----
id | 1
time | 2020-12-14
-[ RECORD 2 ]----
id | 2
time | 2020-12-17
-[ RECORD 3 ]----
id | 3
time | 2020-12-24
-[ RECORD 4 ]----
id | 4
time |
-x (--no-psqlrc) 起動用ファイルを使用しない
~/.psqlrcを読み込まず起動する。
-z (--field-separator-zero) 区切り文字をゼロバイト化
$ psql postgres -z -A -c 'SELECT * FROM foo'
idtime
12020-12-14
22020-12-17
32020-12-24
(3 rows)
-O ハイフンゼロ (--record-separator-zero) 区切り文字をゼロバイト化
$ psql postgres -0 -A -c 'SELECT * FROM foo'
id|time1|2020-12-142|2020-12-173|2020-12-24(3 rows)
-1 (--single-transaction) 明示的トランザクション発行
$ psql postgres -1 -c "DELETE FROM foo WHERE id = 4" -c "INSERT INTO fuu VALUES (5,now()::date)"
DELETE 1
ERROR: relation "fuu" does not exist
LINE 1: INSERT INTO fuu VALUES (5,now()::date)
^
$ psql postgres -c "SELECT * FROM foo"
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24
4 | ★-1オプションにより、ROLLBACKされている
(4 rows)
$ psql postgres -c "DELETE FROM foo WHERE id = 4" -c "INSERT INTO fuu VALUES (5,now()::dat
e)"
DELETE 1
ERROR: relation "fuu" does not exist
LINE 1: INSERT INTO fuu VALUES (5,now()::date)
^
$ psql postgres -c "SELECT * FROM foo"
id | time
----+------------
1 | 2020-12-14
2 | 2020-12-17
3 | 2020-12-24 ★オプションがない場合、各SQLは別トランザクションとなるのでDELETEはROLLBACKされない
(3 rows)
-? (--help[=topic])
$ psql --help=options
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
・・・（以下略）
$ psql --help=commands
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
・・・（以下略）
$ psql --help=variables
List of specially treated variables
psql variables:
Usage:
psql --set=NAME=VALUE
or \set NAME VALUE inside psql
AUTOCOMMIT
if set, successful SQL commands are automatically committed
COMP_KEYWORD_CASE
determines the case used to complete SQL key words
・・・（以下略）
3. psqlのメタコマンド
皆様御存知のとおり、psqlではバックスラッシュから始まるコマンドで色々な情報を取得できるメタコマンドというものが存在します。
PostgreSQL 13で利用できるメタコマンドは、その数なんと
$\huge{106個}$
もあります！！
サマリ（メタコマンド）
サマリとして、覚えていたほうが良さそうなものや、今後ちゃんと使いたいと思うものをピックアップしてみました。
| メタコマンド | 概要 | 主な用途 | 備考 |
|---|---|---|---|
| \c (\connect) | DBサーバへの新規接続 | クライアントからの複数DB操作 | 手元で触るときはあまり使わないけど、そういう環境での手順書を書くなら使ってもいいかも。 |
| \copy | フロントエンドコピー | 検証とか | COPY文がサーバサイドでの処理であることに対してこちらはフロントエンドサイドでの処理 |
| \crosstab | クロス表形式で表示に変更 | リバーシとかお絵かき | 遊び始めたら止まらない。。。ぜひ、一度触ってみてほしい。 |
| \e(\edit) | ファイルの編集 | 検証とか | ファイルも編集できるし、問合せバッファ内もエディタで編集できる。 |
| \gexec | 問合せ出力をSQLとして実行 | 色々な場面で利用できる | publicスキーマ内の全テーブルのTRUNCATEとかも書ける |
| \if \elif \else \endif | 制御構文 | 色々な場面で利用できる | psqlプログラミングを見て勉強しましょう。 |
| \timing | SQLに掛かった時間を出力 | 検証や解析とか | ざっくりの処理時間を見るならコレで十分見える |
| \watch | 問合せバッファの繰り返し | 検証や解析とか | 変化とかを見たりするのにも使える |
| \! | シェルの実行 | 色々な場面で利用できる | 実質何でもできるのでとても便利 |
実行例（メタコマンド）
もちろん、hostを指定することでリモートへの接続も可能。 COPY文とは似ているようで違うので初めて使用する人は気をつけて。 これはやらなくてもいいかな。 これは遊び始めると一気に時間が溶ける。。。 これはあかん。癖になる。。。ほとんどマリオのピクロス状態。 マインスイーパー作れる気がしてきた。。。来年のテーマかな？ 以下のものを始め、40種類ほどあります。 ファイルを指定した場合はそのファイルを編集できるし、問合せバッファの内容もエディタで編集できる。 こんな感じで前回実行したSQLを編集することができます。で、編集を終えたら実行されます。 複数のコマンドを実行する場合の目印に使える。 うーん。便利っちゃ便利かもだけど、あんまり使うことがないような気もする。 これを編集する。 エディタでの編集を終える。 例えば、publicスキーマの全テーブルをTRUNCATEしたい場合は ちなみに 自分から解説する内容は特にありません。 @noborus さんのpsqlプログラミングを見て勉強しましょう。 サンプルでは時間の表示なので、おもしろくない結果ですが、監視用のSQLを仕込んで使うこともできます。 以下のように、シェルを実行できるのでpsqlに入りながら、OS側の操作も可能だったりします。ピックアップして紹介します。サンプルも載せているので、気になる人はクリックして開いてみてください。
\a 出力形式の変更
postgres=# \a
Output format is unaligned.
postgres=# select now();
now
2020-12-18 20:29:02.216612+09
(1 row)
postgres=# \a
Output format is aligned.
postgres=# select now();
now
-------------------------------
2020-12-18 20:30:53.230839+09
(1 row)
\c (\connect) DBサーバへの新規接続
$ psql postgres
psql (13.1)
Type "help" for help.
postgres=# \c testdb ikki
You are now connected to database "testdb" as user "ikki".
testdb=#
psqlから出ずに色々できるということだな。
\C タイトル変更
postgres=# \C 'test title'
Title is "test title".
postgres=# select now();
test title ★ここの部分が任意で指定できる
now
-------------------------------
2020-12-18 20:42:16.125671+09
(1 row)
\cd 作業ディレクトリ変更
postgres=# \! pwd
/var/lib/pgsql
postgres=# \cd /tmp
postgres=# \! pwd
/tmp
\conninfo
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
\copy
\copyだとクライアントとサーバ間で通信が発生するので、データロードのときはサーバサイド（COPY文）のほうが良いよ。postgres=# \copy foo TO /tmp/foo.dump;
COPY 3
postgres=# \! cat /tmp/foo.dump
1 2020-12-14
2 2020-12-17
3 2020-12-24
\copyright 著作権および配布条項
\crosstabview クロス表形式で出力
CREATE TABLE sample(x int,y int,val text);
INSERT INTO sample SELECT 1,i,'' FROM generate_series(1,20) i;
INSERT INTO sample VALUES
(2,3,'■'),(2,4,'■'),(2,6,'■'),(2,11,'■'),(2,15,'■'),(2,16,'■'),(2,19,'■'),(2,20,'■'),
(3,2,'■'),(3,6,'■'),(3,8,'■'),(3,10,'■'),(3,12,'■'),(3,14,'■'),(3,18,'■'),
(4,2,'■'),(4,6,'■'),(4,7,'■'),(4,10,'■'),(4,12,'■'),(4,15,'■'),(4,19,'■'),
(5,2,'■'),(5,6,'■'),(5,10,'■'),(5,12,'■'),(5,16,'■'),(5,20,'■'),
(6,3,'■'),(6,4,'■'),(6,6,'■'),(6,11,'■'),(6,14,'■'),(6,15,'■'),(6,18,'■'),(6,19,'■')
;
postgres=# \crosstabview 1 2 3
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20
---+---+---+---+---+---+---+---+---+---+----+----+----+----+----+----+----+----+----+----+----
1 | | | | | | | | | | | | | | | | | | | |
2 | | | ■ | ■ | | ■ | | | | | ■ | | | | ■ | ■ | | | ■ | ■
3 | | ■ | | | | ■ | | ■ | | ■ | | ■ | | ■ | | | | ■ | |
4 | | ■ | | | | ■ | ■ | | | ■ | | ■ | | | ■ | | | | ■ |
5 | | ■ | | | | ■ | | | | ■ | | ■ | | | | ■ | | | | ■
6 | | | ■ | ■ | | ■ | | | | | ■ | | | ■ | ■ | | | ■ | ■ |
(6 rows)
\d[S+] オブジェクトの表示
\e (\edit) ファイル編集
\echo 標準出力
個人的には SELECT '1st----------';で事足りているからあんまり使わないかも。。。\echo 1st ------------
select now();
\echo 2nd ------------
select generate_series(1,5);
\echo end ------------
$ psql postgres -A -t -f /tmp/test.sql
1st ------------
2020-12-18 22:39:50.743402+09
2nd ------------
1
2
3
4
5
end ------------
\ef 関数やプロシージャの変数
関数はSQLファイルで作成しているケースがほとんどだろうし、直接エディタで書き換える機会はなさそう。postgres=# CREATE OR REPLACE FUNCTION reiwa(integer)
RETURNS TEXT AS $$
SELECT $1 - 2018;
$$ LANGUAGE sql;
CREATE FUNCTION
postgres=# select reiwa(2020);
reiwa
-------
2
(1 row)
postgres=# \ef reiwa(integer)
CREATE OR REPLACE FUNCTION public.reiwa(integer)
RETURNS text
LANGUAGE sql
AS $function$
SELECT '令和 ' || $1 - 2018 || '年';
$function$
postgres=# select reiwa(2020);
reiwa
----------
令和 2年
(1 row)
\gexec 問合せバッファの出力をSQL文として実行
postgres=# SELECT format('TRUNCATE %I', relname)
FROM pg_namespace n JOIN pg_class c ON (c.relnamespace = n.oid)
WHERE n.nspname = 'public' AND c.relkind = 'r'
\gexec
\gexecで実行されているの以下のようなSQLでした。postgres=# SELECT format('TRUNCATE %I', relname)
FROM pg_namespace n JOIN pg_class c ON (c.relnamespace = n.oid)
WHERE n.nspname = 'public' AND c.relkind = 'r'
;
format
---------------------------
TRUNCATE bar
TRUNCATE sample
TRUNCATE foo
TRUNCATE pgbench_accounts
TRUNCATE pgbench_branches
TRUNCATE pgbench_tellers
TRUNCATE pgbench_history
(7 rows)
\if \elif \else \endif 制御構文
\o 結果のファイル出力またはスクリプトへのパイプ
\o実行後の結果をすべてファイルに出力することができる。postgres=# \o /tmp/result.log
postgres=# \d
postgres=# SELECT now();
postgres=# \q
[postgres@localhost ~]$ cat /tmp/result.log
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | bar | table | postgres
public | foo | table | postgres
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
public | sample | table | postgres
(7 rows)
now
-------------------------------
2020-12-18 23:25:21.391783+09
(1 row)
\timing SQLに掛かった時間を表示
postgres=# \timing
Timing is on.
postgres=# select now();
now
-----------------------------
2020-12-18 23:51:16.1351+09
(1 row)
Time: 0.526 ms ★ここに掛かった時間が出力される
\watch 問合せバッファの繰り返し
他にも検証や動作確認で一定の更新を掛け続けたいとき等にも利用できるので覚えておくと便利かと思います。postgres=# select now();
2020-12-18 23:52:20.829818+09
postgres=# \watch
2020-12-18 23:52:26.36411+09
2020-12-18 23:52:28.367452+09
2020-12-18 23:52:30.370257+09
2020-12-18 23:52:32.371365+09
2020-12-18 23:52:34.374732+09
^Cpostgres=#
! シェルの実行
postgres=# \! pwd
/var/lib/pgsql
postgres=# \! date
Fri Dec 18 23:55:10 JST 2020
postgres=# \! whoami
postgres
さいごに
どうですか？皆さんはどれくらい普段からオプションやメタコマンドを使っていたでしょうか？
この記事のために夜な夜な psql で遊んでいましたが、思っていた以上にオプションやメタコマンドが多くて焦りました。
そして、普段使っているオプションやメタコマンドがほんの一部だったので、今回の記事を書く中でだいぶ勉強になりました。
あこがれの psqlマスターになりたいな。ならなくちゃ。絶対なってやる！！
の精神で2021年を迎えたいと思います。
さて、明日の記事は @kingtomo1122 さんです。
今年はどんな記事なのか楽しみですね！