法一:
1.创建临时表
create table test_view as select * from test where date(to_timestamp(endtime))>='2012-09-02';
2.导出临时表数据为文本
copy test_view to '/home/postgres/test_view.txt' with delimiter as '|';
3.导入文件数据到数据库中指定表
delete from test;
copy test from '/home/postgres/test_view.txt' with delimiter as '|';
法二:其实使用copy 不需要新建临时表
导出命令:
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
可以使用查询语句来制定要导出的数据
COPY (select * from user where name='lisi') TO '/tmp/data/test.csv' WITH csv;
导入命令:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
COPY user_1 FROM '/tmp/data/test.csv' WITH csv;
注意:
第一点:
copy命令必须在plsql命令行执行,执行用户必须为superuser,否则会提示:
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
普通用户进行执行,需要在copy前面加入 “\”,即 \copy即可
第二点:
如果导出的字段,有integer[]类型,直接导出,再导入的话,会有问题,解决办法是需要在导出的时候,进行处理:
\COPY ( select coalesce(integer_array, '{}')::integer[] as integer_array from table ) TO '/tmp/data.csv' with csv header;