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