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

== dump/restore ==
dump
<syntaxhighlight lang="bash">
$ pg_dump <DATABASE_NAME> > <FILE_NAME>
</syntaxhighlight>
restore
<syntaxhighlight lang="sql">
$ psql <DATABASE_NAME>< infile
</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">
# DROP ROLE <ROLE_NAME>;
</syntaxhighlight>



== database ==
一覧
<syntaxhighlight lang="sql">
# \l
</syntaxhighlight>
切り替え
<syntaxhighlight lang="sql">
# \c <DABASE_NAME>
</syntaxhighlight>
作成
<syntaxhighlight lang="sql">
# CREATE DATABASE <DABASE_NAME>;
</syntaxhighlight>
<syntaxhighlight lang="sql">
# CREATE DATABASE <DABASE_NAME> OWNER <OWNER_NAME>;
</syntaxhighlight>
削除
<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 <TABLE_NAME>;
</syntaxhighlight>



== schema ==
一覧
<syntaxhighlight lang="sql">
# \dn
</syntaxhighlight>
確認
<syntaxhighlight lang="sql">
# SELECT current_schema;
</syntaxhighlight>
変更
<syntaxhighlight lang="sql">
# SET search_path TO <SCHEMA_NAME>;
</syntaxhighlight>


[[Category:PostgreSQL]]
[[Category:Database]]