我正在嘗試將csv包含表數據的文件導入到 postgres 中。表的一列具有jsonb類型。

我的文件的一行csv包含類似

1,{"a":"b"}

假設表有一個模式

id              | smallint          | 
data            | jsonb             | 

如果我嘗試僅插入數據，一切正常

INSERT INTO table VALUES (1, '{"a":"b"}');

嘗試直接從文件中導入

COPY table FROM '/path/to/file.csv' DELIMITER ',' csv;

給我以下錯誤：

ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY availability, line 1, column services: "{a: b}"

我試圖用', with ", with \"and引用欄位\'，但沒有任何效果..

這樣做的正確語法是什麼？

PostgreSQLCOPY命令很少是理想的，但它經常有效。作為參考，有比猜測更好的方法來解決這個問題。

CREATE TEMP TABLE baz AS
 SELECT 1::int, '{"a":"b"}'::jsonb;

這是您的確切樣本數據。現在我們可以測試不同的設置..

# COPY baz TO STDOUT;
1   {"a": "b"}

COPY baz TO STDOUT DELIMITER ',';
1,{"a": "b"}

你會看到上面生成了你提問的確切數據……

COPY baz TO '/tmp/data.csv' DELIMITER ',';

沒有問題。至少 PostgreSQL 9.5 沒有。

CSV 模式

那麼你的問題在哪裡，它與 CSV 模式有關。觀察，

# COPY baz TO STDOUT;
1   {"a": "b"}
# COPY baz TO STDOUT CSV;
1,"{""a"": ""b""}"

你現在可以看到這兩個是不同的。讓我們嘗試以 CSV 模式載入非 CSV 文件，該模式假定 CSV 模式在上面生成的格式。

TRUNCATE baz;
COPY baz FROM '/tmp/data.csv' DELIMITER ',' CSV;
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY baz, line 1, column jsonb: "{a: b}"

現在我們出錯了。原因來自RFC 4180

每個欄位可能包含也可能不包含在雙引號中（但是某些程序，例如 Microsoft Excel，根本不使用雙引號）。 如果欄位沒有用雙引號括起來，則雙引號可能不會出現在欄位內。

1. 因此JSON RFC 4627指定名稱/值對中的對象名稱必須是需要雙引號的字元串。
2. 並且CSV RFC 4180指定如果欄位內有任何雙引號，則必須引用整個欄位。

此時你有兩個選擇..

1. 不要使用 CSV 模式。
2. 或者，轉義內部引號。

因此，這些將是 CSV 模式下相同選項下的有效輸入。

#COPY baz TO STDOUT DELIMITER ',' CSV ESCAPE E'\\';
1,"{\"a\": \"b\"}"

# COPY baz TO STDOUT DELIMITER ',' CSV;
1,"{""a"": ""b""}"

找到了解決辦法，postgres"用作轉義字元，所以正確的格式應該是

{"""a""": """b"""}

引用自：https://dba.stackexchange.com/questions/156372