PostgreSQLでよくやることのソースを表示

== dump/restore ==
=== dump ===
<syntaxhighlight lang="bash">
$ pg_dump <DATABASE_NAME> > <FILE_NAME>
</syntaxhighlight>
or
<syntaxhighlight lang="bash">
$ pg_dump -Fc <DATABASE_NAME> > <FILE_NAME>
</syntaxhighlight>
or schema指定
<syntaxhighlight lang="bash">
$ pg_dump -n <SCHEMA> <DATABASE_NAME> > <FILE_NAME>
</syntaxhighlight>
=== restore ===
<syntaxhighlight lang="bash">
$ psql <DATABASE_NAME> < <FILE_NAME>
</syntaxhighlight>
or pg_dumpでFcやFtをつけてたらこっち
<syntaxhighlight lang="bash">
$ pg_restore -d <DATABASE_NAME> <FILE_NAME>
</syntaxhighlight>

== role ==
=== 一覧 ===
<syntaxhighlight lang="sql">
# \du
</syntaxhighlight>
=== 作成 ===
<syntaxhighlight lang="sql">
# CREATE ROLE <ROLE_NAME> WITH LOGIN PASSWORD '<PASSWORD>';
</syntaxhighlight>
=== 名前変更 ===
<syntaxhighlight lang="sql">
# ALTER ROLE <OLD_ROLE_NAME> TO <NEW_ROLE_NAME>
</syntaxhighlight>
=== パスワード ===
==== 変更 ====
<syntaxhighlight lang="sql">
# ALTER ROLE <ROLE_NAME> WITH PASSWORD '<PASSWORD>';
</syntaxhighlight>
==== 削除 ====
<syntaxhighlight lang="sql">
# ALTER ROLE <ROLE_NAME> WITH PASSWORD NULL;
</syntaxhighlight>
=== 削除 ===
<syntaxhighlight lang="sql">
# DROP ROLE <ROLE_NAME>;
</syntaxhighlight>

== database ==
=== 一覧 ===
<syntaxhighlight lang="sql">
# \l
</syntaxhighlight>
=== 切り替え ===
<syntaxhighlight lang="sql">
# \c <DATABASE_NAME>
</syntaxhighlight>
=== 作成 ===
<syntaxhighlight lang="bash">
$ createdb -U <ROLE_NAME> <DATABASE_NAME>
</syntaxhighlight>
or
<syntaxhighlight lang="sql">
# CREATE DATABASE <DATABASE_NAME>;
</syntaxhighlight>
or
<syntaxhighlight lang="sql">
# CREATE DATABASE <DATABASE_NAME> OWNER <ROLE_NAME>;
</syntaxhighlight>
=== 名前変更 ===
<syntaxhighlight lang="sql">
# ALTER DATABASE <OLD_NAME> RENAME TO <NEW_NAME>;
</syntaxhighlight>
=== 削除 ===
<syntaxhighlight lang="bash">
$ dropdb -U <ROLE_NAME> <DATABASE_NAME>
</syntaxhighlight>
or
<syntaxhighlight lang="sql">
# DROP DATABASE <DATABASE_NAME>;
</syntaxhighlight>

== table ==
=== 一覧（viewとかも） ===
<syntaxhighlight lang="sql">
# \d
</syntaxhighlight>
=== 一覧（tableだけ） ===
<syntaxhighlight lang="sql">
# \dt
</syntaxhighlight>
=== 作成 ===
<syntaxhighlight lang="sql">
# CREATE TABLE IF NOT EXISTS <TABLE_NAME>(
    id BIGSERIAL,
    <COLUMN_NAME> <TYPE>,
    ...,
    PRIMARY KEY (id)
);
COMMENT ON COLUMN <TABLE_NAME>.id IS 'ID';
COMMENT ON COLUMN <TABLE_NAME>.<COLUMN_NAME>IS '<COMMENT>';
...
</syntaxhighlight>
=== 削除 ===
<syntaxhighlight lang="sql">
# DROP TABLE IF EXISTS <TABLE_NAME>;
</syntaxhighlight>


== column ==
=== 追加 ===
<syntaxhighlight lang="sql">
# ALTER TABLE <TABLE_NAME> ADD COLUMN <COLUMN_NAME> <COLUMN_TYPE>;
</syntaxhighlight>
or 特定のカラムの後に追加
<syntaxhighlight lang="sql">
# ALTER TABLE <TABLE_NAME> ADD COLUMN <COLUMN_NAME> <COLUMN_TYPE> AFTER <EXISTING_COLUMN_NAME>;
</syntaxhighlight>
=== 型変更 ===
<syntaxhighlight lang="sql">
# ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> TYPE <NEW_COLUMN_TYPE>;
</syntaxhighlight>
=== 削除 ===
<syntaxhighlight lang="sql">
# ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;
</syntaxhighlight>



== schema ==
=== 一覧 ===
<syntaxhighlight lang="sql">
# \dn
</syntaxhighlight>
=== 確認 ===
<syntaxhighlight lang="sql">
# SELECT current_schema;
</syntaxhighlight>
=== 作成 ===
<syntaxhighlight lang="sql">
# CREATE SCHEMA <SCHEMA_NAME>; 
</syntaxhighlight>
=== 複製 ===
<syntaxhighlight lang="bash">
$ pg_dump　-n <SOURCE_SCHEMA_NAME> <DATABASE_NAME> > <FILE_NAME>
</syntaxhighlight>
<syntaxhighlight lang="sql">
# ALTER SCHEMA <NEW_SCHEMA_NAME> RENAME TO <SOURCE_SCHEMA_NAME>;
</syntaxhighlight>
<syntaxhighlight lang="bash">
$ psql <DATABASE_NAME> < <FILE_NAME>
</syntaxhighlight>
=== 名前変更 ===
<syntaxhighlight lang="sql">
# ALTER SCHEMA <OLD_NAME> RENAME TO <NEW_NAME>;
</syntaxhighlight>

=== search_path変更 ===
<syntaxhighlight lang="sql">
# SET search_path TO <SCHEMA_NAME>;
</syntaxhighlight>
or
<syntaxhighlight lang="sql">
# ALTER USER <ROLE_NAME> SET search_path TO <SCHEMA_NAME>;
</syntaxhighlight>
=== 削除 ===
<syntaxhighlight lang="sql">
# DROP SCHEMA <SCHEMA_NAME>; 
</syntaxhighlight>
or スキーマ内のテーブルごと削除
<syntaxhighlight lang="sql">
# DROP SCHEMA <SCHEMA_NAME> CASCADE;
</syntaxhighlight>


[[Category:PostgreSQL]]
[[Category:Database]]
[[Category:よくやること]]