Support Accept-Charset for international characters

[Iced-Sun]

Currently, the text/csv response is encoded with UTF-8 w/o BOM. But Microsoft Excel requires the encoding of UTF-8 with BOM to display the non-ascii characters correctly.

Is is possible to provide a csv output with utf-8-bom encoding?

[Iced-Sun]

Or, the response could respect the charset setting in the client header, i.e., Accept: text/csv; charset=gbk. Postgrest just ignores the charset setting and always returns Content type=text/csv; charset=utf-8.

[steve-chavez]

Regarding the BOM in excel, I remember having the same problem for csv files in spanish. And it was impossible to specify bom and sep at the same time, so I ended up generating an xlsx in java. Perhaps that could be done directly in pg with its xml functions(maybe too complicated).

So I think we should definitely not clutter csv generation with some excel specific workarounds that in the end won't work for all of the cases.

[steve-chavez]

Regarding specifying the charset, I think we could add that.

According to RFC 2616, you should add the header Accept-Charset: gbk.

PostgreSQL also supports changing the charset. I think it should just be a matter of setting the client_encoding GUC in each transaction:

set local client_encoding to 'gbk';

[steve-chavez]

@sunbing81 What do you think?

Also specifying the delimiter for csv was brought up in #1102.

[Iced-Sun]

I'll definitely go with the 'Accept-Charset' header solution. Cleaner, no hidden magic, and possible to deal with delimiter.

Regards.

[bwbroersma]

Slightly off topic: It would also be great to have the Accept[-Charset] headers work as query parameter, maybe even have a force download option (adding a Content-Disposition: attachment; filename="name.csv" see MDN).

The background reason I would like this: This would be very useful since then you can just pass API links to less technical users, who can handle CSV/Excel files. Then it's also easy to make a tool based on the OpenAPI schema where a non-tech user can select fields and joins to create a CSV link. Although this becomes more useful when #1001 is solved of course.

[steve-chavez]

@bwbroersma That can be done with http://postgrest.org/en/v5.2/api.html#setting-response-headers. Some users have already used Content-Disposition to download images.

[bwbroersma]

@steve-chavez thanks, yes I knew that, I was thinking about the CRUD endpoints, but since I run proxy/nginx in front, I can both add the Accept* header(s) to PostgREST & the Content-Disposition to the client based on extra query parameters to the CRUD endpoints (that need to be stripped from the request going upstream to PostgREST of course).

However, separators & UTF-8 BOM are hard to fix in the proxy/nginx. In my experience UTF-8 BOM + ;-separator (for Dutch/European Regional settings) is crucial to get CSV working across multiple programs: Microsoft Excel / Apple Numbers / LibreOffice Calc / Gnumeric. I'm an open source & open data enthusiastic, and I really want to expose our endpoints to more non CSV-import-experts, without having to run BOM proxy layers or Excel converters. (I know it's a pain in csvkit csvjson since the BOM will end up being encoded as your first JSON key, although why would someone use csvjson when the default output is JSON 🙂)

The CSV output separator / BOM and resource embedding can all be fixed here I guess:

postgrest/src/PostgREST/QueryBuilder/Private.hs

Lines 106 to 117 in d575852

	asCsvF = asCsvHeaderF <> " || '\n' || " <> asCsvBodyF
	where
	asCsvHeaderF =
	"(SELECT coalesce(string_agg(a.k, ','), '')" <>
	" FROM (" <>
	" SELECT json_object_keys(r)::TEXT as k" <>
	" FROM ( " <>
	" SELECT row_to_json(hh) as r from " <> sourceCTEName <> " as hh limit 1" <>
	" ) s" <>
	" ) a" <>
	")"
	asCsvBodyF = "coalesce(string_agg(substring(_postgrest_t::text, 2, length(_postgrest_t::text) - 2), '\n'), '')"

Update: the amazing hack is that substring(composite_type::text, 2, length(composite_type::text) - 2)) will produce a valid "-escaped CSV row, with , separators. So the question will be what an elegant fix would be to change the , separator
PostgreSQL Docs: Composite Type Input and Output Syntax
PostgreSQL record_out code (output routine for any composite type) rowtypes.c has a , hard-coded.

Update 2: ok probably the header generation code is broken already:

CREATE TABLE nasty ("unusual"",names" INTEGER GENERATED ALWAYS AS IDENTITY, text TEXT);
INSERT INTO nasty (text) VALUES ('test');

curl 'http://localhost/nasty' -H 'Accept: text/csv'

unusual",names,text
1,test

Quoted identifiers can contain any character, except the character with code zero.

See the PostgreSQL syntax.