Support CSV delimiter other than comma

[ghost]

I would like to populate a table that has a JSONB field and some non-JSONB fields using CSV format. I run into problems when the JSONB value in the CSV file has more than one key-value pair because I need to separate pairs with a comma.

Example line from CSV file:
foo,"{""a"": 1, ""b"": "x"}"

Is there a way to provide a parameter like in PostgreSQL's COPY command ("copy ... with delimiter '|'...")?

[SebAlbert]

It may be only in your posting of an example here, but I think the quotation marks surrounding your x inside the JSONB are not properly escaped.

On basically that very same note, I think first and foremost any CSV importing feature should implement RFC-compliant escaping (I don't know if PostgREST does it already), so your commas inside the quotation marks surrounding the entire JSONB would be properly ignored.

Nevertheless, I don't object against the choice of arbitrary delimiters for both separation of columns and quoting; I would in fact also appreciate it for CSV output from PostgREST to be speicifiable, maybe through some Prefer header, unless possible in the Accept header.

[begriffs]

@SebAlbert could you provide me some small examples to test RFC compliance? I'll turn them into tests for the test suite.

@rrantzau the ability to do the true COPY command depends on nikita-volkov/hasql#1. Also thinking about your specific example, maybe the x has too few quotes, what about trying this: foo,"{""a"": 1, ""b"": ""x""}"

[ghost]

Thank you for the replies, it was my mistake!

The following example does work:

psql -U postgres -c "drop table if exists foo.testj cascade"
psql -U postgres -c "create table foo.testj (a int, b text, j jsonb)"
echo 'a,b,j
1,b1,"{""x"": 10, ""y"": ""y1""}"' > ./testj.csv
cat testj.csv
curl "http://localhost:3000/testj" -H "Content-type: text/csv; charset=utf-8" --data-binary @testj.csv
curl "http://localhost:3000/testj?a,b,j" -H "Content-type: text/csv; charset=utf-8"
[{"a":1,"b":"b1","j":{"x": 10, "y": "y1"}}]
psql -U postgres -c "select * from foo.testj"
 a | b  |          j           
---+----+----------------------
 1 | b1 | {"x": 10, "y": "y1"}
(1 row)

Great!

[SebAlbert]

Just some comments, they may or may not be of use: You may want to use the Accept header instead of the Content-type header in order to actually GET csv returned from the server. And you may want to use ?select=a,b,j instead of ?a,b,j