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

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

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

  1. FROM:指定源文件或输入数据流。可以是本地文件路径或服务器文件路径,也可以是一个输入流。如果是一个输入流,则必须使用STDIN关键字。

  2. DELIMITER:指定分隔符。默认为制表符(" \t "),可以使用其他字符或字符串。例如:DELIMITER ‘;’ 表示用分号作为分隔符。

  3. HEADER:指定是否包含标题行。默认情况下不包含标题行,如果源文件包含标题行,则需要使用HEADER关键字。

  4. NULL:指定NULL值的表示方式。默认情况下,需要使用"\N"表示NULL值,如果输入数据使用其他的表示方式,可以使用这个选项。例如:NULL ‘null’ 表示用"null"作为NULL值的表示方法。

  5. ENCODING:指定编码方式。默认是UTF8编码。如果需要使用其他编码方式,可以使用ENCODING关键字。例如:ENCODING ‘GBK’ 表示采用GBK编码方式。

  6. QUOTE:指定数据中使用的引号。默认情况下,使用双引号(")作为引号。如果输入数据使用其他的引号,可以使用这个选项。例如:QUOTE ‘’’ 表示使用单引号(’)作为引号。

  7. ESCAPE:指定转义字符。默认情况下,使用反斜杠("")作为转义字符。如果需要采用其他转义字符,可以使用ESCAPE关键字。例如:ESCAPE ‘|’ 表示使用竖线(|)作为转义字符。

  8. FORMAT:输入数据的格式。默认为文本格式(TEXT)。可选的格式包括二进制格式(BINARY)和CSV格式(CSV)。例如:FORMAT binary 表示采用二进制格式。

  9. FILENAME:指定输出文件的路径。不是必需的参数,只有在导出数据到文件时才需要使用。

  10. ON CONFLICT:指定冲突解决策略。只有在导入数据到表时才需要使用。通常情况下,如果目标表中已经存在要插入的行,会报错。这时,可以使用ON CONFLICT关键字指定冲突解决策略。例如:ON CONFLICT DO NOTHING 表示如果目标表中已经存在要插入的行,则不执行任何操作,跳过这个行;ON CONFLICT DO UPDATE SET 列名=EXCLUDED.列名 表示如果目标表中已经存在要插入的行,则更新该行数据。其中,EXCLUDED表示要插入的行。

  11. HEADER:指定表头所在的行数。只有在导入CSV格式文件时才需要使用。默认为1,表示第一行是表头行。如果表头行不是第一行,需要使用HEADER_ROW关键字指定表头所在的行数。例如:HEADER_ROW 2 表示表头位于第二行。

  12. 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就是导入的语法。

### PostgreSQL 使用 COPY 命令导入导出数据 #### 导入数据 `COPY` 命令是 PostgreSQL 中用于批量操作的主要工具之一,可以高效地将大量数据从文件加载到表中。对于从 CSV 文件导入数据的情况,基本语法结构如下: ```sql COPY table_name FROM 'file_path' WITH (FORMAT csv); ``` 这条语句会把位于 `file_path` 路径下的 CSV 文件的数据复制到名为 `table_name` 的表格里[^1]。 为了更具体展示如何执行这一过程,这里给出一个完整的例子来说明怎样利用 `COPY` 将外部存储设备上的 CSV 数据迁移到数据库内部的一个特定表内: 假设有一个叫作 `employees.csv` 的员工记录文件,目标是要把这些信息存放到已存在的 `employee_records` 表格当中,则相应的 SQL 语句应该是这样的形式: ```sql COPY employee_records FROM '/path/to/your/file/employees.csv' WITH (FORMAT csv, HEADER true); ``` 此命令不仅指定了要处理的目标表名 (`employee_records`) 和源文件位置 (`'/path/to/your/file/employees.csv'`) ,还额外设置了参数 `(FORMAT csv, HEADER true)` 来表明输入文件采用的是逗号分隔值格式并且首行包含了列标题。 #### 导出数据 同样地,如果想要把某个表的内容保存成 CSV 文件以便后续分析或者其他用途,也可以借助于相同的 `COPY` 功能实现反向操作——即从数据库提取数据至文件系统。其一般表达式为: ```sql COPY table_name TO 'output_file_path' WITH (FORMAT csv); ``` 这将会创建一个新的 CSV 文件并将选定表内的所有条目按照指定路径写出。例如,要把整个 `sales_data` 表转储为 `/tmp/sales_report.csv` 文件的话,就应当编写如下所示的查询指令: ```sql COPY sales_data TO '/tmp/sales_report.csv' WITH (FORMAT csv, HEADER true); ``` 上述代码片段展示了如何设置输出选项以确保生成的结果集具有可读性强的标准格式,并且保留原始字段名称作为头部信息的一部分。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值