よく使うPostgreSQLコマンド (ver15対応版)

よく使うPostgreSQLコマンドを集めました。
参考) https://www.postgresql.jp/document/15/html/

データベース接続

/bin/bash

# ユーザ postgres で操作
su - postgres
# データベース接続 Peer方式 (ユーザpostgresでのデフォルト接続方式)
psql
# データベース接続 TCP/IP方式 (ユーザ名を指定しない場合、データベース名を指定しない場合、いずれも実行ユーザ名と同値が自動選択される)
psql -h サーバ名 or サーバIP [-U ユーザ名] [データベース名(指定なければユーザ名と同じ)]

コマンドラインインターフェース

psql

-- コマンドリセット
\r
-- ヘルプ (PostgreSQL固有のコマンド)
\? [PostgreSQLコマンド]
-- ヘルプ (SQLコマンド)
\help [SQLコマンド]
-- コマンドライン終了 (データベース切断)
\q

システム情報

psql

-- バージョン確認
select version();
-- サーバアドレス
select inet_server_addr();
-- サーバポート
select inet_server_port();
-- タイムゾーン
show timezone;
-- 現在時刻
select now();
-- 稼働時間
select date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;
-- 言語設定の確認 (internalはデータベース作成時に決定し変更できない、それ以外はrole/user単位で変更可能)
select name,setting,context from pg_settings where name like 'lc%';

ユーザ操作

psql

-- ユーザ一覧
\du
select * from pg_user;
-- カレントユーザ
select current_user;
-- ユーザ作成 (スーパーユーザー)
create user スーパーユーザ名 with password 'パスワード' superuser;
-- ユーザ作成 (DB作成・ユーザ作成権限あり)
create user ユーザ名 with password 'パスワード' createdb createuser;
-- ユーザ作成 (一般ユーザ)
create user ユーザ名 with password 'パスワード';
-- ユーザ作成 (読み取り専用ユーザ) *ver14以降
create role 読み取り専用ユーザ名 with login password 'パスワード';
grant pg_read_all_data to 読み取り専用ユーザ名;
-- パスワード変更
\password ユーザ名
-- ユーザ削除
drop role ユーザ名;

データベース操作

psql

-- データベース一覧 (Linuxなどシェル上から)
psql -l
-- データベース一覧 (PostgreSQLコマンドラインインターフェース上から)
\l
select * from pg_database;
-- 現在のデータベース
select current_database();
-- 接続先のデータベース変更
\c データベース名
-- データベースサイズ
select pg_size_pretty(pg_database_size('pgbench'));
-- データベース作成
create database データベース名 owner オーナー名;
-- データベース削除
drop データベース名;

スキーマ操作

psql

-- スキーマ一覧
\dn
select * from pg_namespace;
-- 現在のスキーマ
select current_schema();
-- スキーマ作成 (オプション指定がなければ実行ユーザがオーナー)
create schema スキーマ名;
create schema スキーマ名 autholization ユーザ名;
-- 検索パス変更 (一時的)
set search_path to '$user', スキーマ名, public;
-- 検索パス変更 (恒久）
alter role ユーザ名 set search_path = public, スキーマ名, '$user';
-- スキーマ削除
drop schema スキーマ名;
-- オーナー変更
alter schema スキーマ名 owner to 新しい所有者;
-- 名称変更
alter schema スキーマ名 rename to 新しいスキーマ名;
-- 権限付与/剥奪(全権限)
grant all privileges on all tables in schema スキーマ名 to ユーザ名;
revoke all privileges on all tables in schema スキーマ名 from ユーザ名;
-- 権限付与/剥奪(テーブル単位)
grant select, update, delete, create on all tables in shema スキーマ名 to ユーザ名;
grant select, update, delete on table スキーマ名.テーブル名 to ユーザ名;
-- 権限付与/剥奪(シーケンス)
grant all privileges on all sequences in schema スキーマ名 to ユーザ名;
-- 権限付与/剥奪(関数・ストアド)
grant execute on all function in schema スキーマ名 to ユーザ名;

シェルからコマンド発行

/bin/bash

-- コマンド発行(1行)
psql -c "コマンド"
-- コマンド発行(複数行)
psql -c << EOF
\c データベース
select current_database();
EOF

テーブル操作

psql

-- テーブル一覧
\d
select * from pg_tables order by schemaname, tablename;
-- テーブル一覧(コメント付き)
\d+
-- テーブル一覧 (サイズ降順)
select relname, (relpages * 8192) / 1024 as kbyte from pg_class order by kbyte desc;
-- テーブル一覧 (autovacuum最終実行日あり)
select schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum from pg_stat_all_tables where schemaname = current_schema order by schemaname, relname;
-- テーブルスキーマ
\d テーブル名
-- テーブルスキーマ(コメント付き)
\d+ テーブル名
-- テーブル作成
create table テーブル名 (
  ID serial
  , カラム1 int
  , カラム2 numeric(6,1) -- 有効桁数6 うち1桁小数 
  , カラム3 timestamp without time zone default (current_timestamp at time zone 'JST')
  , カラム4 jsonb
  , primary key (カラム1, カラム2)
);
-- コメント付与
comment on table テーブル名 is 'テーブルコメント';
comment on column テーブル名.カラム名 is 'カラムコメント';

インデックス操作

psql

-- インデックス一覧
\di
select tablename, indexname from pg_indexes where tablename not like 'pg_%';
-- 主キー
alter table テーブル名 add primary key (カラム);
-- Btree(汎用・部分一致)
create index インデックス名(省略可) on テーブル名 using btree (カラム1, カラム2);
-- Hash(完全一致)
create index インデックス名(省略可) on テーブル名 hash (カラム1);
-- Gin(全文・JSONB用)
create index インデックス名(省略可) on テーブル名 gin (カラム4);
-- インデックス再構築
reindex index インデックス名;

ビュー操作

psql

-- ビュー一覧
\dv

シーケンス操作

psql

-- シーケンス一覧
\ds
-- シーケンス値確認
select currval(シーケンス名); -- 未セット時はundefined
select * from シーケンス名;
-- シーケンスリセット
select setval (シーケンス名, 1, false);

CSVエクスポート・インポート

NULLと空文字の扱いを厳密にしたい場合は、NULL ASで記号や文字を個別に指定可能

psql

-- psql接続後 (○ダブルクォート、×where句)
\copy スキーマ名.テーブル名 to 'ファイル名.csv' csv header delimiter ',' force quote * null as '<null>'
\copy スキーマ名.テーブル名 from 'ファイル名.csv' csv header delimiter ',' null as '<null>'

シェルからpsqlコマンドを経由すれば、Where句が使用可能
その代わりに個別のNULL指定や囲み文字の指定が不可能

shell

# シェル (○where句、×ダブルクォート)
psql -h ホスト名 -U ユーザ名 -d DB名 -c "select * from スキーマ名.テーブル名 where 1 = 1" -A -F, > ファイル.csv

JSON操作 *ver14以降で対応可

参考: https://www.postgresql.org/docs/15/functions-json.html

psql

-- JSONB型を含んだテーブルを作成
-- JSONB:後方トリムあり&インデックス指定可、JSON:後方トリムなし・インデックス指定不可
create table orders(id serial, json jsonb);
\d orders
                            Table "orders"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 id     | integer |           | not null | nextval('orders_id_seq'::regclass)
 json   | jsonb   |           |          | 


-- データINSERT 1レコード単位
insert into orders(json) values ('{"customer":"A", "items": {"pn":"1111","qty":1}}');
insert into orders(json) values ('{"customer":"B", "items": {"pn":"2222","qty":2}}');
insert into orders(json) values ('{"customer":"C", "items": {"pn":"3333","qty":3}}');

-- バルクINSERT (意図的に重複レコードを挿入 → キーを設定していないため 2レコードとも挿入される)
insert into orders(json) values
  ('{"customer":"D", "items": {"pn":"4444","qty":4}}'),
  ('{"customer":"D", "items": {"pn":"4444","qty":4}}')
;

-- データSELECT
select * from orders;
 id |                         json                         
----+------------------------------------------------------
  1 | {"items": {"pn": "1111", "qty": 1}, "customer": "A"}
  2 | {"items": {"pn": "2222", "qty": 2}, "customer": "B"}
  3 | {"items": {"pn": "3333", "qty": 3}, "customer": "C"}
  4 | {"items": {"pn": "4444", "qty": 4}, "customer": "D"}
  5 | {"items": {"pn": "4444", "qty": 4}, "customer": "D"}
(5 rows)

-- データSELECT WHERE条件での文字列照合 (シングル/ダブルクォートの両方で囲んで比較照合する)
select * from orders where json['customer'] = '"A"';

 id |                         json                         
----+------------------------------------------------------
  1 | {"items": {"pn": "1111", "qty": 1}, "customer": "A"}
(1 row)

-- データSELECT WHERE条件での数値照合 (コロンでキャストしてから比較照合する)
select * from orders where json['items']['qty']::integer = 3;
 id |                         json                         
----+------------------------------------------------------
  3 | {"items": {"pn": "3333", "qty": 3}, "customer": "C"}
(1 row)

-- データSELECT 集約
select sum(json['items']['qty']::integer) from orders where json['customer'] = '"D"';
 sum 
-----
   8
(1 row)

-- データ削除
delete from orders where json['customer'] <> '"A"';
select * from orders;
 id |                         json                         
----+------------------------------------------------------
  1 | {"items": {"pn": "1111", "qty": 1}, "customer": "A"}
(1 row)

-- データINSERT w/ JSON_BUILD_OBJECT (キー,バリュー,キー,バリュー・・・の順で指定し、ネスト構造は変換できない)
-- JSON/JSONB型では 他レコードとデータ構造が揃ってなくても挿入できる (JSONであればOK)
insert into orders(json) values (json_build_object('customer', 'B', 'qty', 5));
select * from orders;
 id |                         json                         
----+------------------------------------------------------
  1 | {"items": {"pn": "1111", "qty": 1}, "customer": "A"}
  6 | {"qty": 5, "customer": "B"}

その他 便利コマンド

psql

-- ランダムUUID(ver13以降)
select gen_random_uuid() as uuid;
-- ランダム浮動小数(0〜100)
select random() * 100 as random_float;
-- ランダム整数(1〜100)
select ceil(random() * 100);
-- ランダム日付(1〜365日後)
select current_date::date + interval '1 days' * ceil(random() * 365) as random_date;