使用gsql元命令导入数据

gsql工具提供了元命令\copy进行数据导入。

\copy命令

\copy命令格式以及说明参见表 1 \copy元命令说明。

表 1 \copy元命令说明

语法 说明
\copy { table [ ( column_list ) ] ( query ) } { from

参数说明

  • table

表的名称（可以有模式修饰）。

取值范围：已存在的表名。 - column_list

可选的待拷贝字段列表。

取值范围：任意字段。如果没有声明字段列表，将使用所有字段。 - query

其结果将被拷贝。

取值范围：一个必须用圆括弧包围的SELECT或VALUES命令。 - filename

文件的绝对路径。执行copy命令的用户必须有此路径的写权限。 - stdin

声明输入是来自标准输入。 - stdout

声明输出打印到标准输出。 - pstdin

声明输入是来自gsql的标准输入。 - pstout - 声明输出打印到gsql的标准输出。 - binary

使用二进制格式存储和读取，而不是以文本的方式。在二进制模式下，不能声明DELIMITER、NULL、CSV选项。指定binary类型后，不能再通过option或copy_option指定CSV、FIXED、TEXT等类型。 - delimiter [ as ] ‘character’

指定数据文件行数据的字段分隔符。

取值范围：支持多字符分隔符，但分隔符长度不能超过10个字节。

默认值：

  • TEXT格式的默认分隔符是水平制表符（tab）。
  • CSV格式的默认分隔符为逗号字符“,”。

  • FIXED格式没有分隔符。

    使用gsql元命令导入数据 - 图1 说明：

    • 分隔符中不能包含\r或\n。
    • 分隔符不能和null参数相同。
    • CSV格式数据的分隔符不能和quote参数相同。
    • TEXT格式数据的分隔符不能包含如下字符中任意字符： .abcdefghijklmnopqrstuvwxyz0123456789。
    • 数据文件中单行数据长度需<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。
    • 分隔符推荐使用多字符和不可见字符。多字符例如’$^&’，设置方法为delimiter ‘$^&’；不可见字符例如0x07，0x08，0x1b等，设置方法为 delimiter E’\x1b’。
    • null [ as ] ‘string’

用来指定数据文件中空值的表示。

取值范围：支持指定多字符，最大为100个字符。

默认值：

  • CSV格式下默认值是一个没有引号的空字符串。

  • 在TEXT格式下默认值是\N（包括’\‘和’N’两个字符）。

    使用gsql元命令导入数据 - 图2 说明：

    • null值不能包含\r或\n。
    • null值不能和分隔符、quote参数相同。
    • 在CSV格式下，COPY TO导出模式下，为避免数据混淆，当null值与字段内的取值相同时，会将字段数据用quote字符包裹，以作区分。
    • header

指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息。header只能用于CSV，FIXED格式的文件中。

在导入数据时，如果header选项为on，则数据文本第一行会被识别为标题行，会忽略此行。如果header为off，而数据文件中第一行会被识别为数据。

在导出数据时，如果header选项为on，则需要指定fileheader。fileheader是指定导出数据包含标题行的定义文件。如果header为off，则导出数据文件不包含标题行。

取值范围：true/on，false/off。

默认值：false - quote [ as ] ‘character’

CSV格式文件下的引号字符。

默认值：””。

使用gsql元命令导入数据 - 图3 说明：

  • quote参数不能和分隔符、null参数相同。
  • quote参数只能是单字节的字符。
  • 推荐不可见字符作为quote，例如0x07、0x08、0x1b等。
  • escape [ as ] ‘character’

CSV格式下，用来指定逃逸字符，逃逸字符后的字符会被当作字段内容处理。逃逸字符只能指定为单字节字符。

默认值：双引号。当与quote值相同时，会被替换为’\0’。 - force quote column_list | *

在CSV COPY TO模式下，强制在每个声明的字段周围对所有非NULL值都使用引号包围。NULL输出不会被引号包围。

取值范围：已存在的字段。 - force not null column_list

在CSV COPY FROM模式下，指定的字段输入不能为空。

取值范围：已存在的字段。 - force null column_list

在CSV COPY FROM模式下，指定的字段输入表示空值的字符串设为空。

取值范围：已存在的字段。 - without escaping

在TEXT格式中，不对’\‘和后面的字符进行转义。

取值范围：仅支持TEXT格式。 - compatible _illegal_chars

导入非法字符容错参数。此语法仅对COPY FROM导入有效。

取值范围：true/on，false/off。

  • 参数为true/on，则导入时遇到非法字符进行容错处理，非法字符转换后入库，不报错，不中断导入。
  • 参数为false/off，导入时遇到非法字符进行报错，中断导入。

缺省值：false/off

使用gsql元命令导入数据 - 图4 说明：

导入非法字符容错规则如下：

（1）对于’\0’，容错后转换为空格；

（2）对于其他非法字符，容错后转换为问号；

（3）若compatible_illegal_chars为true/on标识导入时对于非法字符进行容错处理，则若NULL、DELIMITER、QUOTE、ESCAPE设置为空格或问号则会通过如”illegal chars conversion may confuse COPY escape 0x20”等报错信息提示用户修改可能引起混淆的参数以避免导入错误。

任务示例

  1. 创建目标表a。

1. CREATE TABLE a(a int); 2. 导入数据。

从stdin拷贝数据到目标表a。

1. \copy a from stdin;

出现>>符号提示时，输入数据，输入.时结束。

1. Enter data to be copied followed by a newline. 2. End with a backslash and a period on a line by itself. 3. >> 1 4. >> 2 5. >> \.

查询导入目标表a的数据。

1. openGauss=# SELECT * FROM a; 2. a 3. --- 4. 1 5. 2 6. (2 rows)

从本地文件拷贝数据到目标表a。假设存在本地文件/home/omm/2.csv。

  • 分隔符为“，”。
  • 在导入过程中，若数据源文件比外表定义的列数多，则忽略行尾多出来的列。

1. \copy a FROM '/home/omm/2.csv' WITH (delimiter',',IGNORE_EXTRA_DATA 'on');