首页 > 资讯 > 数据库 >PostgreSQL12.5中分区表的一些操作实例

787

分享到

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

pg查看分区表 pg 分区 pg创建分区表 2022-08-12 13:08:08 787人浏览薄情痞子

摘要

目录1、创建一个有DEFAULT的分区表1、先创建主表2、如果没有创建分区就直接插入数据会报错3、创建分区4、查看分区情况2、有default 分区，再加分区解决办法：3、没有default的分区4、给分区表ddl4.1

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分区表操作内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: PostgreSQL12.5中分区表的一些操作实例

本文链接: https://www.lsjlt.com/news/33676.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

Oracle分区表的move操作

关于分区表的move操作还是很值得深究的一个问题。如果分区表中含有lob字段，难度还会加大。对于普通的表而言，做move操作室理所当然，oracle提供的方式很直接快捷。当然了这个命令也不是高可用的范畴。但...

99+

2024-04-02
Mysql表分区的操作方法

这篇文章主要讲解了“Mysql表分区的操作方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Mysql表分区的操作方法”吧！ M...

99+

2024-04-02
C#操作注册表实例分析

这篇文章主要讲解了“C#操作注册表实例分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“C#操作注册表实例分析”吧！在下面的例子里，要注意：建项函数：CreateSubKey()建子键函数：...

99+

2023-06-18
MySQL分区表和分桶表的操作详解

目录1.创建分区表2.增删改查操作2.1 插入数据2.2 操作数据3. 二级分区表3.1 创建分区表3.2 插入数据4.动态分区5.分桶表5.1 新建分桶表5.2 插入数据5.3 既分区有分桶6 分区与分桶的区别1.创建...

99+

2023-05-12

MySQL分区表和分桶表 MySQL分区表 MySQL分桶表
MySQL中删除表操作实现有哪些区别

这篇文章给大家分享的是有关MySQL中删除表操作实现有哪些区别的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。本文主要讲mysql中三种删除表的操作，delete语句、t...

99+

2024-04-02
oracle表分区的概念及操作

oracle表分区详解 1.表空间及分区表的概念表空间：　　是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。分区表：当表...

99+

2024-04-02
js的链表操作实例

这篇文章主要讲解了“js的链表操作实例”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“js的链表操作实例”吧！如下所示：<!doctype h...

99+

2024-04-02
Python中的json操作实例分析

本文小编为大家详细介绍“Python中的json操作实例分析”，内容详细，步骤清晰，细节处理妥当，希望这篇“Python中的json操作实例分析”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。1.什么是JsonJS...

99+

2023-06-30
Python中的Tuple操作实例分析

这篇“Python中的Tuple操作实例分析”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“Python中的Tuple操作实例...

99+

2023-06-29
JavaScript中的操作符与表达式实例代码分析

这篇文章主要介绍“JavaScript中的操作符与表达式实例代码分析”，在日常操作中，相信很多人在JavaScript中的操作符与表达式实例代码分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”JavaScr...

99+

2023-07-04
linq中的分区操作符

Linq中的分区指的是在不重新排列元素的情况下，将输入序列划分为两部分，然后返回其中一个部分的操作。一、Take操作符 Take(int n)表示将从序列的开头返回数量为n的连续元...

99+

2024-04-02
JavaScript操作表单的示例分析

这篇文章主要为大家展示了“JavaScript操作表单的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“JavaScript操作表单的示例分析”这篇文章吧...

99+

2024-04-02
oracle数据库中表的操作示例分析

这篇文章主要为大家展示了“oracle数据库中表的操作示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“oracle数据库中表的操作示例分析”这篇文章吧。1...

99+

2024-04-02
golang中json操作实例分析

这篇“golang中json操作实例分析”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“golang中json操作实例分析”文...

99+

2023-07-02
java中对List分段操作的实例

java中对List分段操作的实例问题：假设A系统查询出来一个很大很大的List，现在B系统想要得到这个List来导出报表，但是B系统部署环境条件有限，内存容不下这么大一个List，这时候我们就需要把List切分，然后再一个一个导出了。如果...

99+

2023-05-31

java list 分段
oracle中分表之表分区的示例分析

这篇文章将为大家详细讲解有关oracle中分表之表分区的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。此文从以下几个方面来整理关于分区表的概念及操作: ...

99+

2024-04-02
CentOS中怎么实现cfdisk分区操作

本篇文章为大家展示了CentOS中怎么实现cfdisk分区操作，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。警告：如果你是装的xp和CentOS双系统的话，请不要再centos下动磁盘分区，否则会引...

99+

2023-06-10
C++中IO流操作实例分析

这篇“C++中IO流操作实例分析”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“C++中IO流操作实例分析”文章吧。1.标准输...

99+

2023-07-02
C++中文件操作实例分析

这篇文章主要介绍“C++中文件操作实例分析”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“C++中文件操作实例分析”文章能帮助大家解决问题。文件操作意义：利用文件操作可以保存我们程序运行的信息，是持久...

99+

2023-07-02
C#中的Linq to JSON操作实例分析

今天小编给大家分享一下C#中的Linq to JSON操作实例分析的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解...

99+

2023-06-30