注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

Using Only when truncating parent table  

2010-12-02 12:37:25|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

--创建父表
mydb=> create table table_fenqu (id integer, addtime timestamp without time zone);
CREATE TABLE
  
--创建三张子表                                     ^
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

--查看字段属性
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.)

--插入几条测试数据
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

Using  Only when truncating parent table - francs - My DBA LIFE

--查看父表数据
mydb=> select count(*) from table_fenqu;
 count
-------
     4
(1 row)

--查看子表数据
mydb=> select count(*) from table_fenqu_201011;
 count
-------
     2
(1 row)

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

--不加only,truncate 父表,看下有什么结果
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'的情况
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时,只清空父表的一条数据,而子表的数据保留着。

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.

  评论这张
 
阅读(25749)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016