2

I'm trying to import into postgres a csv file containing the data for a table. One of the column of the table has jsonb type.

One line of my csv file contains something like

1,{"a":"b"}

Suppose the table has a schema

id              | smallint          | 
data            | jsonb             | 

If I try just to insert the data, everything works fine

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

Trying to import directly from the file with

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

gives me the following error:

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}"

I tried to quote the fields with ', with ", with \" and \', but nothing works..

Which is the correct syntax do do it?

  • Can you check out my answer. =) – Evan Carroll Mar 9 '17 at 6:26
2

Found the solution, postgres uses " as an escaping character, so the correct format should be

{"""a""": """b"""}
  • You can specify the escape character for the copy command with the escape option – a_horse_with_no_name Nov 25 '16 at 16:36
  • I don't think this is true. Yes, " is an escaping character, so you need "{""a"": ""b""}" not triple quotes. – Evan Carroll Mar 9 '17 at 6:37
2

The PostgreSQL COPY command is seldom ideal, but it often works. For reference there are better methods to figure this out than guessing.

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

This is your exact sample data. Now we can test different settings..

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

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

You'll see that the above generates the exact data your questioning...

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

There is no problem. At least not with PostgreSQL 9.5.

CSV Mode

So where is your problem, it's with CSV-mode. Observe,

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

You can see these two are different now. Let's try to load the non-CSV file in CSV mode which assumes the format that CSV mode generated above.

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}"

Now we error. The reason for that comes from RFC 4180

Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

  1. So JSON RFC 4627 specifies an object's names in name/value pairs must be strings which require double quotes.
  2. And CSV RFC 4180 specifies that if any double quotes are inside the field, then the whole field must be quoted.

At this point you have two options..

  1. Don't use CSV mode.
  2. Or, Escape the inner quotes.

So these would be valid inputs under the same options in CSV mode.

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

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

As noted in other answers the CSV and the JSON specs (and probably the postgresql specs) are somewhat incompatible. To get them to stop fighting, at least in their simple forms, you have to escape things until they're an unreadable mess. Not using CSV mode is even worse as the COPY will die on anything that JSON has an issue with: embedded new lines, back-slashes or quote marks.

I had your exact same problem only with a much more complex input: a bunch of ENUM types, integers and complex JSON fields. To wit:

create table messages( a blab, b integer, c text, d json, e json);

where the typical JSONs would be {"default":"little","sms":"bigger"} and ["name","number"]. Try importing a pile of those with a COPY command: if the commas inside the JSON don't get you the quote marks will! I spend hours on this until i found this fine blog post which pointed out the causes of the problem and the options you need to get out of it.

Basically you need to change the delimiter and quote fields to something that you can guarantee won't be in your JSON data. In my case I can guarantee a lot so I can just go

COPY messages( a, b, c, d, e) from stdin  csv quote '^' delimiter '|';
malfunction|5|La la la|{"default":"little","sms":"bigger"}|["name","number"]

Nice and readable, easy to get to with some minor character substitution in your favourite text mangler, and no escaping of no nothing! If you can't guarantee that the characters used above can't be in your JSON then you can use the rather wacky e'\x01' and e'\x02' as the JSON spec deems them totally illegal. Not quite as readable and so forth but punctiliously correct.

Note that embedded new-lines, as some JSON generators tend to emit for readability purposes, are still 'no-no's so you have to filter them out of your JSON.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.