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

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