PostgreSQL12.5中分区表的一些操作实例（spread过去分词和过去分词）一篇读懂

91 0 0

目录1、创建一个有DEFAULT的分区表1、先创建主表2、如果没有创建分区就直接插入数据会报错3、创建分区4、查看分区情况2、有default 分区，再加分区解决办法：3、没有default的分区4、给分区表ddl4.1、在原来没有主键的分区表加主键4.2、创建分区表时，就指定主键4.3、分区表加字段，修改字段总结

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 TABLE

3、分区创建成功，分区创建之后需把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、在主表添加主键需要是想要的主键+分区键

1.3、可以给分区表单独添加主键

主键不包括分区键，报错提示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、加字段，可以成功添加，在主表加字段，分区表会自动添加

2、直接在分区表加字段会报错

postgres=# alter table tbl_log_p201801 add name2 varchar(2);
ERROR: cannot add column to a partition

3、修改字段

到此这篇关于PostgreSQL12.5中分区表的一些操作的文章就介绍到这了,更多相关pg12.5分区表操作内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:PostgreSQL教程(三)：表的继承和分区表详解PostgreSQL分区表（partitioning）应用实例详解PostgreSQL之分区表(partitioning)pyspark操作hive分区表及.gz.parquet和part-00000文件压缩问题PostgreSQL怎么创建分区表详解PostgreSQL?10分区表及性能测试报告小结

# 随心笔谈