Using psql -f to load a UTF8 file

From: Alan Millington <admillington(at)yahoo(dot)co(dot)uk>
To: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Using psql -f to load a UTF8 file
Date: 2012-09-20 14:56:16
Message-ID: 1348152976.84043.YahooMailNeo@web28905.mail.ir2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My database is UTF8. I use psql -f to load files containing DDL and DML commands. I discovered a long time ago that psql does not like UTF8 files: it complains about the byte order mark on the first line. Up to now I have worked round that by making sure that the files were saved as what Microsoft calls "ANSI". However, that option is not available if I want to insert data which includes non-ASCII characters.
 
I have found a suggestion that psql can be told to expect UTF8 files by creating a file called psqlrc.conf containing the line \encoding unicode. I have tried putting this file (i) in the Postgres data directory, along with postgresql.conf, and (ii) in %APPDATA%\postgresql, but I still get an error:
 
psql:120919_insert_into_letter.sql:1: ERROR:  syntax error at or near "insert
"
LINE 1: insert into LETTER_VAR (var_name, type) values ('REPORT_COP...
 
I have found a workaround, which is to start the file with a line containing just a semicolon. Then the empty "statement" fails, but the other statements succeed:
 
psql:120919_insert_into_letter_copy2.sql:1: ERROR:  syntax error at or near "
"
LINE 1: ;
        ^
INSERT 0 1
INSERT 0 1

 
However, I feel sure that there must be a better way.
Ihave noted BUG report #6271: psql -f reporting unexpected syntax errors on first command. This involves the same problem. Álvaro Herrera advised that "You need to remove [the byte order mark] before passing the file to psql". But how am I supposed to remove the byte order mark from a UTF8 file? I thought that the whole point of the byte order mark was to tell programs what the file encoding is. Other programs, such as Python, rely on this.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Капралов Александр 2012-09-20 15:01:49 WITH RECURSIVE from 2 or more tables.
Previous Message Tom Lane 2012-09-20 14:47:48 Re: pg_upgrade: out of memory