How do I format text array and hstore data in a CSV/TSV file for importing into Postgres-CodePudding

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?

CodePudding user response：

You have to double quote your string values in the text array : {"hello", "world"}. If this field is already double quoted in your csv/tsv file like "{hello, world}", then you have to escape the double quotes inside, for instance : "{\"hello\", \"world\"}" if your escape character is \ when importing the csv/tsv file in Postgres. When using COPY, you can specify the escape character you want, when using alternative solutions, I don't know.

CodePudding user response：

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.