2

I'm trying to do a select on a UTF8 DB, but the Results do not contain proper UTF-8 characters. This is for german characters like "ä", "ö", "ü"

screen: https://i.stack.imgur.com/AwH6z.jpg

The client_encoding is set to UTF8.

CHCP is 850.

When I do psql -c "ö" -d ...-U postgres I get ERROR: invalid byte sequence for encoding "UTF8": 0xf6 HINT: This error can also happen if the byte sequence does not match the encodi ng expected by the server, which is controlled by "client_encoding".

The Database is definetely UTF8, when I do the query in pgadmin it works properly.

How to do this right?

---Edit--- After setting the right encoding for the cmd the command is working, but the results still can't be stored in powershell variables / be exported via powershell. using \copy to perform the export and reading the file in again solves the problem. \copy (Select 'ö') To 'e:/test.csv' With CSV

user2213834
  • 33
  • 1
  • 8
  • 3
    Looks like *your terminal* doesn't handle UTF-8 correctly. – deceze Nov 27 '13 at 09:13
  • How to fix this? It's the same in Powershell, which has no problems working with UTF8 Chars, aswell as my cmd. – user2213834 Nov 27 '13 at 09:40
  • @Denis changing charset doesn't help – user2213834 Nov 27 '13 at 12:12
  • If you do the same command interactively, instead of via `-c`, does it work then? Wondering if psql is failing to do argument encoding conversion on Windows. – Craig Ringer Nov 28 '13 at 01:35
  • No, it behaves just the same. I'm calling psql from within powershell / cmd and it does just the same. – user2213834 Nov 28 '13 at 12:42
  • Since your terminal is sending *windows-1252* or *iso-8859-1* encoded data to *psql* (as shown by your *ö* being encoded as `0xf6`), it might work if the `client_encoding` is one of those encodings. But this will limit your input to characters found in those encodings. [Have you tried](http://stackoverflow.com/questions/4867272/invalid-byte-sequence-for-encoding-utf8) writing your query to a *utf-8* encoded file and letting *psql* read the query from that file instead? – bzlm Nov 30 '13 at 09:50
  • @bzlm That wouldn't make much sense because the problem is how the result is being shown, not how to query the results. So i can query tables with special characters, but they're not being shown right – user2213834 Jan 28 '14 at 14:05
  • @user2213834 according to your question, you cannot psql. – bzlm Jan 29 '14 at 17:24
  • @bzlm What do you mean? I never claimed to be an expert on this.. I tried to read from a sql file now. so i do psql -U ... -d ... -c "\i e:/sql.txt", this is a file containing "select ö" and is encoded in utf8. when i do this, i get an error like psql:E:/sql.txt:1: FEHLER: Syntaxfehler bei »ï»¿Select« ZEILE 1: Select 'ö'; – user2213834 Jan 30 '14 at 16:02
  • when i encode to "utf8 without BOM" I get a result, but with wrong encoding again for the cmd i guess. when i encode in ANSI it works, too, but with another wrong char – user2213834 Jan 30 '14 at 16:10
  • @Craig Ringer I was wrong, the query works when I do it interactively. so chcp is 850, default. i start psql and type select 'ö', and it works. but how can i retrieve this result for further use? would i need to export via psql itself or is there a possibility to get the result right, when calling psql with -c option? – user2213834 Jan 30 '14 at 16:15
  • @user2213834 If `client_encoding` is `utf-8` you must use the matching terminal code-page, `chcp 65001`. Read the link that's been automatically added to the top of your post. – Craig Ringer Jan 30 '14 at 23:46
  • @Craig Ringer Yes, I've read about that. When starting psql it says console codepage is 850 and windows codepage is 1252. setting chcp to 65001 and client_encoding to utf8 causes more problems: when I try sending "select 'a';" for example i just get "not enough ram". when i try "select 'ö';" it shows \q and terminates psql. When I set chcp to 1252 and leave client_encoding as it is it works properly, in interactive mode and with the -c option. But then i have to use a different console font. So this works now. Still my Script isn't working properly.. another encoding problem i guess :-) – user2213834 Jan 31 '14 at 09:24
  • So to make a powershell script with a select like "select 'ö';" statement work I can just add chcp 1252 to the script and it will show the result on the console correctly. the file itself is ANSI encoded. using powershell's "| out-file" piping into a text file for the result returns an error FEHLER: ungültige Byte-Sequenz für Kodierung »UTF8«: 0xf6 0x27 0x3b (Error: invalid byte sequence for encoding utf8) and the console output is broken again. using "utf8 without bom" exports the result to file, but broken char and also broken char on console. which encoding "fits" codepage 1252? – user2213834 Jan 31 '14 at 09:41
  • I'm using notepad++ atm. i found where to set right charset now.. but still out-file not working – user2213834 Jan 31 '14 at 09:46
  • the error occurs as soon as i try to store the result in a variable – user2213834 Jan 31 '14 at 09:59
  • If you're going to use utf-8, use utf-8 consistently. That means input files must be utf-8, SQL sources must be utf-8, etc. – Craig Ringer Jan 31 '14 at 13:31
  • @Craig Ringer Works now, script utf8 chcp 65001, db is utf8. but client_encoding is 1252, else it won't work. this is all very confusing – user2213834 Feb 03 '14 at 10:12
  • @user2213834 Welcome to the wonderful and insane world of text encodings. With that behaviour, I'm kind of wondering if your input isn't misencoded, actually. – Craig Ringer Feb 03 '14 at 12:07

0 Answers0