PostgreSQL DDL(USER、TABLE、INDEX、SEQUENCE、SCHEMA、TABLESPACE)

ユーザ

https://www.postgresql.jp/document/11/html/sql-createuser.html
https://www.postgresql.jp/document/11/html/sql-dropuser.html
https://www.postgresql.jp/document/11/html/sql-alteruser.html

  • 作成
    • CREATE USER name [ [ WITH ] option [ ... ] ]
  • 削除
    • DROP USER [ IF EXISTS ] name [, ...]
  • 変更
    • ALTER USER role_specification [ WITH ] option [ ... ]

ユーザの作成・削除・変更を行うには、CREATEROLE権限が必要。
ユーザやロールの情報はデータベースクラスタのグローバルオブジェクトとして保持しており、各データベースには含まれない。
CREATE ROLEで作成したロールのLOGIN属性はNOLOGIN、CREATE USERで作成した場合はLOGINがデフォルトになっている。
複数のユーザをロールとしてグループにし、1人のユーザと同じように使うことができる。

パスワードの設定
  • CREATE ROLE ロール名 [WITH] PASSWORD 'パスワード';
  • CREATE USER ロール名 [WITH] PASSWORD 'パスワード';
  • ALTER ROLE ロール名 [WITH] PASSWORD 'パスワード';
  • ALTER USER ロール名 [WITH] PASSWORD 'パスワード';
有効期限の変更
  • CREATE ROLE ロール名 [WITH] VALID UNTIL 'タイムスタンプ';
  • ALTER ROLE ロール名 [WITH] VALID UNTIL 'タイムスタンプ';
新規ロールに既存ロールのメンバ資格を与える場合
  • CREATE ROLE userA IN ROLE role1;
ロール名の変更
  • ALTER ROLE 現在のロール名 RENAME TO 新しいロール名;
  • ALTER USER 現在のロール名 RENAME TO 新しいロール名;

テーブル

https://www.postgresql.jp/document/11/html/sql-createtable.html
https://www.postgresql.jp/document/11/html/sql-droptable.html
https://www.postgresql.jp/document/11/html/sql-altertable.html

  • 作成
    • CREATE TABLE テーブル名 (列名 データ型);
  • 削除
    • DROP TABLE テーブル名;
  • 変更
    • ALTER TABLE テーブル名 RENAME TO 新しいテーブル名;
    • ALTER TABLE テーブル名 OWNER TO 新しい所有者となるユーザ名;
    • ALTER TABLE テーブル名 RENAME COLUMN 列名 TO 新しい列名;
    • ALTER TABLE テーブル名 ADD COLUMN 追加する列名 データ型;
    • ALTER TABLE テーブル名 DROP COLUMN 削除する列名;
    • ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT デフォルト値;

作成された直後のテーブルにアクセス権があるのはテーブルの所有者とスーパーユーザだけ。
外部キーとなるカラムは、主キー制約かユニーク制約が設定されている必要がある。

主キー(プライマリーキー)制約
  • CREATE TABLE テーブル名 (列名 データ型 PRIMARY KEY);
  • ALTER TABLE テーブル名 ADD PRIMARY KEY (列名);
  • ALTER TABLE テーブル名 ADD CONSTRAINT 主キー名 PRIMARY KEY (列名);
  • ALTER TABLE テーブル名 DROP CONSTRAINT 主キー名;

主キー名を省略した場合、「テーブル名_pkey」になる。

ユニーク制約
  • CREATE TABLE テーブル名 (列名 データ型 UNIQUE);
  • ALTER TABLE テーブル名 ADD UNIQUE (列名);
  • ALTER TABLE テーブル名 ADD CONSTRAINT ユニークキー名 UNIQUE (列名);
  • ALTER TABLE テーブル名 DROP CONSTRAINT ユニークキー名;

ユニークキー名を省略した場合、「テーブル名_列名_key」になる。

NOT NULL 制約
  • CREATE TABLE テーブル名 (列名 データ型 NOT NULL);
  • ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL;
  • ALTER TABLE テーブル名 ALTER COLUMN 列名 DROP NOT NULL;
外部キー制約(参照整合性制約)
  • CREATE TABLE テーブル名 (列名 データ型 REFERENCES 参照先テーブル名 (列名));
  • CREATE TABLE テーブル名 (列名 データ型, FOREIGN KEY (列名) REFERENCES 参照先テーブル名 (列名));
  • ALTER TABLE テーブル名 ADD CONSTRAINT 外部キー名 FOREIGN KEY (列名) REFERENCES 参照先テーブル名 (列名);
  • ALTER TABLE テーブル名 DROP CONSTRAINT 外部キー名;
  • CREATE TABLE テーブル名 (列名 データ型 REFERENCES 参照先テーブル名 (列名)) [ON DELETE CASCADE] [ON UPDATE CASCADE];
  • ALTER TABLE テーブル名 ADD CONSTRAINT 外部キー名 FOREIGN KEY (列名) REFERENCES 参照先テーブル名 (列名) [ON DELETE CASCADE] [ON UPDATE CASCADE];
チェック制約
  • CREATE TABLE テーブル名 (列名 データ型 CHECK (条件式));
  • ALTER TABLE テーブル名 ADD CONSTRAINT チェック制約名 CHECK (条件式);
  • ALTER TABLE テーブル名 DROP CONSTRAIN チェック制約名;
ドメイン制約

チェック制約を伴ったデータ型。

パーティション

https://www.postgresql.jp/document/11/html/ddl-partitioning.html

  • CREATE TABLE 親テーブル名 (列名 データ型) PARTITION BY { RANGE | LIST | HASH } (列名);
  • CREATE TABLE パーティションテーブル名 (列名 データ型) PARTITION OF 親テーブル名 FOR VALUES 条件;
  • DROP TABLE パーティションテーブル名;
  • ALTER TABLE 親テーブル名 ATTACH PARTITION パーティションテーブル名 FOR VALUES 条件;
  • ALTER TABLE 親テーブル名 DETACH PARTITION パーティションテーブル名;

パーティショニングとは、テーブルなどのデータを複数のまとまりに分割する機能。
DETACH PARTITION は、パーティションを親テーブルから切り離す場合に使う。
切り離されたパーティションは、親テーブルの検索で取得されない。
DROP TABLE でパーティションテーブルを高速に削除することができる。

継承

https://www.postgresql.jp/document/11/html/ddl-inherit.html

  • CREATE TABLE 親テーブル名;
  • CREATE TABLE テーブル名 INHERITS 親テーブル名;

親テーブルを検索すると、子テーブルの内容も取得される。
ただし、親テーブルに存在しないカラムの情報は取得できない。

インデックス

https://www.postgresql.jp/document/11/html/sql-createindex.html
https://www.postgresql.jp/document/11/html/sql-dropindex.html
https://www.postgresql.jp/document/11/html/sql-alterindex.html

  • 作成
    • CREATE INDEX インデックス名 ON テーブル名 [USING インデックスの種類] (列名);
  • 削除
    • DROP INDEX インデックス名;
  • 変更
    • ALTER INDEX インデックス名 RENAME TO 新しいインデックス名;
    • 主に変更できるのは名称。

マルチカラムインデックス(B-tree、GiST、GIN)
関数インデックス(式インデックス)。
部分インデックス。

インデックスの種類

https://www.postgresql.jp/document/11/html/indexes-types.html

  • B-tree、Hash、GiST、GIN
CLUSTER

https://www.postgresql.jp/document/11/html/sql-cluster.html

インデックスを使用してテーブルのデータを並び替える。

  • CLUSTER [VERBOSE] table_name [ USING index_name ]

シーケンス

https://www.postgresql.jp/document/11/html/sql-createsequence.html
https://www.postgresql.jp/document/11/html/sql-dropsequence.html
https://www.postgresql.jp/document/11/html/sql-altersequence.html

  • 作成
    • CREATE SEQUENCE シーケンス名 [オプション];
      • INCREMENT [BY] 増減値(デフォルト:1)
      • MINVALUE 最小値
      • START [WITH] 初期値(デフォルト:1)
  • 削除
    • DROP SEQUENCE シーケンス名;
  • 変更
    • ALTER SEQUENCE シーケンス名;

シーケンスの初期値はデフォルトで1。
シーケンス値にマイナス値を取ることができる。
シーケンスの値は指定した数ずつ減らしながら作成することができる。
nextval()関数で一度進められたシーケンス値は、ロールバックを行っても元に戻らない。
nextval()関数を呼び出す前にcurrval()関数を呼び出すとエラーとなる。

スキーマ

https://www.postgresql.jp/document/11/html/sql-createschema.html
https://www.postgresql.jp/document/11/html/sql-dropschema.html
https://www.postgresql.jp/document/11/html/sql-alterschema.html

データベース内に作成されるテーブルなどのオブジェクトをグループ化する。
データベース[ スキーマ [ テーブル ], ... ] ]
デフォルトで public スキーマが作成される。

テーブルスペース

https://www.postgresql.jp/document/11/html/sql-createtablespace.html
https://www.postgresql.jp/document/11/html/sql-droptablespace.html
https://www.postgresql.jp/document/11/html/sql-altertablespace.html

  • 作成
    • CREATE TABLESPACE テーブルスペース名 [ OWNER { 所有者名 | CURRENT_USER | SESSION_USER } ] LOCATION 'テーブルスペースのパス';
  • 削除
    • DROP TABLESPACE [ IF EXISTS ] テーブルスペース名;
  • 変更
    • ALTER TABLESPACE テーブルスペース名 RENAME TO 新しいテーブルスペース名;
    • ALTER TABLESPACE テーブルスペース名 OWNER TO { new_owner | CURRENT_USER | SESSION_USER };

テーブルスペース(テーブル空間)は、データベースオブジェクトを格納する領域。
LOCATIONで指定するディレクトリ(テーブルスペースのパス)は、事前に作成しておく必要がある。
データベースオブジェクトの作成時に、格納するテーブルスペースを指定することができる。
データベースオブジェクトを各テーブルスペースに振り分けることで、性能を向上させることができる。