現在位置: ホーム / OSSブログ / psqlコマンドを便利に使う

psqlコマンドを便利に使う

PostgreSQLの利用/管理されている方は日常的にpsqlコマンドを利用していると思います。SQL分を実行するだけではなく、psqlには様々な機能があります。今回はpsqlコマンドを便利に利用するための方法を幾つか紹介します。
psqlコマンドを便利に使う

記事の執筆に利用している環境はLinux上のPostgreSQL 9.3、ターミナルはgnome terminalです。

psqlの設定

pgsqlはヘルプ(\?)コマンドでテーブル定義などを参照する\dコマンドがあります。これはヘルプコマンドで一覧できるので利用されている方も多いと思います。psqlには動作を設定するコマンドや設定ファイルを指定することもできます。これらを利用すると、psqlコマンドをより便利に利用できるようになります。

psqlの初期化ファイル

psqlは起動時にホームディレクトリの.psqlrcを読み込む機能があります。.psqlrcにはpsqlコマンドから実行/設定可能なコマンドを記載することができます。これにより自分の好みにあった出力や便利なコマンドを設定/登録可能です。

クエリの実行時間を表示する

\timing on

これを設定するとクエリ実行に必要な時間を毎回表示するようになります。

testusr=> select * from pg_stat_xact_all_tables ;
時間: 1.398 ms

インタラクティブにpsqlを利用する場合、常にクエリ実行時間を表示しておく方が便利だと思います。

接続中のデータベースを表示する

\conninfo

このコマンドは現在接続中のデータベース接続の情報をテキストとして出力します。

testusr=> \conninfo
データベース"testusr"にユーザ"testusr"でソケット"/var/run/postgresql"経由のポート"5432"で接続しています。

プロンプトにデータベース名を表示することも可能です。BASHなどと同じ要領で指定可能です。詳しくはマニュアル(http://www.postgresql.org/docs/9.3/static/app-psql.html#APP-PSQL-PROMPTING)を参照してください。

\set PROMPT1 '%n@%m %~%R%# '

"%~" がデータベース名を表示させる指定です。他に%/もありますが、%~はデフォルトデータベースに接続している場合はデータベース名が~になります。

testusr=> \set PROMPT1 '%n@%m %~%R%# '
testusr@[local] ~=> 

プロンプトに色を付ける

エスケープシークエンス%[, %]を利用してBASHなどと同様にプロンプトに色をつける事も可能です。

testusr@[local] ~=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
testusr@testusr=> 

(背景色が黒、文字色が黄色)

PROMPTにはPROMIT1からPROMIT3まであります。

PROMPT1 - 通常のプロンプト
PROMPT2 - 次の入力が期待される場合のプロンプト
PROMPT3 - COPYコマンドを実行中のプロンプト

ページャーの動作を変える

psqlコマンドでクエリを実行した場合、デフォルトでページャーを利用し、コラムが整列された形で出力されます。

デフォルトではテーブルはASCII文字を利用して整形されます。

 relid │     schemaname     │         relname         │ seq_scan │ seq_tup_read │ idx_scan │ idx_tup_fetch │ n_tup_ins │ n_tup_upd │ n_tup_del │ n_tup_hot_upd 
───────┼────────────────────┼─────────────────────────┼──────────┼──────────────┼──────────┼───────────────┼───────────┼───────────┼───────────┼───────────────
  1261 │ pg_catalog         │ pg_auth_members         │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  2617 │ pg_catalog         │ pg_operator             │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  2600 │ pg_catalog         │ pg_aggregate            │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  1136 │ pg_catalog         │ pg_pltemplate           │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
 12529 │ information_schema │ sql_implementation_info │        0 │            0 │          │               │         0 │         0 │         0 │             0
  2609 │ pg_catalog         │ pg_description          │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  2612 │ pg_catalog         │ pg_language             │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
 12539 │ information_schema │ sql_packages            │        0 │            0 │          │               │         0 │         0 │         0 │             0
  3601 │ pg_catalog         │ pg_ts_parser            │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  3466 │ pg_catalog         │ pg_event_trigger        │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  3592 │ pg_catalog         │ pg_shseclabel           │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  3118 │ pg_catalog         │ pg_foreign_table        │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0
  2604 │ pg_catalog         │ pg_attrdef              │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0

Unicode文字を利用して整形するとより見やすくなります。

  relid │     schemaname     │         relname         │ seq_scan │ seq_tup_read │ idx_scan │ idx_tup_fetch │ n_tup_ins │ n_tup_upd │ n_tup_del │ n_tup_hot_upd 
───────┼────────────────────┼─────────────────────────┼──────────┼──────────────┼──────────┼───────────────┼───────────┼───────────┼───────────┼───────────────
1261 │ pg_catalog │ pg_auth_members │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
2617 │ pg_catalog │ pg_operator │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
2600 │ pg_catalog │ pg_aggregate │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
1136 │ pg_catalog │ pg_pltemplate │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
12529 │ information_schema │ sql_implementation_info │ 0 │ 0 │ │ │ 0 │ 0 │ 0 │ 0
2609 │ pg_catalog │ pg_description │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
2612 │ pg_catalog │ pg_language │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
12539 │ information_schema │ sql_packages │ 0 │ 0 │ │ │ 0 │ 0 │ 0 │ 0
3601 │ pg_catalog │ pg_ts_parser │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
3466 │ pg_catalog │ pg_event_trigger │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
3592 │ pg_catalog │ pg_shseclabel │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
3118 │ pg_catalog │ pg_foreign_table │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0
2604 │ pg_catalog │ pg_attrdef │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0

ボーダーのスタイルを変更するとより、表らしい出力になります。

\pset border 2

実行結果

┌───────┬────────────────────┬─────────────────────────┬──────────┬──────────────┬──────────┬───────────────┬───────────┬───────────┬───────────┬───────────────┐
│ relid │     schemaname     │         relname         │ seq_scan │ seq_tup_read │ idx_scan │ idx_tup_fetch │ n_tup_ins │ n_tup_upd │ n_tup_del │ n_tup_hot_upd │
├───────┼────────────────────┼─────────────────────────┼──────────┼──────────────┼──────────┼───────────────┼───────────┼───────────┼───────────┼───────────────┤
│  1261 │ pg_catalog         │ pg_auth_members         │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  2617 │ pg_catalog         │ pg_operator             │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  2600 │ pg_catalog         │ pg_aggregate            │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  1136 │ pg_catalog         │ pg_pltemplate           │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│ 12529 │ information_schema │ sql_implementation_info │        0 │            0 │          │               │         0 │         0 │         0 │             0 │
│  2609 │ pg_catalog         │ pg_description          │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  2612 │ pg_catalog         │ pg_language             │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│ 12539 │ information_schema │ sql_packages            │        0 │            0 │          │               │         0 │         0 │         0 │             0 │
│  3601 │ pg_catalog         │ pg_ts_parser            │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  3466 │ pg_catalog         │ pg_event_trigger        │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  3592 │ pg_catalog         │ pg_shseclabel           │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  3118 │ pg_catalog         │ pg_foreign_table        │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  2604 │ pg_catalog         │ pg_attrdef              │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │

ページャー自体の動作を変える

psqlは外部ページャーを利用しています。外部ページャーへのオプションを指定すると、より便利に出力結果を参照できます。ここではlessコマンドをページャーにしている場合を例にあげます。

そもそもページャーを利用したくない場合は

\pset pager off

を設定します。ページャーを利用する場合には

\pset pager on

を実行します。筆者の環境でlessがページャーとして利用されていますが、画面右端で折り返す設定がデフォルトになっていあす。全てのコラムを見るには良いのですが、コラム数が多い場合、極端に読みづらくなります。

ページャーにlessを利用し、オプションに-S(右端で折り返さず、切り取る)を設定するには環境変数を利用します。

\setenv PAGER less
\setenv LESS -S

設定前のWebサイトで出力を表示することあまり適さないので、psqlコマンドで確かめてみてください。

クエリ

select * from pg_stats;

ページャー設定を行った後の出力は以下のように、見れる出力になっています。

結果

┌────────────────────┬─────────────────────────┬───────────────────────────────────┬───────────┬───────────┬───────────┬────────────┬───────────────────────────────────────────
│     schemaname     │        tablename        │              attname              │ inherited │ null_frac │ avg_width │ n_distinct │                                           
├────────────────────┼─────────────────────────┼───────────────────────────────────┼───────────┼───────────┼───────────┼────────────┼───────────────────────────────────────────
│ pg_catalog         │ pg_proc                 │ proname                           │ f         │         0 │        64 │  -0.822459 │ {max,min,overlaps,has_column_privilege,dat
│ pg_catalog         │ pg_proc                 │ pronamespace                      │ f         │         0 │         4 │          2 │ {11,12401}                                
│ pg_catalog         │ pg_proc                 │ proowner                          │ f         │         0 │         4 │          1 │ {10}                                      
│ pg_catalog         │ pg_proc                 │ prolang                           │ f         │         0 │         4 │          3 │ {12,13,14}                                
│ pg_catalog         │ pg_proc                 │ procost                           │ f         │         0 │         4 │          5 │ {1,100,10}                                
│ pg_catalog         │ pg_proc                 │ prorows                           │ f         │         0 │         4 │         10 │ {0}                                       
│ pg_catalog         │ pg_proc                 │ provariadic                       │ f         │         0 │         4 │          3 │ {0,2276,25}                               
│ pg_catalog         │ pg_proc                 │ protransform                      │ f         │         0 │         4 │          7 │ {-}                                       
│ pg_catalog         │ pg_proc                 │ proisagg                          │ f         │         0 │         1 │          2 │ {f,t}                                     
│ pg_catalog         │ pg_proc                 │ proiswindow                       │ f         │         0 │         1 │          2 │ {f,t}                                     
│ pg_catalog         │ pg_proc                 │ prosecdef                         │ f         │         0 │         1 │          1 │ {f}                                       
│ pg_catalog         │ pg_proc                 │ proleakproof                      │ f         │         0 │         1 │          2 │ {f,t}                                     
│ pg_catalog         │ pg_proc                 │ proisstrict                       │ f         │         0 │         1 │          2 │ {t,f}                                     
│ pg_catalog         │ pg_proc                 │ proretset                         │ f         │         0 │         1 │          2 │ {f,t}                                     
│ pg_catalog         │ pg_proc                 │ provolatile                       │ f         │         0 │         1 │          3 │ {i,s,v}                                   
│ pg_catalog         │ pg_proc                 │ pronargs                          │ f         │         0 │         2 │          9 │ {2,1,3,5,0,4,7,6,8}                       
│ pg_catalog         │ pg_proc                 │ pronargdefaults                   │ f         │         0 │         2 │          2 │ {0,1}                                     
│ pg_catalog         │ pg_proc                 │ prorettype                        │ f         │         0 │         4 │         89 │ {16,23,701,2278,25,20,2275,17,1700,2281,11
│ pg_catalog         │ pg_proc                 │ proargtypes                       │ f         │         0 │        31 │  -0.164887 │ {2281,26,"","23 23 2275 2281 23",2275,23,"
│ pg_catalog         │ pg_proc                 │ proallargtypes                    │ f         │  0.984974 │        41 │         36 │                                           
│ pg_catalog         │ pg_proc                 │ proargmodes                       │ f         │  0.984974 │        26 │         21 │                                           
│ pg_catalog         │ pg_proc                 │ proargnames                       │ f         │  0.974298 │        77 │         45 │                                           
│ pg_catalog         │ pg_proc                 │ proargdefaults                    │ f         │  0.998814 │       156 │         -1 │                                           
│ pg_catalog         │ pg_proc                 │ prosrc                            │ f         │         0 │        16 │  -0.900751 │ {aggregate_dummy,"select $2 + $1",range_co

情報が削除されてしまうのは困る場合もありますが、lessは-Sコマンドでモードを切り換えることができます。目的の行まで移動してからモードを切り換えると、折り返しが発生している場合でも比較的楽に参照したい行を探して表示できます。

折り返しを無くした表示はEXPLAINでは必須と言えます。試してみてください。

クエリ例

EXPLAIN (ANALYZE,VERBOSE,BUFFERS) select * from pg_stats;

テーブルのフォーマット自体を変える

\aコマンドでクエリ結果をコラムで整列させるか、させないかトグルできます。\aコマンドは

\pset aligned
\pset unaligned

を実行することと同じです。折り返しなしのページャー設定と一緒に使うと結果を読み易くなる場合もあります。

クエリ

select * from pg_stats;

unalighnedの結果

schemaname|tablename|attname|inherited|null_frac|avg_width|n_distinct|most_common_vals|most_common_freqs|histogram_bounds|correlation|most_common_elems|most_common_elem_freqs|e
pg_catalog|pg_proc|proname|f|0|64|-0.822459|{max,min,overlaps,has_column_privilege,date_part,int4,length,substring,sum,to_char,avg,int8,numeric,abs,generate_series,has_any_colu
pg_catalog|pg_proc|pronamespace|f|0|4|2|{11,12401}|{0.995255,0.00474496}|¤|1|¤|¤|¤
pg_catalog|pg_proc|proowner|f|0|4|1|{10}|{1}|¤|1|¤|¤|¤
pg_catalog|pg_proc|prolang|f|0|4|3|{12,13,14}|{0.938711,0.0367734,0.0245156}|¤|0.965122|¤|¤|¤
pg_catalog|pg_proc|procost|f|0|4|5|{1,100,10}|{0.97588,0.0126532,0.0106762}|{2,3}|0.984906|¤|¤|¤
pg_catalog|pg_proc|prorows|f|0|4|10|{0}|{0.980625}|{1,16,100,100,1000,1000,1000,1000,10000}|0.984112|¤|¤|¤
pg_catalog|pg_proc|provariadic|f|0|4|3|{0,2276,25}|{0.998023,0.00118624,0.000790826}|¤|0.99801|¤|¤|¤
pg_catalog|pg_proc|protransform|f|0|4|7|{-}|{0.996837}|{varchar_transform,numeric_transform,varbit_transform,timestamp_transform,interval_transform,time_transform}|0.993946|¤|¤
pg_catalog|pg_proc|proisagg|f|0|1|2|{f,t}|{0.952155,0.047845}|¤|0.966196|¤|¤|¤
pg_catalog|pg_proc|proiswindow|f|0|1|2|{f,t}|{0.994069,0.0059312}|¤|0.99974|¤|¤|¤
pg_catalog|pg_proc|prosecdef|f|0|1|1|{f}|{1}|¤|1|¤|¤|¤
pg_catalog|pg_proc|proleakproof|f|0|1|2|{f,t}|{0.909846,0.0901542}|¤|0.681924|¤|¤|¤
pg_catalog|pg_proc|proisstrict|f|0|1|2|{t,f}|{0.914986,0.0850138}|¤|0.787097|¤|¤|¤
pg_catalog|pg_proc|proretset|f|0|1|2|{f,t}|{0.980625,0.0193752}|¤|0.984268|¤|¤|¤
pg_catalog|pg_proc|provolatile|f|0|1|3|{i,s,v}|{0.691973,0.194939,0.113088}|¤|0.727836|¤|¤|¤
pg_catalog|pg_proc|pronargs|f|0|2|9|{2,1,3,5,0,4,7,6,8}|{0.429023,0.382365,0.0691973,0.0470542,0.0383551,0.0280743,0.00276789,0.00237248,0.000790826}|¤|0.377162|¤|¤|¤
pg_catalog|pg_proc|pronargdefaults|f|0|2|2|{0,1}|{0.998814,0.00118624}|¤|0.999987|¤|¤|¤
pg_catalog|pg_proc|prorettype|f|0|4|89|{16,23,701,2278,25,20,2275,17,1700,2281,1184,2249,21,600,1186,700}|{0.266508,0.0814551,0.0755239,0.0676157,0.0585212,0.0553578,0.0359826,
pg_catalog|pg_proc|proargtypes|f|0|31|-0.164887|{2281,26,"","23 23 2275 2281 23",2275,23,"25 25",701,25,20,"2281 2281",1700,"23 23","701 701",21,"1700 1700",700,"20 20","603 60
pg_catalog|pg_proc|proallargtypes|f|0.984974|41|36|¤|¤|{"{19,25,16,16,19,1003,25}","{19,25,25}","{19,25,25,25}","{23,20,20,20,20,16}","{23,25,25,25,25,25,23,25}","{23,26,23,26,
pg_catalog|pg_proc|proargmodes|f|0.984974|26|21|¤|¤|{"{i,i,i,o,o,o,o}","{i,i,o,o}","{i,i,o,o,o}","{i,o}","{i,o,o}","{i,o,o}","{i,o,o}","{i,o,o,o}","{i,o,o,o}","{i,o,o,o,o}","{i
pg_catalog|pg_proc|proargnames|f|0.974298|77|45|¤|¤|{"{\"\",x,n}","{abbrev,utc_offset,is_dst}","{acl,grantor,grantee,privilege_type,is_grantable}","{base,from_json,use_json_as_
pg_catalog|pg_proc|proargdefaults|f|0.998814|156|-1|¤|¤|{"({CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location 28089 :
pg_catalog|pg_proc|prosrc|f|0|16|-0.900751|{aggregate_dummy,"select $2 + $1",range_constructor2,range_constructor3,textlen,box_center,bpcharlen,circle_center,dpow,numeric_power
    SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
","SELECT
  CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
       THEN CASE WHEN $2 = -1 /* default typmod */
                 THEN CAST(2^30 AS integer)
                 ELSE information_schema._pg_char_max_length($1, $2) *
                      pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))
            END
       ELSE null
  END",aclitem_eq,array_cat,array_to_json_pretty,bit_or,bittypmodin,box_below_eq,box_overright,bpcharlt,btfloat4sortsupport,btoidcmp,byteacat,cash_eq,chareq,cidsend,circle_over

NULLを分るように表示したい

デフォルトではNULLは何も表示されません。空文字列の場合と区別できません。NULLの場合には特定の文字列を表示させる事ができます。

\pset null ¤

この例ではあまり使わないUnicode文字を設定していますが、(null)などに設定しても構いません。

\pset null (null)

クエリ

select * from pg_stat_xact_all_tables ;

実行例

┌───────┬────────────────────┬─────────────────────────┬──────────┬──────────────┬──────────┬───────────────┬───────────┬───────────┬───────────┬───────────────┐
│ relid │     schemaname     │         relname         │ seq_scan │ seq_tup_read │ idx_scan │ idx_tup_fetch │ n_tup_ins │ n_tup_upd │ n_tup_del │ n_tup_hot_upd │
├───────┼────────────────────┼─────────────────────────┼──────────┼──────────────┼──────────┼───────────────┼───────────┼───────────┼───────────┼───────────────┤
│  1261 │ pg_catalog         │ pg_auth_members         │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  2617 │ pg_catalog         │ pg_operator             │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  2600 │ pg_catalog         │ pg_aggregate            │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  1136 │ pg_catalog         │ pg_pltemplate           │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│ 12529 │ information_schema │ sql_implementation_info │        0 │            0 │   (null) │        (null) │         0 │         0 │         0 │             0 │
│  2609 │ pg_catalog         │ pg_description          │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│  2612 │ pg_catalog         │ pg_language             │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │
│ 12539 │ information_schema │ sql_packages            │        0 │            0 │   (null) │        (null) │         0 │         0 │         0 │             0 │
│  3601 │ pg_catalog         │ pg_ts_parser            │        0 │            0 │        0 │             0 │         0 │         0 │         0 │             0 │

 

ヒストリーファイルをデータベース毎に分ける

データベースに保存されているテーブルはデータベース毎に異るので、他のデータベースでの履歴はあまり役立たないことが多いです。データベース毎に履歴を記録することも可能です。

\set HISTFILE ~/.psql_history- :DBNAME

記録する履歴を変更したい

デフォルトでは500の履歴を残します。任意の数に変更可能です。

\set HISTSIZE 2000

重複した履歴は省略する

操作記録として履歴を保存しているのではなく、前に実行したコマンドを繰り返し実行する為にヒストリーファイルを利用している場合、同じコマンドを何度も記録しても意味がありません。重複した履歴は省略することも可能です。

\set HISTCONTROL ignoredups

よく使うコマンドを登録したい

\setを利用すると変数として登録できます。

\set uptime 'select now() - backend_start as uptime from pg_stat_activity where pid = pg_backend_pid();'

利用する場合は変数名に:を付けます。

  :uptime

実行例

testusr@testusr=> :uptime
┌─────────────────┐
│     uptime      │
├─────────────────┤
│ 01:08:02.932329 │
└─────────────────┘
(1 行)

時間: 0.700 ms

実行時間が長いクエリを表示するクエリを登録しておくと便利です。実行には統計情報が有効になっている必要があります。

\set show_slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;'

タブによる補完も効くので長い変数名でも構いません。また、設定した変数の中に変数があった場合は展開されるので、パラメータを埋め込む事も可能です。

\set myquery 'select * from :mytable;'

実行例

\set mytable test
:myquery

出力

testusr@testusr=> :myquery 
testusr-> ;
┌────┬─────┐
│ id │ txt │
├────┼─────┤
│  1 │ aaa │
└────┴─────┘
(1 行)

時間: 0.187 ms

クオート(エスケープ)もサポートしています。識別子、パラメーター両方正しくクオートされます。エスケープしておく方がより安全です。

識別子のエスケープ

  \set myquery 'select * from :"mytable";'

パラメータのエスケープ

\set myquery 'select * from :"mytable" where id =:''id''';

'(シングルクオート)はSQLエスケープと同じく'(シングルクオート)でエスケープします。

実行例

testusr@[local] ~=> \set id 1
testusr@[local] ~=> \set mytable test
testusr@[local] ~=> \echo :myquery
select * from :"mytable" where id =:'id';
testusr@[local] ~=> :myquery
┌────┬─────┐
│ id │ txt │
├────┼─────┤
│  1 │ aaa │
└────┴─────┘
(1 行)

SQLインジェクションできないか試してみます。

別のIDのレコードも取得

testusr@[local] ~=> \set id '1 or id=2'
testusr@[local] ~=> :myquery
ERROR:  22P02: 型integerの入力構文が無効です: "1 or id=2"
行 1: select * from "test" where id ='1 or id=2';
                                     ^
LOCATION:  pg_atoi, numutils.c:104

エスケープ処理の確認

testusr@[local] ~=> \set id '1 or txt = ''aaa'''
testusr@[local] ~=> :myquery
ERROR:  22P02: 型integerの入力構文が無効です: "1 or txt = 'aaa'"
行 1: select * from "test" where id ='1 or txt = ''aaa''';
                                     ^
LOCATION:  pg_atoi, numutils.c:104

パラメータが文字列として処理され、エスケープ処理もされているのでSQLインジェクションはできません。

テーブル名などの識別子を"(ダブルクオート)で囲んだ場合、識別子の大文字/小文字が区別される点に注意してください。

\dコマンドで実行されるSQLを表示する

様々な\dコマンドはSQLクエリでシステムカタログをクエリして情報を取得しています。ECHO_HIDDENは実際に実行されているクエリを表示します。システムカタログへのクエリを参照したい場合に便利です。

  \set ECHO_HIDDEN 1

実行例

testusr@[local] ~=> \d
********* 問い合わせ ********
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' 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','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;
*****************************

リレーションの一覧

┌──────────┬─────────────┬────────────┬─────────┐
│ スキーマ │    名前     │     型     │ 所有者  │
├──────────┼─────────────┼────────────┼─────────┤
│ public   │ test        │ テーブル   │ testusr │
│ public   │ test_id_seq │ シーケンス │ testusr │
└──────────┴─────────────┴────────────┴─────────┘
(2 行)

まとめ

.psqlrcを利用すると、好みの設定を利用でき、よく使うクエリを登録して利用することも可能です。.psqlrcを利用するとより便利にpsqlを利用できます。他にも様々な機能があるので詳しくはPostgreSQLのマニュアルなどを参照してください。

 

参考リンク

http://www.postgresql.org/docs/9.3/static/app-psql.html
https://github.com/datachomp/dotfiles/blob/master/.psqlrc