PostgreSQLの基本的なコマンド

これまでMySQLばかり使ってきてPostgreSQLの操作に戸惑ったので、よく使う基本的なコマンドをメモしました。

シェルでの操作

# 接続
psql -U USERNAME

# DB指定で接続
psql -d postgres

# ダンプ
pg_dump -U USERNAME DBNAME > DUMP_FILE

# ダンプ（データだけ）
pg_dump -U USERNAME --data-only DBNAME > DUMP_FILE

# ダンプ（データだけ/テーブル指定）
pg_dump -U USERNAME --data-only --table TABLE_NAME DBNAME > DUMP_FILE

# リストア
psql -U USERNAME DBNAME < DUMP_FILE

DB接続後の操作

# データベース一覧
\l

# データベース選択
\c DB_NAME

# スキーマ一覧
\dn

# テーブル一覧
# more表示になったときはspaceで進む
\dt


# テーブル構造の表示
\d TABLE_NAME;

# セッションのPIDを調べる
select pid, datname from pg_stat_activity;

# セッションを強制切断
select pg_terminate_backend (PID);

# 現在のスキーマ
select current_schema();

# スキーマ切り替え
set search_path = SCHEMA_NAME;

# selectの結果を縦表示にする(MySQLでいう末尾\G)
\x

# 終了
\q

エクステンションの追加・削除

intarrayの場合で

# エクステンションの有効化
# pg_catalog指定で全スキーマで有効になる
create extension intarray with schema pg_catalog;

# 有効なエクステンション一覧
\dx;

# エクステンションを無効化
drop extension intarray;

ロールの操作

# ロールの一覧
select rolname from pg_roles;

# ロールの作成
CREATE ROLE role_name WITH LOGIN SUPERUSER

# ロールの削除
DROP ROLE role_name

# ロールの変更
ALTER ROLE role_name WITH PASSWOD 'password'

インデックス操作

# インデックス作成
CREATE INDEX index_name ON table_name (column_name);

# インデックス削除
DROP INDEX index_name;

# インデックス一覧
¥di

# インデックス確認
\d index_name

データをリストで取得

# tsvで取り出す
psql -U USER -h HOSTNAME -p 5439 -d DBNAME -c "SELECT GETDATE();" -A -F $'\t' > ./test.tsv

# csvで取り出す
psql -U USER -h HOSTNAME -p 5439 -d DBNAME -c "SELECT GETDATE();" -A -F,> ./test.csv

各テーブルにレコード数を調べる

SELECT T2.relname, T2.reltuples  
FROM pg_stat_user_tables AS T1 
INNER JOIN pg_class AS T2 ON T1.relname = T2.relname 
ORDER BY T2.relname;