PostgreSQL12.5中分区表的一些操作介绍_F11 - 专业站长和开发者的学习网站

网站首页收藏本站

广告位联系

APP正在开发中...

返回顶部

分享到

PostgreSQL12.5中分区表的一些操作介绍

数据库其他 来源：互联网作者：F11站长开发者发布时间：2022-08-12 15:34:48 人浏览

摘要

1、创建一个有DEFAULT的分区表 1、先创建主表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 create table tbl_log ( id serial, create_time timestamp(0) without time zone, remark char(1) ) PARTITION BY RANGE (create_time); #因为是serial类型

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 分区，再加分区，所以会报错

1 2	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分区

1 2	postgres=# ALTER TABLE tbl_log DETACH PARTITION tbl_log_default; ALTER TABLE

2、创建想要的分区

1 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分区成功

1 2	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、直接在分区表加字段会报错

1 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=#

您可能感兴趣的文章 :

PostgreSQL 分区表的ALTER TABLE 语句执行机制解析

原文链接 : https://blog.csdn.net/weixin_42583514/article/details/123063420

Tag :

六大主流数据同步工具对比：DataX、Airbyte、Cana

当数据量变大、数据源复杂、实时需求提高，很多团队在选数据同步工具时犯难。本文对 DataX、Airbyte、Canal、Debezium、Fivetran 与 Apache SeaTun
解读SQL生成工具

SQL 生成工具可用于测试Parser与其他数据库产品的兼容性，通过解析YACC语法文件中的产生式，生成对应的SQL语句，再使用数据库执行该SQL，根
SQLite3在嵌入式C环境中存储音频/视频文件的最优

SQLite3 在嵌入式C环境中存储音频/视频文件的专业方案在嵌入式系统中存储大型媒体文件需要平衡存储效率、访问速度和资源限制。以下是针
嵌入式数据库SQLite 3配置使用

0、惨痛教训随着管理开发的项目体积越来越庞大，产品系统涉及的数据量也越来越多，并且伴随着项目不久就要交付给甲方了。如果项目的
Sqlite3基本语句及安装过程

SQLite3简介 SQLite3是一款轻量级的、基于文件的开源关系型数据库引擎，由 D. Richard Hipp 于 2000 年首次发布。它遵循 SQL 标准，但与传统的数据
在SQLite中进行批量操作的有效实现方法

SQLite 是一个轻量级的关系型数据库管理系统，因其高效性和易用性而广受欢迎。在许多应用场景中，批量操作的需求是不可避免的，例如在
一文介绍在Hive中NULL的理解

在 Hive 中，NULL 是一个特殊的值，表示未知或缺失。任何与NULL的比较操作（如=,,,=,=,）都会返回NULL，而不是TRUE或FALSE。 1.NULL 的比较规则在
Navicat Premium 12数据库管理解决方案

Navicat Premium 12是一款全面的数据库管理工具，支持多种数据库系统如MySQL、MariaDB、Oracle、SQL Server、PostgreSQL等。它提供了多数据库连接、数据
sqlite3命令行工具使用介绍

一、启动与退出启动数据库连接 1 2 3 sqlite3 [database_file] # 打开/创建数据库文件（如 test.db） sqlite3 # 启动临时内存数据库 (:memory:) sqlite3 :m
StarRocks简介与搭建使用介绍

StarRocks简介 StarRocks 是一款高速、实时、全场景的MPP（大规模并行处理）分析型数据库系统，专为现代数据分析场景设计，强调亚秒级查询性

PostgreSQL12.5中分区表的一些操作介绍

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

1、先创建主表

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

3、创建分区

4、查看分区情况

2、有default 分区，再加分区

解决办法：

3、没有default的分区

4、给分区表ddl

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

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

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

您可能感兴趣的文章 :

六大主流数据同步工具对比：DataX、Airbyte、Cana

解读SQL生成工具

SQLite3在嵌入式C环境中存储音频/视频文件的最优

嵌入式数据库SQLite 3配置使用

Sqlite3基本语句及安装过程

在SQLite中进行批量操作的有效实现方法

一文介绍在Hive中NULL的理解

Navicat Premium 12数据库管理解决方案

sqlite3命令行工具使用介绍

StarRocks简介与搭建使用介绍

通过SQL进行分布式死锁的检测与消除

DB2数据库中常见的堵塞问题分析与处

一篇文章带你掌握SQLite3基本用法

MariaDB Spider数据库分库分表实践记录

sQlite常用语句以及sQlite developer的使用