简单介绍PostgreSQL中COPY与\COPY的异同

数据库杂记 2023-03-30

1、前言

PG中针对快速COPY数据，有两条命令与之对应。很多同学，不怎么爱看文档，很容易把这两个搞混。

2、比较

COPY TO/FROM语法：

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where option can be one of:

    FORMAT format_name
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

复制

9.0及以前的老语法：

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ DELIMITER [ AS ] 'delimiter_character' ]
          [ NULL [ AS ] 'null_string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote_character' ]
                [ ESCAPE [ AS ] 'escape_character' ]
                [ FORCE NOT NULL column_name [, ...] ] ] ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | STDOUT }
    [ [ WITH ]
          [ BINARY ]
          [ DELIMITER [ AS ] 'delimiter_character' ]
          [ NULL [ AS ] 'null_string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote_character' ]
                [ ESCAPE [ AS ] 'escape_character' ]
                [ FORCE QUOTE { column_name [, ...] | * } ] ] ]

复制

\COPY的语法与之完全一样。只不过，它是元命令。

两者具体的区别：

1. COPY 命令用于PostgreSQL表和标准文件系统文件之间交换数据

2. COPY TO 把一个表的所有内容都拷贝到一个文件

3. COPY FROM 从一个文件里拷贝数据到一个表里（把数据附加到表中已经存在的内容里）。

4. COPY 命令从PostgreSQL 服务器直接读写文件，因此文件必须让PostgreSQL 用户能够访问到。

5. 该命令使用的文件是数据库服务器直接读写的文件，不是客户端应用的文件，因此必须位于服务器本地或被直接访问的文件，而不是客户端位置

而 \copy 元命令则不一样：

\copy ... **perform SQL COPY with data stream to the client host** 是要把数据流传到客户端主机上。

注意两者之间的区别。再看下边的示例：

mydb=> copy t2 to 'pgccc/tmp/t2.csv' with csv;
ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
mydb=> \copy t2 to 'pgccc/tmp/t2.csv' with csv;
pgccc/tmp/t2.csv: No such file or directory
mydb=> \copy t2 to '/pgccc/tmp/t2.csv' with csv;
COPY 1000

复制

copy是superuser专用的。而\copy则不是专用的。普通用户就可以使用。

copy的示例：

mydb=# set search_path= "$user",mydb,postgres,public;
SET
mydb=# \d
 mydb     | bonus    | 数据表 | mydb
 mydb     | dept     | 数据表 | mydb
 mydb     | emp      | 数据表 | mydb
 mydb     | salgrade | 数据表 | mydb
 mydb     | t        | 数据表 | scott
 public   | weather  | 数据表 | postgres
mydb=# copy emp to '/export/dmp/emp.dat' CSV HEADER;
COPY 14

mydb=# \! cat /export/dmp/emp.dat
empno,ename,job,mgr,hiredate,sal,comm,deptno
7369,SMITH,CLERK,7902,1980-12-17,800.00,,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600.00,300.00,30
7521,WARD,SALESMAN,7698,1981-02-22,1250.00,500.00,30
7566,JONES,MANAGER,7839,1981-04-02,2975.00,,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250.00,1400.00,30
mydb=# create table emp_2 (like emp);
CREATE TABLE
mydb=# copy emp_2 from '/export/dmp/emp.dat' CSV HEADER;
COPY 14

复制

注意: \\! 执行的命令是psql命令行所在的host上的OS命令。这个确实有点绕。而copy to/copy from是直接执行的服务器上的路径。

grant pg_write_server_files to zdb;　　这样才有权限

copy mywords(word) to '/pg/pgdata/mywords.csv'; server 上的路径

\copy的示例：

mydb=# create table t(id int, col2 varchar(32), col3 varchar(32));
CREATE TABLE
mydb=# insert into t values(1, 'abc,def', 'abc,aaa');
INSERT 0 1
mydb=# \copy t to '/pgccc/tmp/t.csv' csv;
COPY 1

mydb-# \! cat /pgccc/tmp/t.csv
1,"abc,def","abc,aaa"

mydb=# truncate table t;
TRUNCATE TABLE
mydb=# \copy t from '/pgccc/tmp/t.csv' csv quote '"';
COPY 1
mydb=# select * from t;
 id |  col2   |  col3
----+---------+---------
  1 | abc,def | abc,aaa
(1 row)

复制

\copy from STDIN, to STDOUT的使用示例：

mydb=# \copy t1 from STDIN with csv;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1, 'wang'
-- 按下Ctrl +D 键, 发出EOF信号
>> COPY 1

mydb=# \copy t1 from STDIN with csv;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 2, 'ttt'
>> \.
COPY 1
这时直接以符号'\.'表示结束

mydb=# \copy t1 to STDOUT;
2        'wang'
1        'wang'
2        'ttt'

复制

[ END ]


复制


复制

文章转载自数据库杂记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

简单介绍PostgreSQL中COPY与\COPY的异同

1、前言

\copy from STDIN, to STDOUT的使用示例：

[ END ]

评论

相关阅读