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.
I have 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.