The form worked find but when syncing I received the message Internal Error JSON quoting -> postgres array quoting -> CSV quotingīut as seen in your example, it seems that you don't need a Postgres array of JSON objects as a column, but rather a single JSON value that contains a JSON array.I recently came across an issue with one of the forms I am working on. If you wanted a Postgres array of JSON objects in rubrics, that would be more complicated, as expressing the Postgres array as text requires another layer of encapsulation with its own syntax rules, so the production of valid data to import with COPY would have to do Your example slightly edited to conform to both CSV and JSON does work: CREATE TABLE posts( If double-quotes are used to enclose fields, then a double-quoteĪppearing inside a field must be escaped by preceding it with.If fields are not enclosed with double quotes, thenĭouble quotes may not appear inside the fields Some programs, such as Microsoft Excel, do not use double quotesĪt all). Each field may or may not be enclosed in double quotes (however.
JSON uses double quotes around string literals, so all the attempts in the question to use single quotes around strings are invalid for the JSON parserĬSV requires that double quotes are doubled, see rules #5 and #7 of rfc 4180, so all attempts to use double quotes in JSON without doubling them for CSV or without enclosing the entire field in double quotes are invalid for the CSV parser. As mentioned in the RFC, programs that read or write CSV do not always conform strictly to all these rules, but concerning the quoting rules, Postgres is conformant. JSON is a layer of encapsulation with a standardized syntax that the JSON field in your input must respect.ĬSV is another layer of encapsulation that applies on top, with a syntax that is more of less standardized in rfc 4180. No quotes at all (just the ) extra data after last expected columnĭouble quotes instead of single quotes ( "") invalid input syntax for type jsonĬOPY posts, line 6, column rubrics: ""ĭouble quotes outside, single quotes inside ( "") invalid input syntax for type jsonĬould it be an issue with the Python library I'm using after all? Using the CSV as is: extra data after last expected columnĪn additional pair of "" around the array ( "''") invalid input syntax for type jsonĬOPY posts, line 6, column rubrics: "''" I've changed the table creation to CREATE TABLE posts(Īnd left a single entry in the CSV to test, so it looks like this text,created_date,rubrics 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.
I've tried all 4 JSON-related data types ( json and jsonb with and without or ), 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ĬOPY posts, line 15, column rubrics: ""ĭo I have any recourse besides manually fixing the.
The error I get is malformed array literal: ""ĭETAIL: "[" must introduce explicitly-specified array dimensions.ĬONTEXT: COPY posts, line 15, column rubrics: "" Table is created with CREATE TABLE posts(Ĭreated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,Īnd the copy command is COPY posts(text, created_date, rubrics) 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) The columns of the CSV are of the format text (can include empty lines), date and a JSON array of strings (something like.
0 kommentar(er)
