postgresqlまとめ

メンテナンス系のクエリまとめ

データベース関連

テーブル一覧取得

SELECT relname AS table_name FROM pg_stat_user_tables;

テーブル関連

プライマリキー

CREATE TABLE \$tablename ( \$col_name SERIAL PRIMARY KEY NOT NULL );

テーブル名変更

ALTER TABLE \$table_name RENAME TO \$new_table_name;

テーブル複製

完全コピー(制約あり、インデックスあり、データあり)

CREATE TABLE \$new_name (LIKE \$old_name INCLUDING ALL);

不完全コピー(制約なし、インデックスなし、データあり)

CREATE TABLE \$new_name AS SELECT * FROM \$old_name;

テーブルコメント取得

SELECT
psut.relname AS table_name,
pd.description AS table_comment
FROM
pg_stat_user_tables AS psut,
pg_description AS pd
where
psut.relname='\$table_name'
and
psut.relid=pd.objoid
and
pd.objsubid=0

カラム一覧取得

SELECT * FROM information_schema.columns WHERE table_catalog='\$dbname' and table_name='\$table_name' order by ordinal_position;

カラム一覧とコメントを取得

select
information_schema.columns.column_name,
information_schema.columns.data_type,
(
select
description
from
pg_description
where
pg_description.objoid=pg_stat_user_tables.relid
and
pg_description.objsubid=information_schema.columns.ordinal_position
)
from
pg_stat_user_tables,
information_schema.columns
where
pg_stat_user_tables.relname='\$table_name'
and
pg_stat_user_tables.relname=information_schema.columns.table_name
;

カラム関連

インデックス関連

インデックス作成

CREATE INDEX \$index_name ON \$table_name (\$target_col_names);
CREATE UNIQUE INDEX \$index_name ON \$table_name (\$target_col_names);

インデックス削除

drop index \$index_name;

インデックス名前変更

ALTER INDEX \$current_name RENAME TO \$new_name;

PSQL

テーブルとプライマリキーの一覧表示

\d

テーブルのカラム一覧表示

\d table_name

インデックス一覧表示

\di

インデックス詳細表示

\d \$index_name

テーブル作成分表示 (show create table)

pg_dump -h \$hostname -U \$username --schema-only \$dbname --table \$tablename
--schema-only : テーブル定義をついける
--table : テーブルを制限

PG_DUMP(create table分の取得)

pg_dump \$dbname -s -t \$table_name -h \$hostname -U \$user_name

オプション	内容
-s	定義のみ
-a	データのみ
-D	カラム名を含めたinsertコマンドでダンプ
-F	アウトプットファイル形式指定 pテキスト