PostgreSQL12.5中分區(qū)表的一些操作實(shí)例
更新時(shí)間：2022年08月12日 11:57:21 作者：面子拿錢(qián)砸
PostgreSQL支持通過(guò)表繼承進(jìn)行分區(qū),下面這篇文章主要給大家介紹了關(guān)于PostgreSQL12.5中分區(qū)表的一些操作的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
1、創(chuàng)建一個(gè)有DEFAULT的分區(qū)表
1、先創(chuàng)建主表
create table tbl_log
(
id serial,
create_time timestamp(0) without time zone,
remark char(1)
) PARTITION BY RANGE (create_time);
#因?yàn)槭莝erial類(lèi)型，自增的所以會(huì)自動(dòng)創(chuàng)建一個(gè)序列
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、如果沒(méi)有創(chuàng)建分區(qū)就直接插入數(shù)據(jù)會(huì)報(bào)錯(cuò)
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、創(chuàng)建分區(qū)
#包括左邊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、查看分區(qū)情況
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 分區(qū)，再加分區(qū)
因?yàn)橛衐efault 分區(qū)，再加分區(qū)，所以會(huì)報(bào)錯(cuò)
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
解決辦法：
以上添加分區(qū)報(bào)錯(cuò)，需要解綁default分區(qū)，之后再添加，如下
1、解綁Default分區(qū)
postgres=# ALTER TABLE tbl_log DETACH PARTITION tbl_log_default; ALTER TABLE
2、創(chuàng)建想要的分區(qū)
postgres=# CREATE TABLE tbl_log_p201804 PARTITION OF tbl_log FOR VALUES FROM ('2018-04-01') TO ('2018-05-01');
CREATE TABLE
3、分區(qū)創(chuàng)建成功，分區(qū)創(chuàng)建之后需把DEFAULT分區(qū)連接。
連接DEFAULT分區(qū)報(bào)錯(cuò)，如下:
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).
因?yàn)閠bl_log_default分區(qū)內(nèi)有2018-04-01的數(shù)據(jù)，把這個(gè)數(shù)據(jù)從tbl_log_default中導(dǎo)出到對(duì)應(yīng)的分區(qū)，并清理tbl_log_default中的對(duì)應(yīng)的數(shù)據(jù)
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分區(qū)成功
postgres=# ALTER TABLE tbl_log ATTACH PARTITION tbl_log_default DEFAULT; ALTER TABLE
3、沒(méi)有default的分區(qū)
創(chuàng)建沒(méi)有default的分區(qū)，當(dāng)插入的數(shù)據(jù)超過(guò)規(guī)劃好的分區(qū)的時(shí)候會(huì)報(bào)錯(cuò)
1、創(chuàng)建1月份分區(qū)
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月的數(shù)據(jù)就會(huì)報(bào)錯(cuò)
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、給分區(qū)表ddl
4.1、在原來(lái)沒(méi)有主鍵的分區(qū)表加主鍵
結(jié)論：
1、在主表加主鍵，主鍵為僅僅想要的主鍵，會(huì)報(bào)錯(cuò)，需要用想要的主鍵+分區(qū)鍵組合為主鍵
2、分區(qū)表可以單獨(dú)添加主鍵
1.1、在主表加主鍵，主鍵為僅僅想要的主鍵，報(bào)錯(cuò)如下 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、在主表添加主鍵需要是想要的主鍵+分區(qū)鍵
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、可以給分區(qū)表單獨(dú)添加主鍵
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、創(chuàng)建分區(qū)表時(shí)，就指定主鍵
主鍵不包括分區(qū)鍵，報(bào)錯(cuò)提示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.
修改語(yǔ)句，添加分區(qū)鍵也為主鍵，創(chuàng)建成功
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、分區(qū)表加字段，修改字段
1、加字段，可以成功添加，在主表加字段，分區(qū)表會(huì)自動(dòng)添加
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、直接在分區(qū)表加字段會(huì)報(bào)錯(cuò)
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=#
總結(jié)
到此這篇關(guān)于PostgreSQL12.5中分區(qū)表的一些操作的文章就介紹到這了,更多相關(guān)pg12.5分區(qū)表操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- PostgreSQL 分區(qū)表的實(shí)現(xiàn)示例
- PostgreSQL分區(qū)表的實(shí)現(xiàn)示例
- PostgreSQL怎么創(chuàng)建分區(qū)表詳解
- PostgreSQL?10分區(qū)表及性能測(cè)試報(bào)告小結(jié)
- PostgreSQL之分區(qū)表(partitioning)
- PostgreSQL分區(qū)表（partitioning）應(yīng)用實(shí)例詳解
- PostgreSQL教程(三)：表的繼承和分區(qū)表詳解
- PostgreSQL 分區(qū)表的 ALTER TABLE 語(yǔ)句執(zhí)行機(jī)制解析
相關(guān)文章
postgresql高級(jí)應(yīng)用之合并單元格的思路詳解
這篇文章主要介紹了postgresql高級(jí)應(yīng)用之合并單元格,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-05-05
PostgreSQL 作為一款功能強(qiáng)大的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù),其復(fù)制功能對(duì)于構(gòu)建高可用性系統(tǒng)至關(guān)重要,本文給大家詳細(xì)介紹了PostgreSQL的復(fù)制參數(shù),需要的朋友可以參考下2025-05-05
PostgreSQL誤刪數(shù)據(jù)庫(kù)該怎么辦詳解
這篇文章主要介紹了PostgreSQL中誤刪數(shù)據(jù)庫(kù)的恢復(fù)方法,包括備份恢復(fù)、歸檔日志恢復(fù)和操作系統(tǒng)層面的快照恢復(fù),文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-03-03
PostgreSQL進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出的操作代碼
在數(shù)據(jù)庫(kù)管理中,數(shù)據(jù)的導(dǎo)入和導(dǎo)出是非常常見(jiàn)的操作,特別是在 PostgreSQL 中,提供了多種工具和方法來(lái)實(shí)現(xiàn)數(shù)據(jù)的有效管理,本文將詳細(xì)介紹在 PostgreSQL 中如何進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出,并給出具體的命令及示例,需要的朋友可以參考下2024-10-10
基于PostgreSQL和mysql數(shù)據(jù)類(lèi)型對(duì)比兼容
這篇文章主要介紹了基于PostgreSQL和mysql數(shù)據(jù)類(lèi)型對(duì)比兼容，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
PostgreSQL連接數(shù)過(guò)多的原因分析與連接池方案
在 PostgreSQL 的生產(chǎn)運(yùn)維中,連接數(shù)過(guò)多是最常見(jiàn)且影響深遠(yuǎn)的性能問(wèn)題之一,本文將系統(tǒng)性地剖析 連接數(shù)過(guò)多的根本原因,詳解 PostgreSQL 連接機(jī)制與資源開(kāi)銷(xiāo),并對(duì)比主流 連接池方案的原理、配置與適用場(chǎng)景,需要的朋友可以參考下2026-02-02
在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法
這篇文章主要介紹了在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù),本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
PostgreSQL 自定義自動(dòng)類(lèi)型轉(zhuǎn)換操作(CAST)
這篇文章主要介紹了PostgreSQL 自定義自動(dòng)類(lèi)型轉(zhuǎn)換操作(CAST)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
如何將postgresql數(shù)據(jù)庫(kù)表內(nèi)數(shù)據(jù)導(dǎo)出為excel格式(推薦)
這篇文章主要介紹了如何將postgresql數(shù)據(jù)庫(kù)表內(nèi)數(shù)據(jù)導(dǎo)出為excel格式(推薦),本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
PostgreSQL登陸方式(本地和遠(yuǎn)程)的實(shí)現(xiàn)
本次分享一下PostgreSQL 的登陸方式,包括本地登錄和遠(yuǎn)程登錄,文中通過(guò)圖文示例介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2025-11-11