这篇文章主要介绍“PostgreSQL12.5中分区表的操作方法有哪些”，在日常操作中，相信很多人在PostgreSQL12.5中分区表的操作方法有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL12.5中分区表的操作方法有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
create table tbl_log ( id serial, create_time timestamp(0) without time zone, remark char(1) ) PARTITION BY RANGE (create_time); #因为是serial类型，自增的所以会自动创建一个序列 postgres=# \d List of relations Schema | Name | Type | Owner --------+----------------+-------------------+---------- public | tbl_log | partitioned table | postgres public | tbl_log_id_seq | sequence | postgres (7 rows)
postgres=# INSERT INTO tbl_log(id, create_time, remark) VALUES (1, '2018-02-01', 'a'); ERROR: no partition of relation "tbl_log" found for row DETAIL: Partition key of the failing row contains (create_time) = (2018-02-01 00:00:00). postgres=#
#包括左边1.1，不包括2.1
CREATE TABLE tbl_log_p201801 PARTITION OF tbl_log FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE tbl_log_p201802 PARTITION OF tbl_log FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');
CREATE TABLE tbl_log_p201803 PARTITION OF tbl_log FOR VALUES FROM ('2018-03-01') TO ('2018-04-01');
CREATE TABLE tbl_log_default PARTITION OF tbl_log DEFAULT;
INSERT INTO tbl_log(id, create_time, remark) VALUES (1, '2018-02-01', 'a');
INSERT INTO tbl_log(id, create_time, remark) VALUES (2, '2018-03-01', 'b');
INSERT INTO tbl_log(id, create_time, remark) VALUES (3, '2018-04-01', 'd');
INSERT INTO tbl_log(id, create_time, remark) VALUES (4, '2020-07-01', 'c');postgres=# select * from tbl_log; id | create_time | remark ----+---------------------+-------- 1 | 2018-02-01 00:00:00 | a 2 | 2018-03-01 00:00:00 | b 3 | 2018-04-01 00:00:00 | d 4 | 2020-07-01 00:00:00 | c (4 rows) postgres=# select * from tbl_log_p201801; id | create_time | remark ----+-------------+-------- (0 rows) postgres=# select * from tbl_log_p201802; id | create_time | remark ----+---------------------+-------- 1 | 2018-02-01 00:00:00 | a (1 row) postgres=# select * from tbl_log_p201803; id | create_time | remark ----+---------------------+-------- 2 | 2018-03-01 00:00:00 | b (1 row) postgres=# select * from tbl_log_default; id | create_time | remark ----+---------------------+-------- 3 | 2018-04-01 00:00:00 | d 4 | 2020-07-01 00:00:00 | c (2 rows) postgres=#
因为有default 分区，再加分区，所以会报错
postgres=# CREATE TABLE tbl_log_p201804 PARTITION OF tbl_log FOR VALUES FROM ('2018-04-01') TO ('2018-05-01');
ERROR: updated partition constraint for default partition "tbl_log_default" would be violated by some row以上添加分区报错，需要解绑default分区，之后再添加，如下
1、解绑Default分区
postgres=# ALTER TABLE tbl_log DETACH PARTITION tbl_log_default; ALTER TABLE
2、创建想要的分区
postgres=# CREATE TABLE tbl_log_p201804 PARTITION OF tbl_log FOR VALUES FROM ('2018-04-01') TO ('2018-05-01');
CREATE TABLE3、分区创建成功，分区创建之后需把DEFAULT分区连接。
连接DEFAULT分区报错，如下:
postgres=# ALTER TABLE tbl_log ATTACH PARTITION tbl_log_default DEFAULT; ERROR: partition constraint is violated by some row postgres=# INSERT INTO tbl_log_p201804 SELECT * FROM tbl_log_default; ERROR: new row for relation "tbl_log_p201804" violates partition constraint DETAIL: Failing row contains (4, 2020-07-01 00:00:00, c).
因为tbl_log_default分区内有2018-04-01的数据，把这个数据从tbl_log_default中导出到对应的分区，并清理tbl_log_default中的对应的数据
postgres=# INSERT INTO tbl_log_p201804 SELECT * FROM tbl_log_default where create_time>='2018-04-01' and create_time<'2018-05-01'; INSERT 0 1 postgres=# delete from tbl_log_default where create_time>='2018-04-01' and create_time<'2018-05-01'; DELETE 1
4、再次连接DEFAULT分区成功
postgres=# ALTER TABLE tbl_log ATTACH PARTITION tbl_log_default DEFAULT; ALTER TABLE
创建没有default的分区，当插入的数据超过规划好的分区的时候会报错
1、创建1月份分区
create table tbl_log2
(
id serial,
create_time timestamp(0) without time zone,
remark char(1)
) PARTITION BY RANGE (create_time);
CREATE TABLE tbl_log2_p201801 PARTITION OF tbl_log2 FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');插入2月的数据就会报错
postgres=# INSERT INTO tbl_log2(id, create_time, remark) VALUES (1, '2018-01-01', 'a'); INSERT 0 1 postgres=# INSERT INTO tbl_log2(id, create_time, remark) VALUES (1, '2018-02-01', 'a'); ERROR: no partition of relation "tbl_log2" found for row DETAIL: Partition key of the failing row contains (create_time) = (2018-02-01 00:00:00).
结论：
1、在主表加主键，主键为仅仅想要的主键，会报错，需要用想要的主键+分区键组合为主键
2、分区表可以单独添加主键
1.1、在主表加主键，主键为仅仅想要的主键，报错如下 must include all partitioning columns
postgres=# alter table tbl_log add primary key(id); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "tbl_log" lacks column "create_time" which is part of the partition key. postgres=# alter table tbl_log add primary key(id)
1.2、在主表添加主键需要是想要的主键+分区键
postgres=# alter table tbl_log add primary key (id,create_time);
ALTER TABLE
postgres=# \d tbl_log
Partitioned table "public.tbl_log"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass)
create_time | timestamp(0) without time zone | | not null |
remark | character(1) | | |
name | character varying(2) | | |
Partition key: RANGE (create_time)
Indexes:
"tbl_log_pkey" PRIMARY KEY, btree (id, create_time)
Number of partitions: 5 (Use \d+ to list them.)
postgres=# \d tbl_log_p201801
Table "public.tbl_log_p201801"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass)
create_time | timestamp(0) without time zone | | not null |
remark | character(1) | | |
name | character varying(2) | | |
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')
Indexes:
"tbl_log_p201801_pkey" PRIMARY KEY, btree (id, create_time)1.3、可以给分区表单独添加主键
postgres=# alter table tbl_log_p201801 add primary key (id);
ALTER TABLE
postgres=# \d tbl_log_p201801
Table "public.tbl_log_p201801"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass)
create_time | timestamp(0) without time zone | | |
remark | character(1) | | |
name | character varying(2) | | |
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')
Indexes:
"tbl_log_p201801_pkey" PRIMARY KEY, btree (id)
postgres=#主键不包括分区键，报错提示must include all partitioning columns
create table tbl_log2 ( id int, create_time timestamp(0) without time zone, remark char(1), primary key (id) ); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "tbl_log2" lacks column "create_time" which is part of the partition key.
修改语句，添加分区键也为主键，创建成功
create table tbl_log2 ( id int, create_time timestamp(0) without time zone, remark char(1), primary key (id,create_time) ) PARTITION BY RANGE (create_time); CREATE TABLE
1、加字段，可以成功添加，在主表加字段，分区表会自动添加
postgres=# alter table tbl_log add name varchar(2);
ALTER TABLE
postgres=# \d tbl_log;
Partitioned table "public.tbl_log"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass)
create_time | timestamp(0) without time zone | | |
remark | character(1) | | |
name | character varying(2) | | |
Partition key: RANGE (create_time)
Number of partitions: 5 (Use \d+ to list them.)
postgres=# \d tbl_log_p201801;
Table "public.tbl_log_p201801"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass)
create_time | timestamp(0) without time zone | | |
remark | character(1) | | |
name | character varying(2) | | |
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')2、直接在分区表加字段会报错
postgres=# alter table tbl_log_p201801 add name2 varchar(2); ERROR: cannot add column to a partition
3、修改字段
postgres=# alter table tbl_log alter column remark type varchar(10);
ALTER TABLE
postgres=# \d tbl_log;
Partitioned table "public.tbl_log"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass)
create_time | timestamp(0) without time zone | | not null |
remark | character varying(10) | | |
name | character varying(2) | | |
Partition key: RANGE (create_time)
Indexes:
"tbl_log_pkey" PRIMARY KEY, btree (id, create_time)
Number of partitions: 5 (Use \d+ to list them.)
postgres=# \d tbl_log_p201801
Table "public.tbl_log_p201801"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass)
create_time | timestamp(0) without time zone | | not null |
remark | character varying(10) | | |
name | character varying(2) | | |
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')
Indexes:
"tbl_log_p201801_pkey" PRIMARY KEY, btree (id, create_time)
postgres=#到此，关于“PostgreSQL12.5中分区表的操作方法有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
