3. copy 使用及示例

1.copy概述

https://www.postgresql.org/docs/14/sql-copy.html

• copy 命令用于表与文件(和标准输出，标准输入)之间的相互拷贝；
• copy to由表至文件，copy from由文件至表；
• copy 命令是到数据库服务端找文件，以超级用户执行导入导出权限，适合数据库管理员操作;
• \copy命令在客户端执行导入客户端的数据文件，权限要求没那么高，适合开发，测试人员使用；
• copy与\copy 命令都能实现数据文件与表的数据传递，两者都在psql环境下执行
• 每个运行的后端都会在视图COPY中报告其进度。pg_stat_progress_copy有关详细信息，请参阅官方文档第 28.4.6 节。

如果指定了列的表，则COPY TO仅将指定列中的数据复制到文件中。对于COPY FROM，文件中的每个字段都按顺序插入到指定的列中。未在列列表中指定的表列COPY FROM将接收其默认值。

注意事情:
1.copy 是数据库命令需要登录到数据库中执行
2.copy 一次只能导出一张表，但是可以定制部份数据，或部份例。更灵活。

2.copy使用

s2db=# \help copy
Command:     COPY
Description: copy data between a file and a table
Syntax:
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'

常用参数说明:

• table_name：现存表的名字(可以有模式修饰)
• column_name：可选的待拷贝字段列表。如果没有声明字段列表，那么将使用所有字段
• Query：一个必须用圆括弧包围的SELECT或VALUES命令，其结果将被拷贝
• Filename：输入或输出文件的路径名。输入文件名可以是绝对或是相对的路径，但输出文件名必须是绝对路径。 Windows用户可能需要使用E”字符串和双反斜线作为路径名称
• PROGRAM：需执行的程序名。在COPY FROM命令中，输入是从程序的标准输出中读取，而在COPY TO中，命令的输出会作为程序的标准输入。
  注意:
  程序一般是在命令行界面下执行，当用户需要传递一些变量给程序时，如果这些变量的来源不是可靠的，用户必须小心过滤处理那些对命令行界面来说是有特殊意义的字符。基于安全的原因，最好是使用固定的命令字符串，或者至少是应避免直接使用用户输入（应先过滤特殊字符）
• STDOUT：声明输入将写入客户端应用
• WITH(FORMAT)：选择被读或者写的数据格式：text、csv（逗号分隔值），或者binary。 默认是text

3. copy示例

3.1 导出CSV

# 命令：
COPY { table_name [ ( column_name [, ...] ) ] | ( query) }    
TO { 'filename' | PROGRAM 'command' | STDOUT }   
 [ [ WITH ] (option [, ...] ) ] 
copy to的导出速度非常之快，经测试10W的数据量只需要3秒左右的时间

# 示例：
COPY user TO '/tmp/data/test.csv' WITH csv;

# 导出指定的属性：
COPY user(name,password) TO '/tmp/data/test.csv' WITH csv;

# 使用select 语句：
COPY (select * from user where   id=) TO '/tmp/data/test.csv' WITH csv;

# 指定要导出哪些字段：
COPY (select name,age from user) TO '/tmp/data/test.csv'WITH csv header;


# 通过命令的方式，压缩
COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz' WITH csv;

#  指定分隔符
COPY country TO STDOUT (DELIMITER '|');

3.2 导入CSV

# 命令：
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

# 示例：导入命令基本与导出一样，只是将TO 改为 FROM
COPY user_1 FROM '/tmp/data/test.csv' WITH csv;

# 如果导出的时候，指定了header属性，那么在导入的时候，也需要指定：
COPY user_1(name, age) FROM '/tmp/data/test.csv' WITH csv header;

4.copy 进度查询

每当COPY运行时，视图将为当前正在运行命令pg_stat_progress_copy的每个后端包含一行。COPY下表描述了将要报告的信息，并提供了有关如何解释它的信息。
该视图是 PG 14 新增的： https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING
关于进度查询，顺便提一句，目前支持进度报告的唯一命令是ANALYZE、CLUSTER、CREATE INDEX、VACUUM、COPY和BASE_BACKUP（即pg_basebackup发出的复制命令以进行基本备份），后将增加更多。

SELECT  
         S.pid AS pid, S.datid AS datid, D.datname AS datname,  
       S.relid AS relid,  
       CASE S.param5 WHEN 1 THEN 'COPY FROM'  
                     WHEN 2 THEN 'COPY TO'  
                     END AS command,  
       CASE S.param6 WHEN 1 THEN 'FILE'  
                     WHEN 2 THEN 'PROGRAM'  
                     WHEN 3 THEN 'PIPE'  
                     WHEN 4 THEN 'CALLBACK'  
                     END AS "type",  
       S.param1 AS bytes_processed,  
       S.param2 AS bytes_total,  
       S.param3 AS lines_processed  
       S.param3 AS tuples_processed,  
       S.param4 AS tuples_excluded  
   FROM pg_stat_get_progress_info('COPY') AS S  
       LEFT JOIN pg_database D ON S.datid = D.oid;

来自为知笔记(Wiz)