使用psqlcopy命令导入导出数据的方法，copy与\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工具。