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工具。