The columns of the CSV are of the format text
(can include empty lines), date
and a JSON array of strings (something like ['a', 'b', 'c']
. I've been trying to copy that CSV to a PostgreSQL table (using psycopg2
's copy_expert
, which simply executes the given SQL COPY
command, if that matters)
Table is created with
CREATE TABLE posts(
id SERIAL PRIMARY KEY,
text TEXT NOT NULL,
created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
rubrics jsonb[] NOT NULL
);
and the copy command is
COPY posts(text, created_date, rubrics)
FROM STDIN
WITH CSV HEADER
where STDIN
is the CSV file.
The error I get is
malformed array literal: "['a', 'b', 'c']"
DETAIL: "[" must introduce explicitly-specified array dimensions.
CONTEXT: COPY posts, line 15, column rubrics: "['a', 'b', 'c']"
I've tried all 4 JSON-related data types (json
and jsonb
with and without []
or [3]
), including square brackets produces the error above, while omitting them (rubrics jsonb NOT NULL
when creating) gives a new one:
invalid input syntax for type json
DETAIL: Token "'" is invalid.
CONTEXT: JSON data, line 1: ['...
COPY posts, line 15, column rubrics: "['a', 'b', 'c']"
Do I have any recourse besides manually fixing the .csv to use {}
instead of []
before copying it? It feels like I do, yet I haven't really been able to found anything besides a couple somewhat, but not fully relevant questions.
Update regarding the comments
I've changed the table creation to
CREATE TABLE posts(
id SERIAL PRIMARY KEY,
text TEXT NOT NULL,
created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
rubrics jsonb NOT NULL
);
and left a single entry in the CSV to test, so it looks like this
text,created_date,rubrics
"Lorem
Ipsum
Test",2019-07-25 12:42:13,'["f", "o", "o"]'
Things I've tried and errors I got
Using the CSV as is:
extra data after last expected column
CONTEXT: COPY posts, line 6: ""Lorem
Ipsum
Test",2019-07-25 12:42:13,'["f", "o", "o"]'"
An additional pair of ""
around the array ("'["f", "o", "o"]'"
)
invalid input syntax for type json
DETAIL: Token "'" is invalid.
CONTEXT: JSON data, line 1: '...
COPY posts, line 6, column rubrics: "'[f, o, o]'"
No quotes at all (just the ["f", "o", "o"]
)
extra data after last expected column
CONTEXT: COPY posts, line 6: ""Lorem
Ipsum
Test",2019-07-25 12:42:13,["f", "o", "o"]"
Double quotes instead of single quotes ("["f", "o", "o"]"
)
invalid input syntax for type json
DETAIL: Token "f" is invalid.
CONTEXT: JSON data, line 1: [f...
COPY posts, line 6, column rubrics: "[f, o, o]"
Double quotes outside, single quotes inside ("['f', 'o', 'o']"
)
invalid input syntax for type json
DETAIL: Token "'" is invalid.
CONTEXT: JSON data, line 1: ['...
COPY posts, line 6, column rubrics: "['f', 'o', 'o']"
Could it be an issue with the Python library I'm using after all?
['a', 'b', 'c']
is not valid JSON, because strings must be enclosed in double quotes, not single quotes.rubrics jsonb[]
and notrubrics jsonb
?"['a','b','c']"
if I view the CSV through a text editor (so one entry is"Lorem Ipsum",2019-07-25 12:42:13,"['a', 'b', 'c']"
), I just had the file open in Excel['a', 'b', 'c']
and it's not a valid JSON or a valid array of JSON. Any JSON parser will reject that.