PostgreSQL12.5中分区表的一些操作实例

PostgreSQL12.5中分区表的一些操作实例

目录

1、创建一个有DEFAULT的分区表

1、先创建主表

2、如果没有创建分区就直接插入数据会报错

3、创建分区

4、查看分区情况

2、有default 分区,再加分区

解决办法:

3、没有default的分区

4、给分区表ddl

4.1、在原来没有主键的分区表加主键

4.2、创建分区表时,就指定主键

4.3、分区表加字段,修改字段

总结

1、创建一个有DEFAULT的分区表 1、先创建主表 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) 2、如果没有创建分区就直接插入数据会报错 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=# 3、创建分区 #包括左边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'); 4、查看分区情况 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=# 2、有default 分区,再加分区

因为有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 3、没有default的分区

创建没有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). 4、给分区表ddl 4.1、在原来没有主键的分区表加主键

结论:

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=# 4.2、创建分区表时,就指定主键

主键不包括分区键,报错提示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 4.3、分区表加字段,修改字段

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中分区表的一些操作的文章就介绍到这了,更多相关pg12.5分区表操作内容请搜索易知道(ezd.cc)以前的文章或继续浏览下面的相关文章希望大家以后多多支持易知道(ezd.cc)!

推荐阅读

    优化PostgreSQL中的批量更新性能

    优化PostgreSQL中的批量更新性能,数据,表格,在Ubuntu 12.04上使用PG 9.1. 我们目前需要24小时才能运行大量UPDATE数据库上的语句,其形式

    postgresql的ALTER常用操作

    postgresql的ALTER常用操作,字段,数据类型,postgresql版本:psql (9.3.4)1、增加一列ALTER TABLE table_name ADD column_name datatype;

    PostgreSQL数据库修改sql表的方法汇总

    PostgreSQL数据库修改sql表的方法汇总,字段,默认值, 一,修改表 PostgreSQL 提供了一族命令用于修改现有表。 可以实现: 增加字段, 删除字段,

    为PostgreSQL添加插件

    为PostgreSQL添加插件,文件,插件, 我目前了解的PG插件大约有两种,一种是利用hook,另一种是建立C函数,然后在数据库中进行关联。PG本身就

    postgresql中对jsonb的查询及转换

    postgresql中对jsonb的查询及转换,数据,字段,表数据:需要将strata排除,并且过滤info字段中为{}的数据,将jsonb转换成text,替换“,{,}见SQL:sele

    PostgreSQL导出导入schema相关

    PostgreSQL导出导入schema相关,库里,也是,整理了一下PostgreSQL导入导出相关schema的方法。 一、导出: 导出his库里crm的schema表结构

    PostgreSQL连接问题|Net LO problem

    PostgreSQL连接问题|Net LO problem,他的,都是,中午开发人员说PHP连接不上了,他的连接方式: &apos;connectionString&apos; =>"pgsql:host=

    从Postgresql中删除Schema

    从Postgresql中删除Schema,语句,命令,我试图从bash文件执行一些postgres命令. 我试图通过编写以下行来删除模式DROP SCHEMA "xxx" IF E