在postgresql数据库中copy指令的应用

necessary653

已于 2023-07-19 09:57:15 修改

阅读量8.5k

点赞数 3

文章标签：数据库 postgresql sql

于 2023-06-17 23:12:06 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_73350116/article/details/131258781

版权

COPY是PostgreSQL数据库中用于高效导入和导出数据的命令，支持TEXT、BINARY和CSV格式。COPYFROM在事务中执行时，错误会导致数据回滚，而COPYTO则不会。该命令允许设置分隔符、NULL值表示、编码、引用字符等参数，且在处理视图和分区表时有特定的用法。示例展示了如何使用COPYTO和COPYFROM进行数据的导出和导入操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

copy .. to ..

copy .. from ..

copy,\copy在postgresql数据库中组要用于数据的导入导出，是一个效率极高的指令。可以在psql上进行执行

copy from 会受事务影响当报错时会发生数据回滚

copy to 当进程报错时，数据不会发生回滚，会数据导出中断。

查看相关参数信息

\h copy

描述：在文件和表之间复制数据

FROM：指定源文件或输入数据流。可以是本地文件路径或服务器文件路径，也可以是一个输入流。如果是一个输入流，则必须使用STDIN关键字。
DELIMITER：指定分隔符。默认为制表符（" \t "），可以使用其他字符或字符串。例如：DELIMITER ‘;’ 表示用分号作为分隔符。
HEADER：指定是否包含标题行。默认情况下不包含标题行，如果源文件包含标题行，则需要使用HEADER关键字。
NULL：指定NULL值的表示方式。默认情况下，需要使用"\N"表示NULL值，如果输入数据使用其他的表示方式，可以使用这个选项。例如：NULL ‘null’ 表示用"null"作为NULL值的表示方法。
ENCODING：指定编码方式。默认是UTF8编码。如果需要使用其他编码方式，可以使用ENCODING关键字。例如：ENCODING ‘GBK’ 表示采用GBK编码方式。
QUOTE：指定数据中使用的引号。默认情况下，使用双引号（"）作为引号。如果输入数据使用其他的引号，可以使用这个选项。例如：QUOTE ‘’’ 表示使用单引号（’）作为引号。
ESCAPE：指定转义字符。默认情况下，使用反斜杠（""）作为转义字符。如果需要采用其他转义字符，可以使用ESCAPE关键字。例如：ESCAPE ‘|’ 表示使用竖线（|）作为转义字符。
FORMAT：输入数据的格式。默认为文本格式（TEXT）。可选的格式包括二进制格式（BINARY）和CSV格式（CSV）。例如：FORMAT binary 表示采用二进制格式。
FILENAME：指定输出文件的路径。不是必需的参数，只有在导出数据到文件时才需要使用。
ON CONFLICT：指定冲突解决策略。只有在导入数据到表时才需要使用。通常情况下，如果目标表中已经存在要插入的行，会报错。这时，可以使用ON CONFLICT关键字指定冲突解决策略。例如：ON CONFLICT DO NOTHING 表示如果目标表中已经存在要插入的行，则不执行任何操作，跳过这个行；ON CONFLICT DO UPDATE SET 列名=EXCLUDED.列名表示如果目标表中已经存在要插入的行，则更新该行数据。其中，EXCLUDED表示要插入的行。
HEADER：指定表头所在的行数。只有在导入CSV格式文件时才需要使用。默认为1，表示第一行是表头行。如果表头行不是第一行，需要使用HEADER_ROW关键字指定表头所在的行数。例如：HEADER_ROW 2 表示表头位于第二行。
FORMAT：指定自定义格式。只有在采用自定义格式时才需要使用。在使用自定义格式时，需要使用FORMAT关键字指定格式类型。例如：FORMAT text 表示要导出的数据是文本格式。

导出示例

copy .. to ..

copy extract_eval to '/home/postgres/extract_eval.csv' with  csv header delimiter '|';

header:指定代表头（列名称），delimiter：指定分隔符为‘|’（默认情况下时逗号，多数情况也建议大家采用默认值，当字段值值含有逗号时，此时导出后再原数导入就会报错），csv指定导出格式

此时文件已经导出

导出为text格式

copy extract_eval to '/home/postgres/extract_eval.text' with (FORMAT text )   ;

导出为text格式就不需要指定delimiter 分隔符,其他展示为空格分隔

导出为binary格式（二进制格式）

copy extract_eval to '/home/postgres/extract_eval.binary' with  binary   ;

内容会展示为二进制源码文件，

在以上的导出测试中 tablename 也可以更改为select * from tablename where condtion

可以给你增加条件过滤导出数据集

COPY TO只能被用于纯粹的表，不能用于视图。不过你可以写COPY (SELECT * FROM viewname) TO ...来拷贝一个视图的当前内容。

COPY只处理提到的表，它不会从子表复制数据或者复制数据到子表中。倘如你导出的是一个分区表，copy tablename to 此时它只会导出only tablename的数据，并不会连带子表数据导出，例如 COPY table TO 会显示与SELECT * FROM ONLY table相同的数据。而COPY (SELECT * FROM table) TO ... 可以被用来转储一个继承层次中的所有数据。

导出分区表时

使用

copy main.extract_train to '/home/postgres/partition.csv' with csv header;

报错

修正

copy (select *  from main.extract_train ) to '/home/postgres/partition.csv' with csv header;

导出成功

注意：导出视图时和导出分区表写法一样需要写（copy （select * from viewname）to '/path/filename'）

导入数据示例

cpoy .. from .. 示例

copy extract_eval from  '/home/postgres/extract_eval.binary' with  binary   ;

上文中copy ..to.. 中将to 改为from就是导入的语法。