postgresql copy和\copy导出命令
COPY命令导入导出
导入数据到数据库中,COPY 命令只能用超级用户执行,普通用户执行会报错
========================表中数据===============
[postgres@oraclehost pg_tbs]$ cat a.txt
1 a
2 b
3 c
4 d
5 e
[postgres@oraclehost pg_tbs]$ cat t
tbs_mydb/ testcopy_import.txt
[postgres@oraclehost pg_tbs]$ cat testcopy_import.txt
1 1_francs
2 2_francs
3 3_francs
4 4_francs
5 5_francs
6 6_francs
7 7_francs
8 8_francs
9 9_francs
10 10_francs
====================================================
mydb=# \d+ test_copy
Table "public.test_copy"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
mydb=#
mydb=# copy test_copy from '/vastbase/postgres/10/pg_tbs/a.txt'(DELIMITER ' ');
COPY 5
mydb=#
mydb=# copy test_copy from '/vastbase/postgres/10/pg_tbs/testcopy_import.txt'(DELIMITER ' ');
**导入后查询**
mydb=# select * from test_copy;
id | name
----+-----------
1 | a
2 | b
3 | c
4 | d
5 | e
1 | 1_francs
2 | 2_francs
3 | 3_francs
4 | 4_francs
5 | 5_francs
6 | 6_francs
7 | 7_francs
8 | 8_francs
9 | 9_francs
10 | 10_francs
copy带出CSV格式
mydb=# copy test_copy to '/vastbase/postgres/10/pg_tbs/cc.csv' with CSV HEADER;
COPY 15
copy导出到标准输出stdout和普通文件中
mydb=# copy test_copy to stdout;
1 a
2 b
3 c
4 d
5 e
1 1_francs
2 2_francs
3 3_francs
4 4_francs
5 5_francs
6 6_francs
7 7_francs
8 8_francs
9 9_francs
10 10_francs
mydb=# copy test_copy to '/vastbase/postgres/10/pg_tbs/bb.txt';
COPY 15
copy导出指定数据到某个文件
COPY (SELECT * FROM test_copy WHERE id=xxx) TO '/vastbase/postgres/10/pg_tbs/bb.txt'
\copy和COPY命令语法相同,单区别为,\copy是从psql客户端导入导出文件,而COPY是从服务端导入导出文件,并且\copy不需要超级用户权限,普通用户即可执行