I'm currently coding an API that connects to an ElephantSQL instance using pg-promise. Everything works fine except that special characters from text columns (like accented letters) are not retrieved properly (i.e. the word "Collège" shows as "Collège" in the JSON object). I suppose this has something to do with the encoding, but special characters are displayed properly in ElephantSQL's dashboard, so I don't think the database encoding is wrong - though I can't find any information about the database encoding in the first place. So my best guess is, I need to add something to the client to specify the encoding I wish to get the results in. I have tried tweaking the client_encoding as described in this page, using utf8 and latin1, but to no avail. Is there something I'm missing or did I just try the wrong encodings?
UPDATE
When running the following query:
SELECT *
FROM information_schema.character_sets;
I get the following JSON object:
{
character_set_catalog: null,
character_set_schema: null,
character_set_name: "UTF8",
character_repertoire: "UCS",
form_of_use: "UTF8",
default_collate_catalog: "ggnonllb",
default_collate_schema: null,
default_collate_name: null
}
So I know for a fact that my online DB is encoded in UTF8 (as one would expect). But my issue isn't solved even though every query I run is wrapped inside a SET CLIENT_ENCODING TO 'UTF8';. So I feel awfully confused. It's as though this had nothing to do with characters encoding. But why would the pg-promise client get results with special characters scrambled if it's not a matter of characters encoding? Moreover, this only happens when retrieving data, not when posting them, since the special characters show as they should in the ElephantSQL's dashboard.
Actually, I figured it out. Seems like it was a problem with the character encoding of the response. When I query my API from my browser, the special characters still don't show like they should, but when called from my application, all is fine.