Postgresql的批量数据删除

导入postgresql

导入数据库是用 copy from，具体命令如下：

copy tablename from file CSV HEADER DELIMITER ',' ENCODING 'GBK';

参数说明：csv 表示文件格式

 

                 header 表示文件第一行为字段名，导入数据库时忽略首行

 

                 delimiter 表示字段分割方式，示例是以逗号分割

 

                 encoding 表示文件编码方式，示例是 GBK 编码，（默认为utf-8）

 

从postgresql导出

导出数据时用 copy to，具体命令如下：

 

copy tablename(a,b) to filename delimiter ',' csv header encoding 'GBK';

参数说明：与导入的参数一致

 

如果需要将查询结果导出，则：

 

copy (select * from tablename) to filename delimiter ',' csv header encoding 'GBK';

注意点：1）如果需要把表中所有数据都导出，则不需要注明字段名

 

              2）数据库用户需要有写文件的权限，postgresql默认的有权限路径是在/tmp 下

 

在 mysql 中如果需要清空表，只需要

TRUNCATE table_name;

即可，如果有自增的 id 字段，也会还原回 1，

但是 postgresql 与 mysql 稍有不同，postgresql 的自增字段 是通过 序列 sequence来实现的，

所以在清空表的时候，还需要还原序列

TRUNCATE TABLE table_name RESTART IDENTITY;

当需要对一些不需要的历史数据进行大批量删除时， 在使用delete语句时，会发现在删除一些数据时会非常慢

比如 DELETE FROM test where id < 10000;

删除缓慢的原因主要在于外键约束，当数据库在有约束的情况下，无论进行删除或者更新操作， 都会对相关表进行一个校验，判断相关表的相关记录是否被删除或者更新。 这个检查的过程会非常慢， 尤其在外建表又关联着外建表的这种层层嵌套的情况下。

解决方法: 

ALTER TABLE test DISABLE TRIGGER ALL;

这样就会发现，删除的速度会大幅度上升， 之前对于操作过的嵌套表删除时，从几十分钟到1分钟以内，瞬间提速很多。

在删除完数据后， 一定要记得把trigger改回来， 调用：

ALTER TABLE test ENABLE TRIGGER ALL;

在删除完数据后，再重新对物理表进行一个优化

1.  使用vacuum（维护数据库磁盘的工具）删除那些已经被标记为删除的数据，并释放空间。但是vacuum工具不能对相应的索引进行清理，所以需要第二步重建索引

vacuum FULL test

2. 重新建立索引提高查询效率 

REINDEX TABLE test