0

I am trying to load a table from a txt file which has Chinese characters in it using the \copy command in PostgreSQL. I have a test table with only one columns Names Varchar(25) in it. When I run an insert statement from PSQL or PgAdmin like

insert into test values ('康狀態概');

it works and inserts the value correctly. When I put the same value in a txt file (say text.txt) and try to use the copy command and load the contents of test.txt

\copy test from 'D:/database/test.dat';

It throws an error ERROR: character with byte sequence 0xa6 0x82 in encoding "GBK" has no equivalent in encoding "UTF8" on WIndows only. I changed the locale and keyboard settings of the Windows server to Chinese BUT it does not work with or without changing the locale settings.

The same exercise works fine on Linux without any changes.

Can someone please suggest what needs to be done here?

Thanks

P_Ar
  • 377
  • 2
  • 9
  • 25
  • What encoding is (1) the file, (2) the client (psql) set to? – Richard Huxton May 17 '16 at 15:54
  • The file is set to UTF-8 encoding. What do you mean by client encoding? I am using PSQL on Windows command prompt to run the \copy command query. – P_Ar May 17 '16 at 15:58
  • You can tell psql what characters to expect http://www.postgresql.org/docs/current/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT Looking at that error message, it seems to be expecting "GBK". Tell it to try UTF-8 if that is the file encoding. – Richard Huxton May 17 '16 at 16:06
  • Added -E UTF8 to while creating the DB and it seems to have resolved the issue. Thanks much. Can you tell me whats the default encoding of the DB when we do not give a -E option while creating the database in PostgreSQL? – P_Ar May 17 '16 at 18:30
  • If it's not UTF-8 then it must try to detect it based on your system settings during installation. If you drop and recreate the "template1" db with a particular encoding, new databases should inherit that. – Richard Huxton May 17 '16 at 18:37
  • While doing initdb, I am actually specifying the encoding `initdb.exe --locale=C --encoding=UTF-8` and then i run the createdb.exe with -E option. Shouldn't the DB have UTF8 encoding as well? – P_Ar May 17 '16 at 18:47
  • 1
    Yes, although I'm not sure "C" locale makes much sense. `psql -l` shows the encoding for all dbs. – Richard Huxton May 17 '16 at 18:52
  • Thank you so much @RichardHuxton – P_Ar May 17 '16 at 20:07

0 Answers0