テーブルの定義
パーティション
シーケンス
ビュー
インデックス
トリガ
- トリガの作成
ルール
- ルールの作成
スキーマ
- スキーマの作成
- スキーマへのテーブルの作成とスキーマ検索パス
関数とプロシージャ
テーブルスペース
マテリアライズドビュー

テーブルの定義

テーブルの作成と削除

CREATE TABLE also automatically creates a data type that represents the composite type
corresponding to one row of the table. Therefore, tables cannot have the same name as any
existing data type in the same schema.

【補】タプル自体が型なので、データ型と名前が衝突する
【補】[USAGE権限]がGRANTされている必要あり
- いろいろなデータベースオブジェクトを作れたりする権限
  - 手続きとか
  - 型とか
- SQL標準由来
作ってみる

postgres=# CREATE TABLE tbl(
postgres(# c1 int DEFAULT 10,
postgres(# c2 text,
postgres(# c3 text DEFAULT 'ABC',
postgres(# c4 timestamp DEFAULT now()
postgres(# );
CREATE TABLE


postgres=# \d tbl


                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           |          | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()


postgres=# INSERT INTO tbl(c2) VALUES('test');
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 |  c2  | c3  |             c4             
----+------+-----+----------------------------
 10 | test | ABC | 2020-01-23 13:37:39.033761
(1 row)

制約

主キー(PRIMARY KEY)制約

追加

postgres=# ALTER TABLE tbl ADD PRIMARY KEY (c1);

ALTER TABLE
\d tbl;
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (c1)

落とす

postgres=# ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;
ALTER TABLE

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()

名前pkeyを指定して主キー制約を追加する

postgres=# ALTER TABLE tbl ADD CONSTRAINT pkey PRIMARY KEY(c1);

ALTER TABLE

postgres=# \d tbl
\d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)

ユニーク制約

追加

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "tbl_c2_key" UNIQUE CONSTRAINT, btree (c2)

落とす

postgres=# ALTER TABLE tbl DROP CONSTRAINT tbl_c2_key;
ALTER TABLE

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)

名前指定して追加

postgres=# ALTER TABLE tbl ADD CONSTRAINT unq_c2 UNIQUE(c2);
ALTER TABLE

postgres=# \d tbl;
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "unq_c2" UNIQUE CONSTRAINT, btree (c2)

NOT NULL制約

【補】公式/カラムのconstraint
- 制約名がついてない
- のでALTER TABLE <テーブル名> DROP CONSTRAINT <制約名> では落とせない
つけはずし

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           | not null | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "unq_c2" UNIQUE CONSTRAINT, btree (c2)

postgres=# ALTER TABLE tbl ALTER COLUMN c2 DROP NOT NULL;
ALTER TABLE

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "unq_c2" UNIQUE CONSTRAINT, btree (c2)

外部キー制約

参照整合性制約とも
primary key もしくは unique 制約のある列を参照できる
- 【補】nullable
テーブル生成時に指定してみる

postgres=# CREATE TABLE master(id int PRIMARY KEY);
CREATE TABLE

postgres=# CREATE TABLE tbl(id int REFERENCES master(id));
CREATE TABLE


postgres=# \d master
               Table "public.master"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "master_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "tbl" CONSTRAINT "tbl_id_fkey" FOREIGN KEY (id) REFERENCES master(id)


postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
Foreign-key constraints:
    "tbl_id_fkey" FOREIGN KEY (id) REFERENCES master(id)

データ挿入してみる

postgres=# INSERT INTO master VALUES(1);
INSERT INTO master VALUES(1);
INSERT 0 1

postgres=# INSERT INTO tbl VALUES(1);
INSERT INTO tbl VALUES(1);
INSERT 0 1

postgres=# INSERT INTO tbl VALUES(2);
INSERT INTO tbl VALUES(2);
ERROR:  insert or update on table "tbl" violates foreign key constraint "tbl_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "master".


postgres=# SELECT * FROM master;
 id 
----
  1
(1 row)

postgres=# SELECT * FROM tbl;
 id 
----
  1
(1 row)

【補】複数行INSERT時、一部の行がFK制約に違反した場合、全部失敗する
- Atomicity

postgres=# INSERT INTO tbl VALUES(1),(2);
ERROR:  insert or update on table "tbl" violates foreign key constraint "tbl_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "master".


postgres=# SELECT * FROM tbl;
SELECT * FROM tbl;
 id 
----
  1
(1 row)

制約をDROPして、CASCADEでつけなおす

postgres=# ALTER TABLE tbl DROP CONSTRAINT tbl_id_fkey;
ALTER TABLE

postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 



postgres=# ALTER TABLE tbl ADD CONSTRAINT tbl_id_fkey_cascade FOREIGN KEY(id) REFERENCES master(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE

postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
Foreign-key constraints:
    "tbl_id_fkey_cascade" FOREIGN KEY (id) REFERENCES master(id) ON UPDATE CASCADE ON DELETE CASCADE

更新してみる
- 子テーブルの更新は制約違反
- 親テーブルの更新は波及する

postgres=# UPDATE tbl SET id = 2 WHERE id = 1;
ERROR:  insert or update on table "tbl" violates foreign key constraint "tbl_id_fkey_cascade"
DETAIL:  Key (id)=(2) is not present in table "master".

postgres=# UPDATE master SET id = 2 WHERE id = 1;
UPDATE 1
postgres=# 


postgres=# SELECT * FROM master;
 id 
----
  2
(1 row)

postgres=# SELECT * FROM tbl;
 id 
----
  2
(1 row)

削除
- 親テーブルの行削除は子に波及する

postgres=# DELETE FROM master WHERE id = 2;
DELETE 1


postgres=# SELECT * FROM master;
 id 
----
(0 rows)

postgres=# SELECT * FROM tbl;
 id 
----
(0 rows)

【補】親テーブルDROP
怒られる

postgres=# DROP table master;
ERROR:  cannot drop table master because other objects depend on it
DETAIL:  constraint tbl_id_fkey_cascade on table tbl depends on table master
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

子テーブルのFK制約もDROPするには、CASCADEを指定する

postgres=# DROP table master CASCADE;
NOTICE:  drop cascades to constraint tbl_id_fkey_cascade on table tbl
DROP TABLE

子テーブル自体は残る

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tbl  | table | postgres
(1 row)

FK制約はDROPされる

postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          |

チェック制約

偶数のみ受け入れるカラムを定義してみる

postgres=# CREATE TABLE tbl(even int CHECK(mod(even, 2) = 0));
CREATE TABLE


postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 even   | integer |           |          | 
Check constraints:
    "tbl_even_check" CHECK (mod(even, 2) = 0)

制約が効くことを確認

postgres=# INSERT INTO tbl (even) VALUES (2);
INSERT 0 1

postgres=#  INSERT INTO tbl (even) VALUES (3);
ERROR:  new row for relation "tbl" violates check constraint "tbl_even_check"
DETAIL:  Failing row contains (3).

ドメイン制約

チェック制約を再利用するためのやつ

postgres=# CREATE DOMAIN even AS int CHECK (mod(VALUE, 2) = 0);
CREATE DOMAIN

postgres=# CREATE TABLE tbl (id int, num even);
CREATE TABLE

postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 num    | even    |           |          |

DROP
使われているものをDROPしようとすると怒られる

postgres=# DROP DOMAIN even;
ERROR:  cannot drop type even because other objects depend on it
DETAIL:  column num of table tbl depends on type even
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


postgres=# DROP DOMAIN even CASCADE;
NOTICE:  drop cascades to column num of table tbl
DROP DOMAIN


postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          |

カラムごと消えた

テーブル定義の変更

テーブルの
- 名前変更
- 所有者変更
列の
- 名前変更
- 追加
- 削除
制約も追加・削除できる(先述)

パーティション

公式
テーブルを水平分割
利点
- 読み出しパフォーマンス向上
  - アクセス対象の絞り込み
- 更新パフォーマンス向上
- 大量データの追加・削除
  - partitionの付け外しで実現できる
- アクセス頻度の低いデータを遅くて安いストレージに置ける
作ってみる

postgres=# CREATE TABLE tbl (c1 serial, c2 text, c3 date) PARTITION BY range (c3);
CREATE TABLE


postgres=# \d tbl
                      Partitioned table "public.tbl"
 Column |  Type   | Collation | Nullable |             Default             
--------+---------+-----------+----------+---------------------------------
 c1     | integer |           | not null | nextval('tbl_c1_seq'::regclass)
 c2     | text    |           |          | 
 c3     | date    |           |          | 
Partition key: RANGE (c3)
Number of partitions: 0

【補】partitionのない状態でデータを投入しようとするとエラー

postgres=# INSERT INTO tbl(c2, c3) VALUES ('hoge', '2020-01-01');
ERROR:  no partition of relation "tbl" found for row
DETAIL:  Partition key of the failing row contains (c3) = (2020-01-01).

パーティション切る

postgres=# CREATE TABLE tbl_y2020m04 PARTITION OF tbl FOR VALUES FROM ('2020-04-01') TO ('2020-04-30');
CREATE TABLE
postgres=# CREATE TABLE tbl_y2020m05 PARTITION OF tbl FOR VALUES FROM ('2020-05-01') TO ('2020-05-31');
CREATE TABLE
postgres=# CREATE TABLE tbl_y2020m06 PARTITION OF tbl FOR VALUES FROM ('2020-06-01') TO ('2020-06-30');
CREATE TABLE


postgres=# \d
                  List of relations
 Schema |     Name     |       Type        |  Owner   
--------+--------------+-------------------+----------
 public | tbl          | partitioned table | postgres
 public | tbl_c1_seq   | sequence          | postgres
 public | tbl_y2020m04 | table             | postgres
 public | tbl_y2020m05 | table             | postgres
 public | tbl_y2020m06 | table             | postgres
(5 rows)


postgres=# \d+ tbl
                                          Partitioned table "public.tbl"
 Column |  Type   | Collation | Nullable |             Default             | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------------------------------+----------+--------------+-------------
 c1     | integer |           | not null | nextval('tbl_c1_seq'::regclass) | plain    |              | 
 c2     | text    |           |          |                                 | extended |              | 
 c3     | date    |           |          |                                 | plain    |              | 
Partition key: RANGE (c3)
Partitions: tbl_y2020m04 FOR VALUES FROM ('2020-04-01') TO ('2020-04-30'),
            tbl_y2020m05 FOR VALUES FROM ('2020-05-01') TO ('2020-05-31'),
            tbl_y2020m06 FOR VALUES FROM ('2020-06-01') TO ('2020-06-30')



postgres=# \d tbl_y2020m04
                        Table "public.tbl_y2020m04"
 Column |  Type   | Collation | Nullable |             Default             
--------+---------+-----------+----------+---------------------------------
 c1     | integer |           | not null | nextval('tbl_c1_seq'::regclass)
 c2     | text    |           |          | 
 c3     | date    |           |          | 
Partition of: tbl FOR VALUES FROM ('2020-04-01') TO ('2020-04-30')

パーティション範囲外のデータをINSERTすると怒られる

postgres=# INSERT INTO tbl(c2, c3) VALUES('hoge', '2019-01-01');
ERROR:  no partition of relation "tbl" found for row
DETAIL:  Partition key of the failing row contains (c3) = (2019-01-01).

パーティション範囲内のデータをINSERT

postgres=# SELECT * FROM tbl;
 c1 | c2 |     c3     
----+----+------------
  3 | GW | 2020-05-03
(1 row)


postgres=# SELECT * FROM tbl_y2020m05;
 c1 | c2 |     c3     
----+----+------------
  3 | GW | 2020-05-03
(1 row)

小分けのテーブル側に範囲外データをINSERTするとpartition constraint違反でエラー

postgres=# INSERT INTO tbl_y2020m05(c2, c3) VALUES('hoge', '2019-01-01');
ERROR:  new row for relation "tbl_y2020m05" violates partition constraint
DETAIL:  Failing row contains (4, hoge, 2019-01-01).

【補】分割元をDROPすると小分けのテーブルも容赦なく消える

postgres=# \dt
                  List of relations
 Schema |     Name     |       Type        |  Owner   
--------+--------------+-------------------+----------
 public | tbl          | partitioned table | postgres
 public | tbl_y2020m04 | table             | postgres
 public | tbl_y2020m05 | table             | postgres
 public | tbl_y2020m06 | table             | postgres
(4 rows)

postgres=# DROP TABLE tbl;
DROP TABLE

postgres=# \dt
Did not find any relations.

シーケンス

公式

postgres=# CREATE SEQUENCE even_seq START 2 INCREMENT BY 2 MAXVALUE 10 MINVALUE 0;
INCREMENT BY 2 MAXVALUE 10 MINVALUE 0;
CREATE SEQUENCE


postgres=# \ds+ even_seq
                         List of relations
 Schema |   Name   |   Type   |  Owner   |    Size    | Description 
--------+----------+----------+----------+------------+-------------
 public | even_seq | sequence | postgres | 8192 bytes | 
(1 row)

【補】状態確認

postgres=# SELECT * FROM even_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          2 |       0 | f
(1 row)

値読み出し

postgres=# SELECT currval('even_seq');
ERROR:  currval of sequence "even_seq" is not yet defined in this session

一度も呼び出していない (is_calledがf) 状態だとエラー
値セット

postgres=# SELECT setval('even_seq', 2);
 setval 
--------
      2
(1 row)


postgres=# SELECT * from even_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          2 |       0 | t
(1 row)

is_calledがtになった
再度値読み出し
- 冪等

postgres=# SELECT currval('even_seq');
 currval 
---------
       2
(1 row)


postgres=# SELECT currval('even_seq');
 currval 
---------
       2
(1 row)

次の値払い出し・返却

postgres=# SELECT nextval('even_seq');
 nextval 
---------
       4
(1 row)

postgres=# SELECT nextval('even_seq');
 nextval 
---------
       6
(1 row)

postgres=# SELECT nextval('even_seq');
 nextval 
---------
       8
(1 row)

postgres=# SELECT nextval('even_seq');
 nextval 
---------
      10
(1 row)

postgres=# SELECT nextval('even_seq');
ERROR:  nextval: reached maximum value of sequence "even_seq" (10)

ビュー

postgres=# CREATE TABLE numbers(num int);
CREATE TABLE

postgres=# INSERT INTO numbers VALUES(1),(2),(3);
INSERT 0 3


postgres=# CREATE VIEW squared AS SELECT num * num FROM numbers;
CREATE VIEW

postgres=# SELECT * FROM squared;
 ?column? 
----------
        1
        4
        9
(3 rows)


postgres=# \dv
\dv
         List of relations
 Schema |  Name   | Type |  Owner   
--------+---------+------+----------
 public | squared | view | postgres
(1 row)

DROP VIEWで落とす

インデックス

【補】SQL標準にインデックスに関する規定はない

インデックスの概要

さくいん

インデックスの種類

公式

PostgreSQL provides the index methods B-tree, hash, GiST, SP-GiST, GIN, and BRIN. Users can also
define their own index methods, but that is fairly complicated.

B-Tree
- 一般的に使われるやつ
- スカラの一致、大小不等号検索
  - 【補】NOT EQUALには使えない
SP-GiST
- 公式
- Space-Partitioned GiST
GiST
- 公式
- Generalized Search Tree
  - B木とかもこれで実装されうる
  - いろいろなデータ型を検索できる
  - 公式
- 主に空間情報の検索に用いられる
GIN
- Generalized Inverted Index
- 全文検索とかで使う
ハッシュ
- 値の一致検索がはやいやつ
BRIN
- 公式
- Block Range Index
- ビッグデータの検索とかに使うやつ

インデックスの作成

postgres=# CREATE TABLE tbl (c1 int, c2 text[], c3 point);
CREATE TABLE

postgres=# CREATE INDEX tbl_c1_index ON tbl (c1);
CREATE INDEX
postgres=# CREATE INDEX tbl_c2_index ON tbl USING gin (c2);
CREATE INDEX
postgres=# CREATE INDEX tbl_c3_index ON tbl USING gist (c3);
CREATE INDEX

postgres=# \di
                List of relations
 Schema |     Name     | Type  |  Owner   | Table 
--------+--------------+-------+----------+-------
 public | tbl_c1_index | index | postgres | tbl
 public | tbl_c2_index | index | postgres | tbl
 public | tbl_c3_index | index | postgres | tbl
(3 rows)

【補】intに対してginとかgistとか作ろうとすると「演算子ねーぞ」と怒られる
DROP INDEXで消す
- テーブルを消しても消える

マルチカラムインデックス

postgres=# CREATE TABLE tbl2 (c1 int, c2 int);
CREATE TABLE

postgres=# CREATE INDEX multi_idx ON tbl(c1,c2);
CREATE INDEX

postgres=# CREATE INDEX multi_idx ON tbl USING hash (c1,c2);
ERROR:  access method "hash" does not support multicolumn indexes

ハッシュ方式の複合インデックスは作れない

関数インデックス/式インデックス

式や関数の結果をインデックス化できる

postgres=# CREATE INDEX added_index ON tbl2 ((c1 + c2));
CREATE INDEX

postgres=# CREATE INDEX squared_index_1 ON tbl2 (pow(c1,2));
CREATE INDEX

部分インデックス

テーブルの特定の範囲のデータのみにインデックスを張る

postgres=# CREATE INDEX partial_index ON tbl (c1) WHERE c1 < 100;
CREATE INDEX

トリガ

挿入・更新・削除時にユーザ定義関数/プロシージャを実行

トリガの作成

公式

Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

URL: https://www.postgresql.org/docs/12/sql-createtrigger.html

When	Event	Row-level	Statement-level
BEFORE	INSERT/UPDATE/DELETE	Tables and foreign tables	Tables, views, and foreign tables
	TRUNCATE	-	Tables
AFTER	INSERT/UPDATE/DELETE	Tables and foreign tables	Tables, views, and foreign tables
	TRUNCATE	-	Tables
INSTEAD OF	INSERT/UPDATE/DELETE	Views	-
	TRUNCATE	-	-

SQL実行ごと/行ごと
いつ実行する
- CUD前
- CUD後
- かわりに何かする
  - Viewの行ごとでのみ

CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE FUNCTION view_insert_row();

ルール

ルールの作成

Command:     CREATE RULE
Description: define a new rewrite rule
Syntax:
CREATE [ OR REPLACE ] RULE name AS ON event
    TO table_name [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

where event can be one of:

    SELECT | INSERT | UPDATE | DELETE

URL: https://www.postgresql.org/docs/12/sql-createrule.html

SQLの置き換え(INSTEAD) or 別の処理を付け加える(ALSO)

スキーマ

名前空間とも
【補】だから\dnメタコマンド

スキーマの作成

Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/12/sql-createschema.html

所有者シャロちゃんでスキーマ作成

postgres=# CREATE USER syaro;
CREATE ROLE

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}


postgres=# CREATE SCHEMA my_schema AUTHORIZATION syaro;
CREATE SCHEMA


postgres=# \dn
   List of schemas
   Name    |  Owner   
-----------+----------
 my_schema | syaro
 public    | postgres
(2 rows)

【補】schema_elementで、スキーマに含められるデータベースオブジェクトを指定する

スキーマへのテーブルの作成とスキーマ検索パス

同名のデータベースオブジェクト(テーブルとか)が複数のスキーマに存在する場合の特定方法
- データベースオブジェクトをスキーマ名で修飾する
  - インデックスは特定のスキーマ名で修飾できない
    - テーブルと一緒
- スキーマ検索パスを指定する
スキーマ検索パス: search_path パラメータ

postgres=# SELECT name,setting,context FROM pg_settings WHERE name = 'search_path';
pg_settings WHERE name = 'search_path';
    name     |     setting     | context 
-------------+-----------------+---------
 search_path | "$user", public | user
(1 row)

デフォルト値: "$user", public
- 現在のデータベースユーザ名と同名のスキーマを優先
一般ユーザのSET文で更新できる

postgres=# CREATE TABLE public.tbl(col int);
CREATE TABLE

postgres=# CREATE TABLE my_schema.tbl(col int);
CREATE TABLE


postgres=# INSERT INTO public.tbl VALUES (1);
INSERT 0 1

postgres=# INSERT INTO my_schema.tbl VALUES (715);
INSERT 0 1


postgres=# SELECT * FROM tbl;
 col 
-----
   1
(1 row)

postgres=# SET search_path = 'my_schema';
SET

postgres=# SELECT * FROM tbl;
 col 
-----
 715
(1 row)

関数とプロシージャ

利点
- アプリケーション側での複雑な処理をDBMS側に任せられる
- ネットワークの負荷軽減

関数とプロシージャの違い

returnの有無
関数はSELECT、プロシージャはCALLで呼ぶ
プロシージャ内ではトランザクションのCOMMIT/ROLLBACKができる
- 関数ではできない

postgres=# CALL nextval('hoge');
CALL nextval('hoge');
ERROR:  nextval(unknown) is not a procedure
LINE 1: CALL nextval('hoge');
             ^
HINT:  To call a function, use SELECT.

関数定義

関数の構造

テーブルスペース

データベースオブジェクトの物理的な格納先を設定する
- 高性能なストレージを使う
- 複数のテーブルスペースに分散してアクセス効率を上げる
【補】公式
- シンボリックリンクのサポートが必要
- トランザクション内では実行できない

postgres=# SELECT name,setting,context FROM pg_settings WHERE name ~ 'tablespace';
        name        | setting | context 
--------------------+---------+---------
 default_tablespace |         | user
 temp_tablespaces   |         | user
(2 rows)

一般ユーザのSET文で変更可能
テーブルスペース作る
ディレクトリがないと怒られる

postgres=# CREATE TABLESPACE tblspc1 LOCATION '/var/lib/postgresql/data/tablespace';
N '/var/lib/postgresql/data/tablespace';
WARNING:  tablespace location should not be inside the data directory
ERROR:  directory "/var/lib/postgresql/data/tablespace" does not exist

700でディレクトリ作る

$ mkdir /var/lib/postgresql/data/tablespace

$ ls -ld /var/lib/postgresql/data/tablespace
drwxr-xr-x 2 postgres postgres 4096 Jan 23 17:06 /var/lib/postgresql/data/tablespace

$ chmod 700 /var/lib/postgresql/data/tablespace

postgres=# CREATE TABLESPACE tblspc1 LOCATION '/var/lib/postgresql/data/tablespace';
WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE


postgres=# SELECT * FROM pg_tablespace;
SELECT * FROM pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 16513 | tblspc1    |       10 |        | 
(3 rows)

テーブルスペースの場所確認

postgres=# SELECT pg_tablespace_location(16513);

       pg_tablespace_location        
-------------------------------------
 /var/lib/postgresql/data/tablespace
(1 row)

マテリアライズドビュー

実体を持つビュー
- 即時性はない
- インデックス持てる
つくる

postgres=# CREATE TABLE tbl (number int);
CREATE TABLE

postgres=# CREATE MATERIALIZED VIEW squared AS SELECT number * number FROM tbl;
SELECT 0

テーブル側にデータ追加

postgres=# INSERT INTO tbl VALUES(1),(2),(3);
INSERT 0 3

postgres=# SELECT * FROM tbl;
 number 
--------
      1
      2
      3
(3 rows)

マテリアライズドビュー側には反映されない

postgres=# SELECT * FROM squared;
 ?column? 
----------
(0 rows)

必要に応じて更新を行う必要がある

postgres=# REFRESH MATERIALIZED VIEW squared;
REFRESH MATERIALIZED VIEW

postgres=# SELECT * FROM squared;
 ?column? 
----------
        1
        4
        9
(3 rows)

勉強日記

チラ裏

OSS-DB試験対策緑本 ch8 (2/2)

テーブルの定義

テーブルの作成と削除

制約

主キー(PRIMARY KEY)制約

ユニーク制約

NOT NULL制約

外部キー制約

チェック制約

ドメイン制約

テーブル定義の変更

パーティション

シーケンス

ビュー

インデックス

インデックスの概要

インデックスの種類

インデックスの作成

マルチカラムインデックス

関数インデックス/式インデックス

部分インデックス

トリガ

トリガの作成

ルール

ルールの作成

スキーマ

スキーマの作成

スキーマへのテーブルの作成とスキーマ検索パス

関数とプロシージャ

関数とプロシージャの違い

関数定義

関数の構造

テーブルスペース

マテリアライズドビュー