「PostgreSQLでよくやること」の版間の差分
提供: オレッジベース
| 1行目: | 1行目: | ||
== dump/restore == | == dump/restore == | ||
| − | dump | + | === dump === |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
$ pg_dump <DATABASE_NAME> > <FILE_NAME> | $ pg_dump <DATABASE_NAME> > <FILE_NAME> | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | restore | + | === restore === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
$ psql <DATABASE_NAME>< infile | $ psql <DATABASE_NAME>< infile | ||
| 12行目: | 12行目: | ||
== role == | == role == | ||
| − | 一覧 | + | === 一覧 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# \du | # \du | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 作成 | + | === 作成 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# CREATE ROLE <ROLE_NAME> WITH LOGIN PASSWORD '<PASSWORD>'; | # CREATE ROLE <ROLE_NAME> WITH LOGIN PASSWORD '<PASSWORD>'; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 名前変更 | + | === 名前変更 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# ALTER ROLE <OLD_ROLE_NAME>TO <NEW_ROLE_NAME> | # ALTER ROLE <OLD_ROLE_NAME>TO <NEW_ROLE_NAME> | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 削除 | + | === 削除 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# DROP ROLE <ROLE_NAME>; | # DROP ROLE <ROLE_NAME>; | ||
| 32行目: | 32行目: | ||
== database == | == database == | ||
| − | 一覧 | + | === 一覧 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# \l | # \l | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 切り替え | + | === 切り替え === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# \c <DABASE_NAME> | # \c <DABASE_NAME> | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 作成 | + | === 作成 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# CREATE DATABASE <DABASE_NAME>; | # CREATE DATABASE <DABASE_NAME>; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | or | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
| − | # CREATE DATABASE <DABASE_NAME> OWNER < | + | # CREATE DATABASE <DABASE_NAME> OWNER <ROLE_NAME>; |
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 削除 | + | === 削除 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# DROP DATABASE <DATABASE_NAME>; | # DROP DATABASE <DATABASE_NAME>; | ||
| 55行目: | 56行目: | ||
== table == | == table == | ||
| − | 一覧（viewとかも） | + | === 一覧（viewとかも） === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# \d | # \d | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 一覧（tableだけ） | + | === 一覧（tableだけ） === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# \dt | # \dt | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 作成 | + | === 作成 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# CREATE TABLE IF NOT EXISTS <TABLE_NAME>( | # CREATE TABLE IF NOT EXISTS <TABLE_NAME>( | ||
| 75行目: | 76行目: | ||
... | ... | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 削除 | + | === 削除 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
| − | # DROP TABLE <TABLE_NAME>; | + | # DROP TABLE IF EXISTS <TABLE_NAME>; |
</syntaxhighlight> | </syntaxhighlight> | ||
| 83行目: | 84行目: | ||
== schema == | == schema == | ||
| − | 一覧 | + | === 一覧 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# \dn | # \dn | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 確認 | + | === 確認 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# SELECT current_schema; | # SELECT current_schema; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | 変更 | + | === 変更 === |
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
# SET search_path TO <SCHEMA_NAME>; | # SET search_path TO <SCHEMA_NAME>; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | + | or | |
| + | <syntaxhighlight lang="sql"> | ||
| + | # ALTER USER <ROLE_NAME> SET search_path TO <SCHEMA_NAME>; | ||
| + | </syntaxhighlight> | ||
[[Category:PostgreSQL]] | [[Category:PostgreSQL]] | ||
[[Category:Database]] | [[Category:Database]] | ||
2017年12月14日 (木) 13:32時点における版
目次
dump/restore
dump
$ pg_dump <DATABASE_NAME> > <FILE_NAME>
restore
$ psql <DATABASE_NAME>< infile
role
一覧
# \du
作成
# CREATE ROLE <ROLE_NAME> WITH LOGIN PASSWORD '<PASSWORD>';
名前変更
# ALTER ROLE <OLD_ROLE_NAME>TO <NEW_ROLE_NAME>
削除
# DROP ROLE <ROLE_NAME>;
database
一覧
# \l
切り替え
# \c <DABASE_NAME>
作成
# CREATE DATABASE <DABASE_NAME>;
or
# CREATE DATABASE <DABASE_NAME> OWNER <ROLE_NAME>;
削除
# DROP DATABASE <DATABASE_NAME>;
table
一覧（viewとかも）
# \d
一覧（tableだけ）
# \dt
作成
# 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>';
...
削除
# DROP TABLE IF EXISTS <TABLE_NAME>;
schema
一覧
# \dn
確認
# SELECT current_schema;
変更
# SET search_path TO <SCHEMA_NAME>;
or
# ALTER USER <ROLE_NAME> SET search_path TO <SCHEMA_NAME>;
