【postgreSQL学習ノート】PostgreSQLパーティションテーブル応用
8265 ワード
一、環境
システム環境:Ubuntu 15.04
PostgreSQLバージョン:postgresql-9.4.4
二、メインテーブルの作成
2.1プライマリ・テーブルの作成
2.2パーティション表の作成
2.3パーティション表に索引を作成する
ユーザーにとって、データを挿入するときは、パーティションの他の存在を理解する必要はありません.つまり、テーブルは透明な全体であるべきで、ユーザーはデータを挿入するだけで、区別する必要はありません.では、各パーティション・テーブルにトリガを作成して、アシスト・オペレーションを行う必要があります.
2.4トリガ関数の作成
2.5トリガの作成
三、表の表示
すべてのコンテンツの準備が整いました.各テーブルを確認します.
3.1すべてのテーブルの表示
3.2マスター・テーブルの表示
3.3パーティション表の表示
四、テスト
4.1試験用データの挿入
各学年は3つのクラスで、各クラスには1人の学生のデータがあり、6つの学年には18人の学生のデータがあります.
4.2プライマリ・テーブル・データの再表示
4.3パーティション表のデータを見る6学年ごとに見る
これで、パーティションテーブルが作成されます.データは自動的に各パーティションテーブルに分けられ、管理が容易になります.
システム環境:Ubuntu 15.04
PostgreSQLバージョン:postgresql-9.4.4
二、メインテーブルの作成
2.1プライマリ・テーブルの作成
test=# CREATE TABLE tbl_student(
test(# id integer,
test(# name varchar(20),
test(# grade integer,
test(# class integer,
test(# age integer);
CREATE TABLE
test=#2.2パーティション表の作成
test=# CREATE TABLE tbl_student_grade_1(
test(# CHECK (grade = 1) //
test(# ) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_2(
CHECK (grade = 2) //
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_3(
CHECK (grade = 3) //
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_4(
CHECK (grade = 4) //
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_5(
CHECK (grade = 5) //
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_6(
CHECK (grade = 6) //
) INHERITS (tbl_student);
CREATE TABLE
test=#2.3パーティション表に索引を作成する
test=# CREATE INDEX tbl_student_grade_1_index on tbl_student_grade_1(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_2_index on tbl_student_grade_2(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_3_index on tbl_student_grade_3(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_4_index on tbl_student_grade_4(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_5_index on tbl_student_grade_5(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_6_index on tbl_student_grade_6(grade);
CREATE INDEX
test=#ユーザーにとって、データを挿入するときは、パーティションの他の存在を理解する必要はありません.つまり、テーブルは透明な全体であるべきで、ユーザーはデータを挿入するだけで、区別する必要はありません.では、各パーティション・テーブルにトリガを作成して、アシスト・オペレーションを行う必要があります.
2.4トリガ関数の作成
test=# CREATE OR REPLACE FUNCTION tbl_student_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.grade = 1) THEN INSERT INTO tbl_student_grade_1 VALUES (NEW.*);
ELSEIF (NEW.grade = 2) THEN INSERT INTO tbl_student_grade_2 VALUES (NEW.*);
ELSEIF (NEW.grade = 3) THEN INSERT INTO tbl_student_grade_3 VALUES (NEW.*);
ELSEIF (NEW.grade = 4) THEN INSERT INTO tbl_student_grade_4 VALUES (NEW.*);
ELSEIF (NEW.grade = 5) THEN INSERT INTO tbl_student_grade_5 VALUES (NEW.*);
ELSEIF (NEW.grade = 6) THEN INSERT INTO tbl_student_grade_6 VALUES (NEW.*);
ELSE RAISE EXCEPTION 'Date error,out of range.Fix it and try again';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
test=#2.5トリガの作成
test=# CREATE TRIGGER insert_tbl_student_trigger
test-# BEFORE INSERT ON tbl_student
test-# FOR EACH ROW EXECUTE PROCEDURE tbl_student_insert_trigger();
CREATE TRIGGER
test=#三、表の表示
すべてのコンテンツの準備が整いました.各テーブルを確認します.
3.1すべてのテーブルの表示
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------------+-------+--------------
public | tbl_student | table | highgo-yueyf
public | tbl_student_grade_1 | table | highgo-yueyf
public | tbl_student_grade_2 | table | highgo-yueyf
public | tbl_student_grade_3 | table | highgo-yueyf
public | tbl_student_grade_4 | table | highgo-yueyf
public | tbl_student_grade_5 | table | highgo-yueyf
public | tbl_student_grade_6 | table | highgo-yueyf
(7 rows)
test=#3.2マスター・テーブルの表示
test=# \d tbl_student
Table "public.tbl_student"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
grade | integer |
class | integer |
age | integer |
Triggers:
insert_tbl_student_trigger BEFORE INSERT ON tbl_student FOR EACH ROW EXECUTE PROCEDURE tbl_student_insert_trigger()
Number of child tables: 6 (Use \d+ to list them.)
test=#3.3パーティション表の表示
test=# \d tbl_student_grade_1
Table "public.tbl_student_grade_1"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
grade | integer |
class | integer |
age | integer |
Indexes:
"tbl_student_grade_1_index" btree (grade)
Check constraints:
"tbl_student_grade_1_grade_check" CHECK (grade = 1)
Inherits: tbl_student
test=#四、テスト
4.1試験用データの挿入
各学年は3つのクラスで、各クラスには1人の学生のデータがあり、6つの学年には18人の学生のデータがあります.
test=# INSERT INTO tbl_student VALUES (1, 'David', 1, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (2, 'Jim', 1, 2, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (3, 'Sandy', 1, 3, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (4, 'Candy', 2, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (5, 'Lily', 2, 2, 8);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (6, 'Smith', 2, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (7, 'Pool', 3, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (8, 'Alen', 3, 2, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (9, 'May', 3, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (10, 'Huan', 4, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (11, 'Yue', 4, 2, 8);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (12, 'Kate', 4, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (13, 'Gay', 5, 1, 8);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (14, 'Jane', 5, 2, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (15, 'Wolf', 5, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (16, 'David', 6, 1, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (17, 'David', 6, 2, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (18, 'David', 6, 3, 7);
INSERT 0 0
test=#4.2プライマリ・テーブル・データの再表示
test=# SELECT * FROM tbl_student;
id | name | grade | class | age
----+-------+-------+-------+-----
1 | David | 1 | 1 | 7
2 | Jim | 1 | 2 | 6
3 | Sandy | 1 | 3 | 6
4 | Candy | 2 | 1 | 7
5 | Lily | 2 | 2 | 8
6 | Smith | 2 | 3 | 7
7 | Pool | 3 | 1 | 7
8 | Alen | 3 | 2 | 6
9 | May | 3 | 3 | 7
10 | Huan | 4 | 1 | 7
11 | Yue | 4 | 2 | 8
12 | Kate | 4 | 3 | 7
13 | Gay | 5 | 1 | 8
14 | Jane | 5 | 2 | 6
15 | Wolf | 5 | 3 | 7
16 | David | 6 | 1 | 6
17 | David | 6 | 2 | 7
18 | David | 6 | 3 | 7
(18 rows)
test=#4.3パーティション表のデータを見る6学年ごとに見る
test=# SELECT * FROM tbl_student_grade_1;
id | name | grade | class | age
----+-------+-------+-------+-----
1 | David | 1 | 1 | 7
2 | Jim | 1 | 2 | 6
3 | Sandy | 1 | 3 | 6
(3 rows)
test=# SELECT * FROM tbl_student_grade_2;
id | name | grade | class | age
----+-------+-------+-------+-----
4 | Candy | 2 | 1 | 7
5 | Lily | 2 | 2 | 8
6 | Smith | 2 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_3;
id | name | grade | class | age
----+------+-------+-------+-----
7 | Pool | 3 | 1 | 7
8 | Alen | 3 | 2 | 6
9 | May | 3 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_4;
id | name | grade | class | age
----+------+-------+-------+-----
10 | Huan | 4 | 1 | 7
11 | Yue | 4 | 2 | 8
12 | Kate | 4 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_5;
id | name | grade | class | age
----+------+-------+-------+-----
13 | Gay | 5 | 1 | 8
14 | Jane | 5 | 2 | 6
15 | Wolf | 5 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_6;
id | name | grade | class | age
----+-------+-------+-------+-----
16 | David | 6 | 1 | 6
17 | David | 6 | 2 | 7
18 | David | 6 | 3 | 7
(3 rows)
test=#これで、パーティションテーブルが作成されます.データは自動的に各パーティションテーブルに分けられ、管理が容易になります.