「PostgreSQLでよくやること」の版間の差分

提供: オレッジベース
移動先: 案内、 検索
 
(同じ利用者による、間の16版が非表示)
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
+
or
−
<syntaxhighlight lang="sql">
+
<syntaxhighlight lang="bash">
−
$ psql <DATABASE_NAME>< infile
+
$ 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>
 
</syntaxhighlight>
−
 
−
 
  
 
== 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">
 +
# ALTER ROLE <OLD_ROLE_NAME> TO <NEW_ROLE_NAME>
 +
</syntaxhighlight>
 +
=== パスワード ===
 +
==== 変更 ====
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
# ALTER ROLE <OLD_ROLE_NAME>TO <NEW_ROLE_NAME>
+
# ALTER ROLE <ROLE_NAME> WITH PASSWORD '<PASSWORD>';
 
</syntaxhighlight>
 
</syntaxhighlight>
−
削除
+
==== 削除 ====
 +
<syntaxhighlight lang="sql">
 +
# ALTER ROLE <ROLE_NAME> WITH PASSWORD NULL;
 +
</syntaxhighlight>
 +
=== 削除 ===
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
# DROP ROLE <ROLE_NAME>;
 
# DROP ROLE <ROLE_NAME>;
 
</syntaxhighlight>
 
</syntaxhighlight>
−
 
−
 
  
 
== database ==
 
== database ==
−
一覧
+
=== 一覧 ===
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
# \l
 
# \l
 
</syntaxhighlight>
 
</syntaxhighlight>
−
切り替え
+
=== 切り替え ===
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
# \c <DABASE_NAME>
+
# \c <DATABASE_NAME>
 +
</syntaxhighlight>
 +
=== 作成 ===
 +
<syntaxhighlight lang="bash">
 +
$ createdb -U <ROLE_NAME> <DATABASE_NAME>
 
</syntaxhighlight>
 
</syntaxhighlight>
−
作成
+
or
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
# create database  <DABASE_NAME>;
+
# CREATE DATABASE <DATABASE_NAME>;
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
or
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
# create database <DABASE_NAME> owner <OWNER_NAME>;
+
# 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>
 
</syntaxhighlight>
−
削除
+
or
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
# drop database <DATABASE_NAME>;
+
# DROP DATABASE <DATABASE_NAME>;
 
</syntaxhighlight>
 
</syntaxhighlight>
−
 
−
 
  
 
== 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">
 +
# 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">
 
<syntaxhighlight lang="sql">
−
# drop table <TABLE_NAME>;
+
# ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;
 
</syntaxhighlight>
 
</syntaxhighlight>
  
71行目: 130行目:
  
 
== 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>;
+
# CREATE SCHEMA <SCHEMA_NAME>;  
 
</syntaxhighlight>
 
</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>
 +
 +
 +
 +
== ほか ==
 +
=== 複数カラムの組み合わせで重複しているレコードを抽出 ===
 +
<syntaxhighlight lang="sql">
 +
SELECT
 +
      *
 +
FROM
 +
    <TABLE>
 +
WHERE
 +
    (<COLUMN_1>, <COLUMN_2>) IN (
 +
        SELECT DISTINCT
 +
              <COLUMN_1>
 +
            , <COLUMN_2>
 +
        FROM
 +
            <TABLE>
 +
        GROUP BY
 +
            <COLUMN_1>
 +
            , <COLUMN_2>
 +
        HAVING
 +
            count(<COLUMN_1>) > 1
 +
            AND count(<COLUMN_2>) > 1
 +
    )
 +
ORDER BY
 +
    <COLUMN_1>
 +
    , <COLUMN_2>
 +
;
 +
</syntaxhighlight>
 +
  
  
 
[[Category:PostgreSQL]]
 
[[Category:PostgreSQL]]
 
[[Category:Database]]
 
[[Category:Database]]
 +
[[Category:よくやること]]

2021年2月2日 (火) 15:25時点における最新版

dump/restore

dump

$ pg_dump <DATABASE_NAME> > <FILE_NAME>

or 

$ pg_dump -Fc <DATABASE_NAME> > <FILE_NAME>

or schema指定 

$ pg_dump -n <SCHEMA> <DATABASE_NAME> > <FILE_NAME>

restore

$ psql <DATABASE_NAME> < <FILE_NAME>

or pg_dumpでFcやFtをつけてたらこっち 

$ pg_restore -d <DATABASE_NAME> <FILE_NAME>

role

一覧

# \du

作成

# CREATE ROLE <ROLE_NAME> WITH LOGIN PASSWORD '<PASSWORD>';

名前変更

# ALTER ROLE <OLD_ROLE_NAME> TO <NEW_ROLE_NAME>

パスワード

変更

# ALTER ROLE <ROLE_NAME> WITH PASSWORD '<PASSWORD>';

削除

# ALTER ROLE <ROLE_NAME> WITH PASSWORD NULL;

削除

# DROP ROLE <ROLE_NAME>;

database

一覧

# \l

切り替え

# \c <DATABASE_NAME>

作成

$ createdb -U <ROLE_NAME> <DATABASE_NAME>

or 

# CREATE DATABASE <DATABASE_NAME>;

or 

# CREATE DATABASE <DATABASE_NAME> OWNER <ROLE_NAME>;

名前変更

# ALTER DATABASE <OLD_NAME> RENAME TO <NEW_NAME>;

削除

$ dropdb -U <ROLE_NAME> <DATABASE_NAME>

or 

# 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>;


column

追加

# ALTER TABLE <TABLE_NAME> ADD COLUMN <COLUMN_NAME> <COLUMN_TYPE>;

or 特定のカラムの後に追加 

# ALTER TABLE <TABLE_NAME> ADD COLUMN <COLUMN_NAME> <COLUMN_TYPE> AFTER <EXISTING_COLUMN_NAME>;

型変更

# ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> TYPE <NEW_COLUMN_TYPE>;

削除

# ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;


schema

一覧

# \dn

確認

# SELECT current_schema;

作成

# CREATE SCHEMA <SCHEMA_NAME>;

複製

$ pg_dump　-n <SOURCE_SCHEMA_NAME> <DATABASE_NAME> > <FILE_NAME>

# ALTER SCHEMA <NEW_SCHEMA_NAME> RENAME TO <SOURCE_SCHEMA_NAME>;

$ psql <DATABASE_NAME> < <FILE_NAME>

名前変更

# ALTER SCHEMA <OLD_NAME> RENAME TO <NEW_NAME>;

search_path変更

# SET search_path TO <SCHEMA_NAME>;

or 

# ALTER USER <ROLE_NAME> SET search_path TO <SCHEMA_NAME>;

削除

# DROP SCHEMA <SCHEMA_NAME>;

or スキーマ内のテーブルごと削除 

# DROP SCHEMA <SCHEMA_NAME> CASCADE;


ほか

複数カラムの組み合わせで重複しているレコードを抽出

SELECT
      * 
FROM
    <TABLE>
WHERE
    (<COLUMN_1>, <COLUMN_2>) IN ( 
        SELECT DISTINCT
              <COLUMN_1>
            , <COLUMN_2>
        FROM
            <TABLE>
        GROUP BY
            <COLUMN_1>
            , <COLUMN_2>
        HAVING
            count(<COLUMN_1>) > 1 
            AND count(<COLUMN_2>) > 1
    ) 
ORDER BY
    <COLUMN_1>
    , <COLUMN_2>
;
「https://wiki.r371.com/index.php?title=PostgreSQLでよくやること&oldid=154」から取得