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;
