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

来源:脚本之家  责任编辑:小易  
目录
1、创建一个有DEFAULT的分区表1、先创建主表2、如果没有创建分区就直接插入数据会报错3、创建分区4、查看分区情况2、有default 分区,再加分区解决办法:3、没有default的分区4、给分区表ddl4.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分区表操作内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

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

  • 本文相关:
  • postgresql 查找当前数据库的所有表操作
  • postgresql删除主键的操作
  • 修改postgresql存储目录的操作方式
  • postgresql教程(三):表的继承和分区表详解
  • postgresql中的comment用法说明
  • 解决postgresql无法远程访问的情况
  • 如何使用postgresql进行中文全文检索
  • postgresql 实现快速删除一个用户
  • pgsql之pg_stat_replication的使用详解
  • linux centos 7源码编译安装postgresql9.5
  • PostgreSQL数据库中,怎么样使用in
  • postgreSQL中向视图插入数据,需要注意哪些问题?
  • 如何将Excel数据导入到postgresql数据库中
  • 如何将Excel数据导入到postgresql数据库中
  • PostgreSQL数据库中char,varchar和text的区别
  • PostgreSQL数据库中char,varchar和text的区别
  • 如何从PostgreSQL json中提取数组
  • 如何将Excel数据导入到postgresql数据库中
  • 请教如何修改postgresql数据库中已有数据的表的字段长度
  • 如何从PostgreSQL json中提取数组
  • postgreSQL数据库中,如何实现多条update语句同时一起执行。
  • 怎样设置PostgreSQL中字段和表名对大小写敏感
  • 如何在PostgreSQL中调试plpgsql存储过程
  • PHP 读取Postgresql中的数组
  • 如何修改PostgreSQL中一个表的oid
  • 如何向postgreSQL中添加bytea类型的大对象数据
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页postgresqlpostgresql教程(三):表的继承和分区表详解postgresql分区表(partitioning)应用实例详解postgresql之分区表(partitioning)pyspark操作hive分区表及.gz.parquet和part-00000文件压缩问题postgresql怎么创建分区表详解postgresql?10分区表及性能测试报告小结postgresql 查找当前数据库的所有表操作postgresql删除主键的操作修改postgresql存储目录的操作方式postgresql教程(三):表的继承和分区表详解postgresql中的comment用法说明解决postgresql无法远程访问的情况如何使用postgresql进行中文全文检索postgresql 实现快速删除一个用户pgsql之pg_stat_replication的使用详解linux centos 7源码编译安装postgresql9.5postgresql 角色与用户管理介绍windows下postgresql数据库的下载与配置方法windows下postgresql安装图解postgresql中的oid和xid 说明15个postgresql数据库实用命令分享postgresql实现批量插入、更新与合并操作的方法postgresql alter语句常用操作小结postgresql 创建表分区windows postgresql 安装图文教程postgresql 安装和简单使用postgresql 打印日志信息所在的源文件和行数的实例springboot连接使用postgresql数据库的方法postgresql 中round函数的用法说明sqoop读取postgresql数据库表格导入到hdfs中的实现postgresql三种自增列sequence,serial,identitpostgresql 性能优化之服务器参数配置操作postgresql sql语句变量的使用说明如何获取postgresql数据库中的json值postgresql分布式插件plproxy的使用详解postgresql 删除重复数据案例详解
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved