Thread: BUG #6271: psql -f reporting unexpected syntax errors on first command

The following bug has been logged online:

Bug reference:      6271
Logged by:          Shannon
Email address:      shannon@arc.net.au
PostgreSQL version: 8.4.9
Operating system:   Gentoo Linux 64bit
Description:        psql -f reporting unexpected syntax errors on first
command
Details:

Given the following 2 line SQL script in a file:

--- test.sql ---
SHOW client_encoding;
SHOW client_encoding;
----------------

$ psql -ftest.sql -U postgres template1

psql:test.sql:1: ERROR:  syntax error at or near "SHOW"
LINE 1: SHOW client_encoding;
        ^
 client_encoding
-----------------
 UTF8
(1 row)


See what happened there? The first line is treated as a syntax error and the
second line works which is insane because the two lines are identical!

I've tried:
* Adding empty lines and/or comments to the top of the file
* Setting PGCLIENTENCODING in the shell to a variety of values including
UTF8, SQL_ASCII and LATIN1
* Other commands

What works:
* Every line except the one with the first command
* Piping a string into psql stdin
* Using psql interactively

Other factors:
Could this be related to my locale?

$ locale
LANG=en_AU.utf8
LC_CTYPE="en_AU.utf8"
LC_NUMERIC="en_AU.utf8"
LC_TIME="en_AU.utf8"
LC_COLLATE="en_AU.utf8"
LC_MONETARY="en_AU.utf8"
LC_MESSAGES="en_AU.utf8"
LC_PAPER="en_AU.utf8"
LC_NAME="en_AU.utf8"
LC_ADDRESS="en_AU.utf8"
LC_TELEPHONE="en_AU.utf8"
LC_MEASUREMENT="en_AU.utf8"
LC_IDENTIFICATION="en_AU.utf8"
LC_ALL=

On ons, 2011-10-26 at 04:08 +0000, Shannon wrote:
> The following bug has been logged online:
>
> Bug reference:      6271
> Logged by:          Shannon
> Email address:      shannon@arc.net.au
> PostgreSQL version: 8.4.9
> Operating system:   Gentoo Linux 64bit
> Description:        psql -f reporting unexpected syntax errors on first
> command
> Details:
>
> Given the following 2 line SQL script in a file:
>
> --- test.sql ---
> SHOW client_encoding;
> SHOW client_encoding;
> ----------------
>
> $ psql -ftest.sql -U postgres template1
>
> psql:test.sql:1: ERROR:  syntax error at or near "SHOW"
> LINE 1: SHOW client_encoding;
>         ^
>  client_encoding
> -----------------
>  UTF8
> (1 row)

Look at the file with a hex editor or od or something like that to see
whether there are any "invisible" characters on the first line.  A
Unicode byte-order mark is a possibility.

Excerpts from Shannon's message of mié oct 26 01:08:00 -0300 2011:

> Given the following 2 line SQL script in a file:
>
> --- test.sql ---
> SHOW client_encoding;
> SHOW client_encoding;
> ----------------
>
> $ psql -ftest.sql -U postgres template1
>
> psql:test.sql:1: ERROR:  syntax error at or near "SHOW"
> LINE 1: SHOW client_encoding;
>         ^
>  client_encoding
> -----------------
>  UTF8
> (1 row)
>
>
> See what happened there? The first line is treated as a syntax error and the
> second line works which is insane because the two lines are identical!

They are not identical -- the first line contains a BOM, which I
actually see in the error messages you pasted (but you likely don't):

> psql:test.sql:1: ERROR:  syntax error at or near "<feff>SHOW"
> LINE 1: <feff>SHOW client_encoding;

This is being hidden from you by your text editor.  You need to remove
those bytes before passing the file to psql.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support