PostgreSQL使用copy导入有双引号的数据

问题概述

使用\copy导入有双引号的数据时报错“字段超长”

[postgres@mydb1a ~]$ cat test1.csv
a,a,1
b",b,2
c,c,3
a,a,4
b",b,5
c,c,6
[postgres@mydb1a ~]$ psql
psql (12.3)
Type "help" for help.

postgres=# \copy  test_copy from test1.csv with (format 'csv');
ERROR:  value too long for type character varying(10)
CONTEXT:  COPY test_copy, line 5, column name: "b,b,2
c,c,3
a,a,4
b"
postgres=# \d+ test_copy
                                        Table "public.test_copy"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 name   | character varying(10) |           |          |         | extended |              |
 name1  | character varying(15) |           |          |         | extended |              |
 id     | integer               |           |          |         | plain    |              |
Access method: heap

根据报错信息可以发现，copy导入的时候把把双引号里的数据作为name(varchar(10))列插入了，所以报错超出字段长度

问题原因

查看中文手册COPY (postgres.cn)，使用 CSV格式时，双引号作为数据值引用的默认符号

QUOTE

    指定一个数据值被引用时使用的引用字符。默认是双引号。 这必须是一个单一的单字节字符。只有使用 CSV格式时才允许这个选项。 

解决方案

方案一 不使用CSV格式

text格式中默认是一个制表符， csv格式中默认是一个逗号，所以要指定一下分隔符

postgres=# \copy  test_copy from test1.csv with (format 'text' ,delimiter ',');
COPY 6
postgres=# select * from test_copy ;
 name | name1 | id
------+-------+----
 a    | a     |  1
 b"   | b     |  2
 c    | c     |  3
 a    | a     |  4
 b"   | b     |  5
 c    | c     |  6
(6 rows)

方案二 为quote设置特殊符号

postgres=# \copy  test_copy from test1.csv with (format 'csv' ,quote E'\x5');
COPY 6
postgres=# select * from test_copy ;
 name | name1 | id
------+-------+----
 a    | a     |  1
 b"   | b     |  2
 c    | c     |  3
 a    | a     |  4
 b"   | b     |  5
 c    | c     |  6
(6 rows)

总结

1. copy导入导出时，数据中特殊符号较多时，可以使用十六进制ASCII码作为列分隔符或数据值引用符
2. 关于文件中列的分隔符，使用text格式默认是一个制表符， 使用csv格式默认是一个逗号，不指定格式时默认是text格式