欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

使用psqlcopy命令导入导出数据的方法,copy与\copy的区别

程序员文章站 2022-03-02 22:40:08
test=> \conninfo you are connected to database "test" as user "a" via socket...

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