1、导出csv格式数据(Tab符作为分隔符)
copy (select * from public.system_history where ctime <'2014-12-27 00:00:00' limit 10) to '/home/gpadmin/goopand/temp/public.system_history.dat' header delimiter E'\t' csv ;
2、导入文本格式数据('|'符作为分隔符)
copy public.country from '/home/gpadmin/goopand/temp/public.country.dat' with delimiter '|' log errors into err_country segment reject limit 10 rows;
COPY nation FROM '/opt/PostgreSQL/9.4/workspace/tpch_2_15_0/dbgen/nation.csv' WITH (FORMAT csv, DELIMITER '|');
copy命令详细如下:
bass_gp=# \h copy
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY table [(column [, ...])] FROM {'file' | STDIN}
[ [WITH]
[OIDS]
[HEADER]
[DELIMITER [ AS ] 'delimiter']
[NULL [ AS ] 'null string']
[ESCAPE [ AS ] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[CSV [QUOTE [ AS ] 'quote']
[FORCE NOT NULL column [, ...]]
[FILL MISSING FIELDS]
[ [LOG ERRORS INTO error_table] [KEEP]
SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}
[ [WITH]
[OIDS]
[HEADER]
[DELIMITER [ AS ] 'delimiter']
[NULL [ AS ] 'null string']
[ESCAPE [ AS ] 'escape' | 'OFF']
[CSV [QUOTE [ AS ] 'quote']
[FORCE QUOTE column [, ...]] ]
bass_gp=#