031.PGSQL-psql-导入导出数据COPY 和\copy命令

COPY  sql命令  、超级用户、更快

\copy  元命令、无需超级用户

COPY 导入大量数据的效率要比INSERT语句高很多

https://www.postgresql.org/docs/10/sql-copy.html

https://www.cnblogs.com/alianbog/p/5621660.html

COPY 导出数据

先用超级用户postgres登录对应的数据库

psql -h localhost -p 5432 mydb postgres

然后导出数据为csv

 COPY (select * from myschema.o_ls_test  limit 10) to '/var/lib/pgsql/13/o_ls_test.csv' with csv header;

[postgres@s101 /usr/pgsql-13]$psql -h localhost -p 5432 mydb postgres
Password for user postgres:
psql (9.2.24, server 13.3)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

mydb=# COPY (select * from myschema.o_ls_test  limit 10) to '/var/lib/pgsql/13/o_ls_test.csv' with csv header;
COPY 10

COPY 导入数据（表的字段>=csv的列数）

mydb=# copy myschema.o_ls_test(rid,name,timeflag) from  '/var/lib/pgsql/13/o_ls_test.csv'   delimiter ',' csv header;
COPY 10