具有两个NULL字符串的PostgreSQL COPY CSV [英] PostgreSQL COPY CSV with two NULL strings
问题描述
我有一个来自Web查询的csv文件源,其中包含一个字符串的两个变体,在复制到PostgreSQL表时,我希望将其分类为NULL。
I have a source of csv files from a web query which contains two variations of a string that I would like to class as NULL when copying to a PostgreSQL table.
例如
COPY my_table FROM STDIN WITH CSV DELIMITER AS ',' NULL AS ('N/A', 'Not applicable');
我知道此查询将引发错误,因此我正在寻找一种方法来指定两个单独的NULL
I know this query will throw an error so I'm looking for a way to specify two separate NULL strings in a COPY CSV query?
推荐答案
我认为在这种情况下最好的选择,因为 COPY
不支持多个 NULL
字符串,而是将 NULL
字符串参数设置为其中之一,然后,在全部加载后,执行 UPDATE
,它将在您希望的任何列中设置值,而另一个 NULL
想要实际的 NULL
值的字符串(确切的查询取决于哪些列可以具有这些值)。
I think your best bet in this case, since COPY
does not support multiple NULL
strings, is to set the NULL
string argument to one of them, and then, once it's all loaded, do an UPDATE
that will set values in any column you wish having the other NULL
string you want to the actual NULL
value (the exact query would depend on which columns could have those values).
如果您有一堆列,则可以在 SET
子句中使用 CASE
语句返回 NULL
(如果它与您的特殊字符串匹配),否则与值匹配。 NULLIF
也可以使用(这样会更紧凑)。例如 NULLIF(col1,不适用)
If you have a bunch of columns, you could use CASE
statements in your SET
clause to return NULL
if it matches your special string, or the value otherwise. NULLIF
could also be used (that would be more compact). e.g. NULLIF(col1, 'Not applicable')
这篇关于具有两个NULL字符串的PostgreSQL COPY CSV的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!