テーブルの定義
テーブルの作成と削除
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制約
-
- 制約名がついてない
- ので
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
-
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)