Help us understand the problem. What is going on with this article?

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テキスト
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした