Databases

結果が1行1列のみの場合 psqlコマンドでクエリを実行した結果をシェルスクリプトで利用するには、-qtAオプションを指定すると扱いやすくなる。 $ psql -qtA -U 接続ユーザ -d データベース名 -c "クエリ(SQL)" 例えばcount(*)で集計した結果は以下のような出力となる

PostgreSQLの設定ファイルの場所を探すコマンド(show config_file) PostgreSQLの設定ファイルpostgresql.confはOSやインストール方法によって場所が異なる。 稼働しているPostgreSQLの設定ファイルの場所を確認するには以下のコマンドを実行する。

SQLでテーブル一覧を取得する PostgreSQLでテーブル一覧を取得するにはinformation_schema.tablesテーブルを参照する。 SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_name; table_schemaの条件にはスキーマ名を指定する。 スキーマ名で絞らない場合pg_catalogスキーマのシステムテーブルも取得されるため指定しておくとよい。

CSV形式でクエリ結果を出力する psqlコマンドでクエリ結果をCSV形式で出力するには以下のようにする。 psql -h ホスト名 -U ユーザ名 -d データベース名 -c "SQL" -A -F ',' > output.csv オプションは以下の通り。

高速なレコード件数の取得 PostgreSQLで大量のレコードを含むテーブルでcount関数を使って件数取得すると時間がかかる。 SELECT count(*) FROM テーブル名; 統計情報ビューの一種であるpg_stat_user_tablesを使うと高速にレコード件数を取得できる。

jsonb_set関数 jsonb_set関数はJSON文字列の要素を更新する関数。 以下の書式で定義される。 jsonb_set(target, path, new_value, [create_missing]) target: 更新対象のJSON path: 更新する要素のパス new_value: 更新する値 create_missing: パスで指定したキーが無い場合に追加しない場合はfalseを指定する 更新する要素のパス 要素のパスは{}の中にカンマ区切りで指定する。 キーバリューの場合はキー名、配列の場合はインデックスを指定する。

psqlでエラーが出てもステータスコードが0になるパターン psqlコマンドでSQLファイルから標準入力で受け取って実行するには以下のようにする。 $ psql -U usename -d dbname < test.sql 上記でtest.sqlの内容を受け取ってSQLを実行する。

テーブル定義を確認するにはpsqlの\dコマンドを使う。SQLで取得する場合はinformation_schema.columnsを参照する。

PostgreSQLのtext型のカラムにjson型としてクエリする 以下のデータからJSONデータを抽出したテーブルを表示する。 ここでsettingカラムの型はtextとする。 # select * from settings; id | setting ----+------------------------------ 1 | {"foo": "bar", "baz": "qux"} 2 | {"foo": "aaa", "baz": "bbb"} settingカラムのJSONデータの、“foo”, “baz"キーをカラム名とした値を取得しテーブルとして表示するには 例えば以下のようなSQLを実行する。