1. 云图网首页
  2. 技术专区
  3. 数据库

Using Only when truncating parent table

• 数据库

在PG里，通常会对大表做分区，建成月表形式，通常月表都是继承父表，在维护时，有个重要的属性需要时刻记牢， 比如 truncate 父表时，需要加 only 属性, 否则，一个 truncate下去，本来是想清空父表的数据，结果把所有子表的数据也干掉了，此时为时已晚，下面做了个简单的实验，验证了下。

创建父表

1
2
 
mydb=> create table table_fenqu (id integer, addtime timestamp without time zone);  
CREATE TABLE

创建三张子表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
mydb=> create table table_fenqu_201009( id integer, addtime timestamp without time zone) inherits (table_fenqu);  
NOTICE: merging column "id" with inherited definition  
NOTICE: merging column "addtime" with inherited definition  
CREATE TABLE

mydb=> create table table_fenqu_201010( id integer, addtime timestamp without time zone) inherits (table_fenqu);  
NOTICE: merging column "id" with inherited definition  
NOTICE: merging column "addtime" with inherited definition  
CREATE TABLE

mydb=> create table table_fenqu_201011( id integer, addtime timestamp without time zone) inherits (table_fenqu);  
NOTICE: merging column "id" with inherited definition  
NOTICE: merging column "addtime" with inherited definition  
CREATE TABLE

查看字段属性

1
2
3
4
5
6
7
 
mydb=> /d table_fenqu  
 Table "skytf.table_fenqu"  
Column | Type | Modifiers  
---------+-----------------------------+-----------  
id | integer |  
addtime | timestamp without time zone |  
Number of child tables: 3 (Use d+ to list them.)

插入测试数据

1
2
3
4
5
6
7
8
 
mydb=> insert into table_fenqu_201009 (id,addtime) values (1,'2010-09-01 00:00:00');  
INSERT 0 1  
mydb=> insert into table_fenqu_201010 (id,addtime) values (1,'2010-10-01 00:00:00');  
INSERT 0 1  
mydb=> insert into table_fenqu_201011 (id,addtime) values (1,'2010-11-01 00:00:00');  
INSERT 0 1  
mydb=> insert into table_fenqu_201011 (id,addtime) values (1,'2010-11-02 00:00:00');  
INSERT 0 1

查看父表数据

1
2
3
4
5
 
mydb=> select count(*) from table_fenqu;  
count  
-------  
 4  
(1 row)

查看子表数据

1
2
3
4
5
6
7
8
9
10
11
 
mydb=> select count(*) from table_fenqu_201011;  
count  
-------  
 2  
(1 row)

mydb=> select count(*) from table_fenqu_201009;  
count  
-------  
 1  
(1 row)

不加 Only 属性 Truncate 父表

不加Only属性 Truncate父表数据，看下有什么结果 ，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
mydb=> truncate table table_fenqu;  
TRUNCATE TABLE

mydb=> select count(*) from table_fenqu;  
count  
-------  
 0  
(1 row)

mydb=> select count(*) from only table_fenqu_201009;  
count  
-------  
 0  
(1 row)

这里说明，不加only时,所有数据,包括子表都被清空了.

重新插入测试数据，验证加’only’的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
mydb=> insert into table_fenqu_201009 (id,addtime) values (1,'2010-09-01 00:00:00');  
INSERT 0 1  
mydb=> insert into table_fenqu_201010 (id,addtime) values (1,'2010-10-01 00:00:00');  
INSERT 0 1  
mydb=> insert into table_fenqu_201011 (id,addtime) values (1,'2010-11-01 00:00:00');  
INSERT 0 1  
mydb=> insert into table_fenqu_201011 (id,addtime) values (1,'2010-11-02 00:00:00');  
INSERT 0 1  
mydb=> select count(*) from table_fenqu;  
count  
-------  
 4  
(1 row)
mydb=> insert into table_fenqu (id,addtime) values (1,'2010-11-02 00:00:00');  
INSERT 0 1  

mydb=> select count(*) from table_fenqu;  
count  
-------  
 5  
(1 row)

mydb=> truncate table only table_fenqu;  
TRUNCATE TABLE  

mydb=> select count(*) from table_fenqu;  
count  
-------  
 4  
(1 row)

这里可以看出，加only时，只清空父表的一条数据,而子表的数据保留着。

1
2
3
4
5
 
mydb=> select count(*) from only table_fenqu;  
count  
-------  
 0  
(1 row)

Truncate 语法官方文档

Name
TRUNCATE ― empty a table or set of tables
Synopsis
TRUNCATE [ TABLE ] [ ONLY ] name [, … ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

name
The name (optionally schema-qualified) of a table to be truncated. If ONLY is specified, only that table is truncated.
If ONLY is not specified, the table and all its descendant tables (if any) are truncated.

原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/236340.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论