合 PG中的SQL copy和元命令copy命令
Tags: PGPostgreSQL数据迁移导出数据COPY文本文件导入数据
COPY和“\copy”的区别
psql支持文件数据导入到数据库,也支持数据库表数据导出到文件中。 COPY命令和\copy 命令都支持这两类操作,但两者有如下区别:
- COPY 命令是SQL命令,\copy 命令是元命令。
- 执行COPY 命令的用户必须具有SUPERUSER超级权限(将数据通过stdin/stdout方式导入导出情况除外)或授予了默认角色
pg_read_server_files
、pg_write_server_files
及pg_execute_server_program
之一的用户,而 \copy 元命令不需要SUPERUSER权限。 - COPY 命令读取或写入数据库服务端主机上的文件,而 \copy 元命令是从psql客户端主机读取或写入文件。
- 从性能方面看, 大数据量导出文件或大文件数据导入数据库,COPY 比 \copy 性能高。
COPY命令
COPY — 在一个文件和一个表之间复制数据
大纲
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 [, ...] ) ] 其中 option 可以是下列之一: 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' |
描述
COPY
在 PostgreSQL表和标准文件系统文件之间 移动数据。COPY TO
把一个表的内容复制 到一个文件,而COPY FROM
则从一个文件复制数据到一个表(把数据追加到表中原有数 据)。COPY TO
也能复制一个 SELECT
查询的结果。
如果指定了一个列列表,COPY TO
将只把指定列的数据复制到文件。对于COPY FROM
,文件中的每个字段将按顺序插入到指定列中。COPY FROM
命令的列列表中没有指定的表列则会采纳其默认值。
带一个文件名的COPY
指示 PostgreSQL服务器直接从一个文件读取 或者写入到一个文件。该文件必须是 PostgreSQL用户(运行服务器的用户 ID) 可访问的并且应该以服务器的视角来指定其名称。当指定了 PROGRAM
时,服务器执行给定的命令并且从该程序的标准 输出读取或者写入到该程序的标准输入。该程序必须以服务器的视角指定,并且 必须是PostgreSQL用户可执行的。在指定 STDIN
或者STDOUT
时,数据会通过客 户端和服务器之间的连接传输。
参数
table_name
一个现有表的名称(可以是模式限定的)。
column_name
可选的要被复制的列列表。如果没有指定列列表,则该表的所有列除了生成的列都会被复制。
query
其结果要被复制的SELECT、 VALUES、 INSERT、UPDATE或者 DELETE命令。注意查询周围的圆括号是必要的。对于
INSERT
、UPDATE
以及DELETE
查询,必须提供一个 RETURNING 子句并且 目标关系不能具有会扩展成多条语句的条件规则、ALSO
规则或者INSTEAD
规则。filename
输入或者输出文件的路径名。一个输入文件的名称可以是一个绝对或相对路径, 但一个输出文件的名称必须是绝对路径。Windows 用户可能需要使用一个
E''
字符串并且双写路径名称中使用的任何反斜线。PROGRAM
一个要执行的命令。在
COPY FROM
中,输入 将从该命令的标准输出读取,而在COPY TO
中,输出会 写入到该命令的标准输入。注意该命令是由 shell 调用,因此如果你需要传递任何来自不可信来源的 参数给 shell 命令,你必须小心地剥离那些可能对 shell 有特殊意义的特殊 字符。出于安全原因,最好使用一个固定的命令字符串,或者至少避免传递 任何用户输入到其中。STDIN
指定输入来自客户端应用。
STDOUT
指定输出会去到客户端应用。
boolean
指定选中的选项是应该被关闭还是打开。可以写
TRUE
、ON
或1
来启用选项,写FALSE
、OFF
或0
禁用它。boolean
值也可以被省略, 那样会假定为TRUE
。FORMAT
选择要读取或者写入的数据格式:
text
、csv
(逗号分隔值)或者binary
。 默认是text
。FREEZE
请求复制已经完成了行冻结的数据,就好像在运行
VACUUM FREEZE
命令之后复制。这是为了初始 数据载入的性能而设计的。只有被载入表已经在当前子事务中被创建 或截断、该事务中没有游标打开并且该事务没有持有更旧的快照时, 行才会被冻结。目前无法在分区表上执行COPY FREEZE
。注意一旦成功地载入,所有其他会话将能立即看到该数据。这违背了 普通的 MVCC 可见性规则,指定该选项的用户应该注意这可能会导致 的潜在问题。DELIMITER
指定分隔文件每行中各列的字符。文本格式中默认是一个制表符, 而
CSV
格式中默认是一个逗号。这必须是一个单一 的单字节字符。使用binary
格式时不允许这个选项。NULL
指定表示一个空值的字符串。文本格式中默认是
\N
(反斜线-N),CSV
格式中默认 是一个未加引用的空串。在你不想区分空值和空串的情况下,即使在文本 格式中你也可能更喜欢空串。使用binary
格式时不允许这 个选项。注意在使用COPY FROM
时,任何匹配 这个串的数据项将被存储为空值,因此你应该确定你使用的是和COPY TO
时相同的串。HEADER
指定文件包含标题行,其中有每一列的名称。在输出时,第一行包含 来自表的列名。在输入时,第一行会被忽略。只有使用
CSV
格式时才允许这个选项。QUOTE
指定一个数据值被引用时使用的引用字符。默认是双引号。 这必须是一个单一的单字节字符。只有使用
CSV
格式时才允许这个选项。ESCAPE
指定应该出现在一个匹配
QUOTE
值的数据字符之前 的字符。默认和QUOTE
值一样(这样如果引用字符 出现在数据中,它会被双写)。这必须是一个单一的单字节字符。 只有使用CSV
格式时才允许这个选项。FORCE_QUOTE
强制必须对每个指定列中的所有非
NULL
值使用引用。NULL
输出不会被引用。如果指定了*
, 所有列的非NULL
值都将被引用。只有在COPY TO
中使用CSV
格式时才允许 这个选项。本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!