I’m trying to import text arrays and hstore values into Postgres from a CSV (actually TSV) spreadsheet, but I keep getting multiples of these two errors:
ERROR: Syntax error near '}' at position 667
ERROR: malformed array literal: "" Detail: Array value must start with "{" or dimension information.
My text arrays look like this: {hello, world}
and my hstore values look like this: {"hello" => "world", "goodbye" => "world"}
I'm not using the COPY command, I'm importing the file either through my IDE (Goland) database navigator or through Postico 2. What is the proper syntax for array and hstore data in a CSV or TSV spreadsheet for importing into Postgres?
Ok, so for anyone who needs to import array and hstore data into Postgres similar to how I am (spreadsheet exported as a TSV then imported via IDE), this is what worked for me.
For my text arrays, I needed to enclose them in braces and use double quotes only for the array entries that had spaces in them. No escape characters or doubling of double quotes was necessary.
Example: {foo,"bar baz",qux}
For my hstore values, no braces were needed, but double quotes for keys or values with spaces was necessary (AFAIK):
Example: foo=>bar,baz=>"qux quuz"
I don't know if it makes any difference, but I eliminated all spaces other than those inside quoted values (e.g. around commas and =>). Also, for array columns that can have null values, any cells in the those columns that would be null, you have to have an empty array in the cell (i.e. {}) to import without error.
I've have not yet attempted to retrieve these values with my app, but as they are now in my spreadsheet, they import without error.