本篇文章给大家分享的是有关PostgreSQL 10中如何使用分区表，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

分区介绍

PostgreSQL的分区需要先建立主表，然后再建立子表，使用继承的特性，但不需要手动写触发器/规则了，目前支持range、list分区，10 正式版发布时不知道会不会有其他的，后面我会介绍我基于10 Beta2添加的hash分区。

range分区

分区语法：

postgres=#createtabler(r_idint,r_namename,r_datedate)partitionbyrange(r_id);CREATETABLEpostgres=#createtabler1partitionofrforvaluesfrom(1)to(10);CREATETABLEpostgres=#createtabler2partitionofrforvaluesfrom(10)to(20);CREATETABLEpostgres=#createtabler3partitionofrforvaluesfrom(20)to(30);CREATETABLEpostgres=#insertintorselectid,md5(random()::text),now()+(id||'day')::intervalfromgenerate_series(1,29)t(id);INSERT029postgres=#select*,tableoid::regclassfromr;r_id|r_name|r_date|tableoid------+----------------------------------+------------+----------1|1d0d0680930198d2962b3b5f9cf82083|2017-08-09|r12|47ba81de41d71bd51b18c7861a594bdf|2017-08-10|r13|820b0b1affe3bf0e5705aee3e77b0b29|2017-08-11|r14|0cc06451bd0652d2583a733374d787b3|2017-08-12|r15|642108381b2fc203b830f1215a0d7c6a|2017-08-13|r16|57e3869b2ab8ee1c0bca96b1cf022a5d|2017-08-14|r17|5357fa6de3c1c559edb78cddb4eae902|2017-08-15|r18|6ea5a7dba4dfc6c81ca5932be86a9341|2017-08-16|r19|d3d4dcb9dc48e0629042ede7ed9c7a33|2017-08-17|r110|248d6f3e072c6c137a3402d11fc5b1d7|2017-08-18|r211|ae3a671045ded43260bc4d0bbcb7e428|2017-08-19|r212|acdc89bb326d9f0caaeeb86bfeac3a76|2017-08-20|r213|147b6e975d7299db66e170874b913b25|2017-08-21|r214|6041a6b84b1af615bdb34a5926d72a33|2017-08-22|r215|3d96e08395af120dd36e10a0252ce29c|2017-08-23|r216|5e613d10c9cac126453413ddfc17c210|2017-08-24|r217|e92fc34d180be652e72a63b92d327f1b|2017-08-25|r218|3109c4e8f4da701721151df11a4d266f|2017-08-26|r219|35ba5892f3b88aa3254445fbf5267eea|2017-08-27|r220|c92d1df47257784bb11d7bfbb52b5710|2017-08-28|r321|d076a5498d17ade8f317bf47cfa322c3|2017-08-29|r322|a66c2e83f1e54e1392964ed71d5b8e20|2017-08-30|r323|6a94df0f08921728aa0af9455d05c9f8|2017-08-31|r324|248c46d80b926c66c093c500f309614d|2017-09-01|r325|4da3be147fd1831e8605fc400e7a7503|2017-09-02|r326|3029d7e22b7c963e8983200a93894669|2017-09-03|r327|720d6d04249e9f3595a19cf59f075332|2017-09-04|r328|95b5e5492591c38ddd864d83265e26c4|2017-09-05|r329|2628c14bd3f67699ab0411b6fd402460|2017-09-06|r3(29rows)postgres=#explainselect*fromrwhereid=20;ERROR:column"id"doesnotexistLINE1:explainselect*fromrwhereid=20;^postgres=#explainselect*fromrwherer_id=20;QUERYPLAN----------------------------------------------------------Append(cost=0.00..20.12rows=4width=72)->SeqScanonr3(cost=0.00..20.12rows=4width=72)Filter:(r_id=20)(3rows)postgres=#setconstraint_exclusion=off;SETpostgres=#explainselect*fromrwherer_id=20;QUERYPLAN----------------------------------------------------------Append(cost=0.00..60.38rows=12width=72)->SeqScanonr1(cost=0.00..20.12rows=4width=72)Filter:(r_id=20)->SeqScanonr2(cost=0.00..20.12rows=4width=72)Filter:(r_id=20)->SeqScanonr3(cost=0.00..20.12rows=4width=72)Filter:(r_id=20)(7rows)postgres=#postgres=#createindexonr1(r_id);CREATEINDEXpostgres=#explainselect*fromrwherer_id=5;QUERYPLAN----------------------------------------------------------------------------------Append(cost=5.53..25.54rows=161width=72)->BitmapHeapScanonr1(cost=5.53..25.54rows=161width=72)RecheckCond:(r_id=5)->BitmapIndexScanonr1_r_id_idx(cost=0.00..5.48rows=161width=0)IndexCond:(r_id=5)(5rows)postgres=#\d+r*Table"public.r"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------r_id|integer||||plain||r_name|name||||plain||r_date|date||||plain||Partitionkey:RANGE(r_id)Partitions:r1FORVALUESFROM(1)TO(10),r2FORVALUESFROM(10)TO(20),r3FORVALUESFROM(20)TO(30)Table"public.r1"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------r_id|integer||||plain||r_name|name||||plain||r_date|date||||plain||Partitionof:rFORVALUESFROM(1)TO(10)Partitionconstraint:((r_idISNOTNULL)AND(r_id>=1)AND(r_id<10))Indexes:"r1_r_id_idx"btree(r_id)Index"public.r1_r_id_idx"Column|Type|Definition|Storage--------+---------+------------+---------r_id|integer|r_id|plainbtree,fortable"public.r1"Table"public.r2"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------r_id|integer||||plain||r_name|name||||plain||r_date|date||||plain||Partitionof:rFORVALUESFROM(10)TO(20)Partitionconstraint:((r_idISNOTNULL)AND(r_id>=10)AND(r_id<20))Table"public.r3"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------r_id|integer||||plain||r_name|name||||plain||r_date|date||||plain||Partitionof:rFORVALUESFROM(20)TO(30)Partitionconstraint:((r_idISNOTNULL)AND(r_id>=20)AND(r_id<30))

说明：

创建分区时必须指定主表

分区表和主表列数量、定义必须完全一致

分区表的列可以单独添加约束、索引

向主表插入数据，自动插入到对应分区，如果找不到对应分区，抛出错误

range分区范围>=最小值、<***值

修改主表字段名、字段类型，会自动修改所有分区

truncate主表会清除所有分区表数据

drop主表会把所有子表一起drop

\d、\d+ 可查看分区表详细定义

在PostgreSQL10的分区表功能中， 范围分区的KEY支持由多个字段组成，多列组成的KEY可看做是范围分区表的组合约束。

[postgres@localhostbin]$./psqlpsql(10beta2)Type"help"forhelp.postgres=#createtabler(aint,bint)partitionbyrange(a,b);CREATETABLEpostgres=#createtabler1partitionofrforvaluesfrom(1,60)to(10,80);CREATETABLEpostgres=#createtabler2partitionofrforvaluesfrom(10,80)to(20,60);CREATETABLEpostgres=#\d+r*Table"public.r"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------a|integer||||plain||b|integer||||plain||Partitionkey:RANGE(a,b)Partitions:r1FORVALUESFROM(1,60)TO(10,80),r2FORVALUESFROM(10,80)TO(20,60)Table"public.r1"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------a|integer||||plain||b|integer||||plain||Partitionof:rFORVALUESFROM(1,60)TO(10,80)Partitionconstraint:((aISNOTNULL)AND(bISNOTNULL)AND((a>1)OR((a=1)AND(b>=60)))AND((a<10)OR((a=10)AND(b<80))))Table"public.r2"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------a|integer||||plain||b|integer||||plain||Partitionof:rFORVALUESFROM(10,80)TO(20,60)Partitionconstraint:((aISNOTNULL)AND(bISNOTNULL)AND((a>10)OR((a=10)AND(b>=80)))AND((a<20)OR((a=20)AND(b<60))))postgres=#insertintorvalues(10,70);INSERT01postgres=#insertintorvalues(10,80);INSERT01postgres=#insertintorvalues(10,90);INSERT01postgres=#selecttableoid::regclass,*fromr;tableoid|a|b----------+----+----r1|10|70r2|10|80r2|10|90(7rows)postgres=#

这里需要注意它的分区约束，from (10, 80) to (20, 60)，最初还以为是有bug，其实不是，Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 10) OR ((a = 10) AND (b >= 80))) AND ((a < 20) OR ((a = 20) AND (b < 60))))

list分区

语法：

postgres=#createtablel(l_idint,l_namename,l_datedate)partitionbylist(l_id);CREATETABLEpostgres=#createtablel1partitionoflforvaluesin(1);CREATETABLEpostgres=#createtablel2partitionoflforvaluesin(2);CREATETABLEpostgres=#createtablel3partitionoflforvaluesin(3);CREATETABLEpostgres=#createtablel4partitionoflforvaluesin(4);CREATETABLEpostgres=#insertintolselectid,md5(random()::text),now()+(id||'day')::intervalfromgenerate_series(1,5)t(id);ERROR:nopartitionofrelation"l"foundforrowDETAIL:Partitionkeyofthefailingrowcontains(l_id)=(5).postgres=#insertintolselectid,md5(random()::text),now()+(id||'day')::intervalfromgenerate_series(1,4)t(id);INSERT04postgres=#explainselect*fromlwherel_id=2;QUERYPLAN----------------------------------------------------------Append(cost=0.00..20.12rows=4width=72)->SeqScanonl2(cost=0.00..20.12rows=4width=72)Filter:(l_id=2)(3rows)postgres=#

hash分区

语法：

[postgres@localhostbin]$./psqlyonj1epsql(10beta2)Type"help"forhelp.yonj1e=#createtableh(h_idint,h_namename,h_datedate)partitionbyhash(h_id);CREATETABLEyonj1e=#createtableh2partitionofh;CREATETABLEyonj1e=#createtableh3partitionofh;CREATETABLEyonj1e=#createtableh4partitionofh;CREATETABLEyonj1e=#createtableh5partitionofh;CREATETABLEyonj1e=#insertintohselectid,md5(random()::text),now()+(id||'day')::intervalfromgenerate_series(1,50)t(id);INSERT050yonj1e=#select*,tableoid::regclassfromh;h_id|h_name|h_date|tableoid------+----------------------------------+------------+----------5|21fe9a616ce20868769904bbda56aa3e|2017-08-14|h26|8fa0f42bf4239c05c1cd46a814f71eaa|2017-08-15|h28|858e324311506fb5c5000a4741b9af3c|2017-08-17|h212|ef4ce7a0f6168605a7c243a709f28bc3|2017-08-21|h213|2273522a7b3c286e214a8f57e010568e|2017-08-22|h217|8bca453f60f13278d3a02149b30394d2|2017-08-26|h219|0c2f14a6a8e675341b4e7bdb6ed161de|2017-08-28|h223|f10fff43558393b577d417127bf6a163|2017-09-01|h226|1dd0851728458b67a053d500bbb837ae|2017-09-04|h228|00f67b8636b4d225d3b62bcca9c6d527|2017-09-06|h240|d3a217d39b6808ff5e37ed3977513e05|2017-09-18|h241|0f4c765d809c3db3fa608e986aed1247|2017-09-19|h242|022ff983201352092d5d7cb735e9f531|2017-09-20|h244|c3dba31501b3625aac7f3d4f41512855|2017-09-22|h249|21c697e92f936982840b928e03151204|2017-09-27|h211|625ad3b0c9d40f7cae26be84a7ae054d|2017-08-20|h314|7ee39c8df46d7ec61923dffe6f58ec07|2017-08-23|h322|77c9230f9eeeb9faaa5c30ff518bbf60|2017-08-31|h329|5a6e0895b2477026bcfa4996650797a8|2017-09-07|h331|37d84c0c0956df75407e0bfc67a782ed|2017-09-09|h334|f43f07545fba020b47c84952c6af6cc7|2017-09-12|h335|2fa08f1311c20ac45a6726bfbc8a4f05|2017-09-13|h336|d01940a876b86c2de8d67a37813ab89d|2017-09-14|h338|f21e401cb38c6d625b264f53fb59fb8b|2017-09-16|h343|c42fcb14c2d5e5c067db8231d502daae|2017-09-21|h345|cc5670020ba35ae2c324dd33a6efff98|2017-09-23|h31|2881b2aadddd2dfef14477369a107319|2017-08-10|h42|e22e0bba2716e6d969a62502d34fc518|2017-08-11|h49|a933091df7f51f5b0b6ab43816e5d765|2017-08-18|h415|92ee6dc6670ea8e02e746ee508b51022|2017-08-24|h421|67b7140105e81730f364ee9de195e0a0|2017-08-30|h446|f4c47b9e055f6c732dff55cb4fd152b3|2017-09-24|h450|1b419faea293d3edab2cfb7f8efb55f8|2017-09-28|h43|6ea55fe46f2119f084edd66abe486d91|2017-08-12|h54|094b16011f0e9b34c878caa7d95e067f|2017-08-13|h57|c916c264c77ba90b3463143b9513bc15|2017-08-16|h510|d333ce15f3a8d01a39df9ae317bf64b7|2017-08-19|h516|b136e8466bbafc58f917e14919b1edf1|2017-08-25|h518|6c3fb7b3e473f793575407299006e6a3|2017-08-27|h520|bac12655b2d54855c58d19c9facc1579|2017-08-29|h524|ebcafb42d26654eff04bb5f8b35fdd69|2017-09-02|h525|494e25facb9fe46e00037c716e2052e7|2017-09-03|h527|961b1728893e7f6d46ed827ee9b4809e|2017-09-05|h530|ebf840a36af46cc3dd8f29c94013cb71|2017-09-08|h532|7c5083fed360079bcbc23c6ee803a4d6|2017-09-10|h533|707e98eac5ba349c80df6f9d8f062676|2017-09-11|h537|e1cb546e66cd45b00441493100fb7752|2017-09-15|h539|0bfa7e7ccb00a477add00df4d0327c52|2017-09-17|h547|d894969c3cdbf00fe7dce9683c6f9a17|2017-09-25|h548|09ca86c5e5bd87ba786533f34c4ebf25|2017-09-26|h5(50rows)yonj1e=#explainselect*fromhwhereh_id=20;QUERYPLAN----------------------------------------------------------Append(cost=0.00..20.12rows=4width=72)->SeqScanonh5(cost=0.00..20.12rows=4width=72)Filter:(h_id=20)(3rows)yonj1e=#\d+h*Table"public.h"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------h_id|integer||||plain||h_name|name||||plain||h_date|date||||plain||Partitionkey:HASH(h_id)Partitions:h2SERIALNUMBER0,h3SERIALNUMBER1,h4SERIALNUMBER2,h5SERIALNUMBER3Table"public.h2"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------h_id|integer||||plain||h_name|name||||plain||h_date|date||||plain||Partitionof:hSERIALNUMBER0Partitionconstraint:(h_idISNOTNULL)Table"public.h3"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------h_id|integer||||plain||h_name|name||||plain||h_date|date||||plain||Partitionof:hSERIALNUMBER1Partitionconstraint:(h_idISNOTNULL)Table"public.h4"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------h_id|integer||||plain||h_name|name||||plain||h_date|date||||plain||Partitionof:hSERIALNUMBER2Partitionconstraint:(h_idISNOTNULL)Table"public.h5"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------h_id|integer||||plain||h_name|name||||plain||h_date|date||||plain||Partitionof:hSERIALNUMBER3Partitionconstraint:(h_idISNOTNULL)yonj1e=#

HASH分区语法还不支持，以后或许会支持。

以上就是PostgreSQL 10中如何使用分区表，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。