?
1.普通方式建立主表
create
table
tbl_partition(
id
integer
,
name
varchar
(
20
),
gender boolean,
join_date date,
dept
char
(
4
)
)
2.創建分區表.(注意加上約束和繼承)
create
table
tbl_partition_201211(
check
( join_date
>=
DATE
'
2012-11-01
'
and
join_date
<
DATE
'
2012-12-01
'
)
)inherits(tbl_partition);
create
table
tbl_partition_201212 (
check
( join_date
>=
DATE
'
2012-12-01
'
AND
join_date
<
DATE
'
2013-01-01
'
)
) INHERITS (tbl_partition);
create
table
tbl_partition_201301 (
check
( join_date
>=
DATE
'
2013-01-01
'
AND
join_date
<
DATE
'
2013-02-01
'
)
) INHERITS (tbl_partition);
create
table
tbl_partition_201302 (
check
( join_date
>=
DATE
'
2013-02-01
'
AND
join_date
<
DATE
'
2013-03-01
'
)
) INHERITS (tbl_partition);
create
table
tbl_partition_201303 (
check
( join_date
>=
DATE
'
2013-03-01
'
AND
join_date
<
DATE
'
2013-04-01
'
)
) INHERITS (tbl_partition);
create
table
tbl_partition_201304 (
check
( join_date
>=
DATE
'
2013-04-01
'
AND
join_date
<
DATE
'
2013-05-01
'
)
) INHERITS (tbl_partition);
create
table
tbl_partition_201305 (
check
( join_date
>=
DATE
'
2013-05-01
'
AND
join_date
<
DATE
'
2013-06-01
'
)
) INHERITS (tbl_partition);
?
3.分區表上建立索引.
create
index
tbl_partition_201211_joindate
on
tbl_partition_201211 (join_date);
create
index
tbl_partition_201212_joindate
on
tbl_partition_201212 (join_date);
create
index
tbl_partition_201301_joindate
on
tbl_partition_201301 (join_date);
create
index
tbl_partition_201302_joindate
on
tbl_partition_201302 (join_date);
create
index
tbl_partition_201303_joindate
on
tbl_partition_201303 (join_date);
create
index
tbl_partition_201304_joindate
on
tbl_partition_201304 (join_date);
create
index
tbl_partition_201305_joindate
on
tbl_partition_201305 (join_date);
?
4.postgresql不能自動插入到字表.所以
要創建觸發器 函數 和創建觸發器.這樣對外插入就不會有字表的感覺.
觸發器函數:
CREATE
OR
REPLACE
FUNCTION
tbl_partition_insert_trigger()
RETURNS
TRIGGER
AS
$$
BEGIN
IF
( NEW.join_date
>=
DATE
'
2012-11-01
'
AND
NEW.join_date
<
DATE
'
2012-12-01
'
)
THEN
INSERT
INTO
tbl_partition_201211
VALUES
(NEW.
*
);
ELSIF ( NEW.join_date
>=
DATE
'
2012-12-01
'
AND
NEW.join_date
<
DATE
'
2013-01-01
'
)
THEN
INSERT
INTO
tbl_partition_201212
VALUES
(NEW.
*
);
ELSIF ( NEW.join_date
>=
DATE
'
2013-01-01
'
AND
NEW.join_date
<
DATE
'
2013-02-01
'
)
THEN
INSERT
INTO
tbl_partition_201301
VALUES
(NEW.
*
);
ELSIF ( NEW.join_date
>=
DATE
'
2013-02-01
'
AND
NEW.join_date
<
DATE
'
2013-03-01
'
)
THEN
INSERT
INTO
tbl_partition_201302
VALUES
(NEW.
*
);
ELSIF ( NEW.join_date
>=
DATE
'
2013-03-01
'
AND
NEW.join_date
<
DATE
'
2013-04-01
'
)
THEN
INSERT
INTO
tbl_partition_201303
VALUES
(NEW.
*
);
ELSIF ( NEW.join_date
>=
DATE
'
2013-04-01
'
AND
NEW.join_date
<
DATE
'
2013-05-01
'
)
THEN
INSERT
INTO
tbl_partition_201304
VALUES
(NEW.
*
);
ELSIF ( NEW.join_date
>=
DATE
'
2013-05-01
'
AND
NEW.join_date
<
DATE
'
2013-06-01
'
)
THEN
INSERT
INTO
tbl_partition_201305
VALUES
(NEW.
*
);
ELSE
RAISE EXCEPTION
'
Date out of range. Fix the tbl_partition_insert_trigger() function!
'
;
END
IF
;
RETURN
NULL
;
END
;
$$
LANGUAGE plpgsql;
觸發器:
CREATE
TRIGGER
insert_tbl_partition_traigger
BEFORE
INSERT
ON
tbl_partition
For
EACH ROW
EXECUTE
PROCEDURE
tbl_partition_insert_trigger();
?
?
?
5.插入數據進行測試.
?
insert
into
tbl_partition
values
(
1
,
'
David
'
,
'
1
'
,
'
2013-01-10
'
,
'
TS
'
);
insert
into
tbl_partition
values
(
2
,
'
Sandy
'
,
'
0
'
,
'
2013-02-10
'
,
'
TS
'
);
insert
into
tbl_partition
values
(
3
,
'
Eagle
'
,
'
1
'
,
'
2012-11-01
'
,
'
TS
'
);
insert
into
tbl_partition
values
(
4
,
'
Miles
'
,
'
1
'
,
'
2012-12-15
'
,
'
SD
'
);
insert
into
tbl_partition
values
(
5
,
'
Simon
'
,
'
1
'
,
'
2012-12-10
'
,
'
SD
'
);
insert
into
tbl_partition
values
(
6
,
'
Rock
'
,
'
1
'
,
'
2012-11-10
'
,
'
SD
'
);
insert
into
tbl_partition
values
(
7
,
'
Peter
'
,
'
1
'
,
'
2013-01-11
'
,
'
SD
'
);
insert
into
tbl_partition
values
(
8
,
'
Sally
'
,
'
0
'
,
'
2013-03-10
'
,
'
BCSC
'
);
insert
into
tbl_partition
values
(
9
,
'
Carrie
'
,
'
0
'
,
'
2013-04-02
'
,
'
BCSC
'
);
insert
into
tbl_partition
values
(
10
,
'
Lee
'
,
'
1
'
,
'
2013-01-05
'
,
'
BMC
'
);
insert
into
tbl_partition
values
(
11
,
'
Nicole
'
,
'
0
'
,
'
2012-11-10
'
,
'
PROJ
'
);
insert
into
tbl_partition
values
(
12
,
'
Renee
'
,
'
0
'
,
'
2013-01-10
'
,
'
TS
'
);
?
?
另: 解除分區方法:
alter
table
tbl_partition_201304 no inherit tbl_partition;
增加分區:
?
?
參考:
PostgreSQL官方說明： http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html
ITEYE： http://diegoball.iteye.com/blog/713826
kenyon(君羊)： http://my.oschina.net/Kenyon/blog/59455
博客園: http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
