PostgreSQL如何使用COPY命令和包含双引号的CSV数据？

Question

示例 CSV 行：

"2012","Test User","ABC","First","71.0","","","0","0","3","3","0","0","","0","","","","","0.1","","4.0","0.1","4.2","80.8","847"

All values after "First" are numeric columns. Lots of NULL values just quoted as such, right.

Attempt at COPY:

copy mytable from 'myfile.csv' with csv header quote '"';

不行： 错误：类型为数字的无效输入语法：“”

是的，这是一个空值。尝试第二个COPY:

copy mytable from 'myfile.csv' with csv header quote '"' null '""';

NOPE: ERROR: CSV引用字符不能出现在NULL规范中

那该怎么办呢？在运行COPY之前从文件中删除所有双引号？虽然可以这样做，但我认为必须有一个正确的解决方案，因为这肯定是一个非常常见的问题。

- Wells

---

在我看来，这似乎是PostgreSQL中的一个错误（在9.4版本中仍然存在），因为null ''应该将空字符串视为null。 - Tobia

Answer 1

虽然一些数据库产品将空字符串视为NULL值，但标准规定它们是不同的，PostgreSQL将它们视为不同的值。

最好使用明确的表示方法生成CSV文件。 虽然您可以使用sed等工具将文件过滤到正确的格式，但另一个选项是将数据COPY到可以接受空字符串的text列的表中，然后填充目标表。 NULLIF 函数可能有所帮助: http://www.postgresql.org/docs/9.1/interactive/functions-conditional.html#FUNCTIONS-NULLIF -- 如果两个参数匹配，则返回NULL，否则返回第一个值。 所以，类似NULLIF(txtcol, '')::numeric 的内容可能适合您的需求。

- kgrittn

---

太棒了 - 我可以轻松创建没有双引号的 CSV 文件，但是我不确定是否有比空的双引号字符串更少歧义的内容。这只是我的个人观点。 - Wells

根据 SQL 规范，你可以有一个长度为零的字符串，这与 NULL 不同。我知道有些数据库将它们视为相同的东西，并且如果你只使用这些产品工作，可能会觉得这很自然，但从逻辑上讲，这是知道值是长度为零的字符串和不知道值之间的区别。 - kgrittn

同意，但是数据库中的列是数字类型，所以我不确定为什么COPY需要将CSV值视为字符字符串。 - Wells

由于 0（零） 是一个有效的值，因此 ""（空字符串） 也是如此。NULL 表示完全没有任何值。 - Magno C

Answer 2

作为一种替代方案，可以使用。

sed 's/""//g' myfile.csv > myfile-formatted.csv
psql 
# copy mytable from 'myfile-formatted.csv' with csv header;

同样也适用。

- Adam Greenhall

---

很好的想法，但我认为 's/,""/,\\N/g' 会更合适。（\N表示NULL） - wildplasser

with csv 意味着 null ''（空字符串 = null） - wrschneider

不！你不想这样做。想象一下，你有一行数据像这样：“名称”，“示例公司”，“其他内容”。你的sed命令将会把三列变成四列。 - raindog308

Answer 3

我认为你需要做的只有以下几点：

COPY mytable from '/dir/myfile.csv' DELIMITER ',' NULL '' WITH CSV HEADER QUOTE ;

- Chris

---

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Joseph Lust

Answer 4

COPY mytable from '/dir/myfile.csv' DELIMITER ',' NULL '' 
WITH CSV HEADER FORCE QUOTE *;

- Abdel

---

仅通过COPY TO命令可用强制引用COPY。 - Nikolay Shebanov

Answer 5

这在Python 3.8.X中对我有效。

import psycopg2
import csv
from io import StringIO
db_conn = psycopg2.connect(host=t_host, port=t_port,
                           dbname=t_dbname, user=t_user, password=t_pw)
cur = db_conn.cursor()

csv.register_dialect('myDialect',
                     delimiter=',',
                     skipinitialspace=True,
                     quoting=csv.QUOTE_MINIMAL)

with open('files/emp.csv') as f:
    next(f) 
    reader = csv.reader(f, dialect='myDialect') 
    buffer = StringIO()
    writer = csv.writer(buffer, dialect='myDialect') 
    writer.writerows(reader) 
    buffer.seek(0)
    cur.copy_from(buffer, 'personnes', sep=',', columns=('nom', 'prenom', 'telephone', 'email'))
    db_conn.commit()

- Juanse