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不需要超级用户权限，普通用户即可执行