psql copy命令导入导出数据

test=> \conninfo
You are connected to database "test" as user "a" via socket in "/tmp" at port "5432".
test=> select * from test;
 id 
----
  1
(1 row)


test=> copy (select * from test) to '/tmp/a.txt' 
;
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.
test=> 


test=> \du
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 a           |                                                            | {}
 b           |                                                            | {}
 postgres9.6 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test        |                                                            | {}


 
 test=> \c test postgres9.6
You are now connected to database "test" as user "postgres9.6".
test=# \conninfo
You are connected to database "test" as user "postgres9.6" via socket in "/tmp" at port "5432".
test=# copy (select * from a.test) to '/tmp/a_test.txt'
;
COPY 1


test=# \! cat /tmp/a_test.txt
1


test=# copy a.test from '/tmp/a_test.txt'
;
COPY 1
test=# select * from a.test;
 id 
----
  1
  1
(2 rows)


test=# \copy a.test from '/tmp/a_test.txt'
;
COPY 1
test=# select * from a.test;
 id 
----
  1
  1
  1
(3 rows)


test=# \copy (select * from a.test) to '/tmp/a_test.txt'
;
COPY 3
test=# \! cat /tmp/a_test.txt
1
1
1




test=# \conninfo
You are connected to database "test" as user "postgres9.6" via socket in "/tmp" at port "5432".
test=# \c test a
You are now connected to database "test" as user "a".
test=> \copy (select * from a.test) to '/tmp/a_test.txt'
COPY 3
test=> \! cat /tmp/a_test.txt
1
1
1
test=> copy a.test from '/tmp/a_test.txt';
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.
test=> \copy a.test from '/tmp/a_test.txt';
COPY 3
test=> select * from test;
 id 
----
  1
  1
  1
  1
  1
  1
(6 rows)


我们将文件的扩展名命名为.csv，但生成的文件不是真的用逗号隔开，它使用默认格式，使用tab作为列分隔符；对于csv格式的输出，必须添加with csv选项，此时以逗号作为分隔符：
copy (select * from myt) to '/tmp/myt.csv' with csv;




highgo=# copy test to '/tmp/test.csv';
COPY 2
highgo=# \! head /tmp/test.csv
1       aaaaa
2       bbbbb
highgo=# copy test to '/tmp/test.csv' with csv;
COPY 2
highgo=# \! head /tmp/test.csv
1,aaaaa
2,bbbbb


如果想要在输出文件中显示列名，则需要添加HEADER选项：
copy (select * from myt) to '/tmp/myt.csv' with csv HEADER;


highgo=# copy test to '/tmp/test.csv' with csv header;
COPY 2
highgo=# \! head /tmp/test.csv
id,name
1,aaaaa
2,bbbbb
highgo=# copy (select * from test) to '/tmp/test.csv' with csv header;
COPY 2
highgo=# \! head /tmp/test.csv
id,name
1,aaaaa
2,bbbbb






copy与\copy的区别是：


copy必须使用能够超级用户使用；
copy .. to file ,copy file to ..中的文件都是数据库服务器所在的服务器上的文件。
\copy 一般用户即可执行
\copy 保存或者读取的文件是在客户端所在的服务器
　　比如当使用192.168.17.53连上192.168.17.52的数据库,使用copy tb1 to ‘/home/postgres/aa.txt’,该文件是存放在192.168.17.52上； 
　　当使用\copy时候就会把文件存放到客户端所在的服务器上，即使用\copy tb1 to ‘/home/postgres/aa.sql’，该文件是存放在192.168.17.53上；


使用\copy是备份到客户端上。
恢复的时候也是一样，使用copy是从服务端寻找文件，使用\copy是从客户端上寻找文件。






\copy命令支持的默认分隔符是制表符。
如果源文件使用了一些非标准的分隔符，比如竖杠“|”，那么也请在命令中指明：
\copy sometable from somefile.txt DELIMITER '|';


如果希望把文本中的控制替换为别的内容再导入，可以用NULL AS来标记要替换的内容：
\copy sometable from somefile.txt NULL AS '';


*****************************************************************
当我们从表中复制数据到一个文件，文件中已经存在的数据将被覆盖；当我们从一个文件复制数据到一个表时，数据被附加到表中已存在的数据上。
COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.


copy操作是在数据库和文件之间直接读或写。
COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. 
--from：https://www.postgresql.org/docs/9.6/static/sql-copy.html
*****************************************************************
postgres9.3在copy中添加了PROGRAM选项。所以，我们现在可以执行复制命令，并在输出成文件之前使用如awk或sed这样的程序来处理/操作数据，使用ZIP压缩数据等；
test-=# copy myt to PROGRAM 'grep "FIRST" > /tmp/file.csv';
test-=# \! cat /tmp/file.csv
1  First record


copy会停在出现错误的第一个错误处，在错误发生之前插入的行将是不可见的或不可访问的。如果我们从文件复制成千上万的记录，然后错误发生在最近的几条记录中，这可能是一个问题。该表将占用磁盘上的空间，兵器哲学数据将无法访问。因此，更好的做法是确保数据是干净和正确格式化的（如果数据量大的话）。
如果想继续加载过程而忽略错误，可以使用pg_bulkload工具。