Identical command-line command will not work with \i metacommand and filename

On Wed, Apr 21, 2010 at 6:07 PM, John Gage <jsmgage@numericable.fr> wrote:

I enter the identical command:

select * from mesh_descriptors;

using the psql command line and it works perfectly.

The same command in a file produces an immediate syntax error:

EFNWeb=# \i ./CopySql.sql
psql:./CopySql.sql:1: ERROR: syntax error at or near "select"
LINE 1: select * from mesh_descriptors;

when run from a file using \i

What am I doing wrong?

Do you have some funky hidden characters at the beginning of the file?

run:

od -a CopySql.sql

Look at the beginning, that'll show you character by character what's in
there (should reveal anything hidden).

--Scott

Yeesh. What the ding-dong is this?

JohnGage:EFNWebsite johngage$ od -a CopySql.sql
0000000 ? ? ? s e l e c t sp * sp f r o m
0000020 sp m e s h _ d e s c r i p t o r
0000040 s ; nl nl

What are the ?'s. Mon Dieu, what is going on?

This is a file that runs perfectly well in pgAdmin and that I edit in
Vim.

Sorry to be so ignorant and thanks,

John Gage

On Apr 22, 2010, at 3:34 AM, Scott Mead wrote:

Oh, I should add. Everything, the database, vim, is UTF-8.

On 22/04/10 08:24, John Gage wrote:

Yeesh. What the ding-dong is this?

JohnGage:EFNWebsite johngage$ od -a CopySql.sql
0000000 ? ? ? s e l e c t sp * sp f r o m
0000020 sp m e s h _ d e s c r i p t o r
0000040 s ; nl nl

What are the ?'s. Mon Dieu, what is going on?

http://en.wikipedia.org/wiki/Byte_order_mark

Tends to get added if you go through a Windows system. Useless for utf-8
afaik. Confuse the hell out of you because various tools parse and hide
them then you pipe the file to a script and everything falls over.

Bunch of scripts available here to remove them:
http://www.xs4all.nl/~mechiel/projects/bomstrip/

--
Richard Huxton
Archonet Ltd

http://en.wikipedia.org/wiki/Byte_order_mark

Tends to get added if you go through a Windows system. Useless for
utf-8 afaik. Confuse the hell out of you because various tools parse
and hide them then you pipe the file to a script and everything
falls over.

Bunch of scripts available here to remove them:
http://www.xs4all.nl/~mechiel/projects/bomstrip/

Correct. I found the following via Google.
"I created a file utf8.rb with this content: C:\>ruby -e "p
File.read('utf8.rb')" "\357\273\277puts \"Hello World\""
The "\357\273\277" part is the Byte Order Mark for UTF-8, my editor
automatically put it at the beginning of the file, because I saved it
as UTF-8."
At least it isn't some evil virus. Have to do Mr. WorkAround now.
Thank you very much for your help Scott and Richard,
John Gage

Well, well, well. Guess who the culprit is...

I edited the file both in Vim and in pgAdmin3 (1.10.2, Mar 9 2010, rev
8217), and the BOM shows up after saving the file with pgAdmin3.

I don't know if pgAdmin3 wants to keep this feature...

Thank everyone again for the excellent help.

John

On 2010-04-22, John Gage wrote:

Well, well, well. Guess who the culprit is...

I edited the file both in Vim and in pgAdmin3 (1.10.2, Mar 9
2010, rev
8217), and the BOM shows up after saving the file with pgAdmin3.

I don't know if pgAdmin3 wants to keep this feature...

Thank everyone again for the excellent help.

John

I do not think that it is a feature. Instead I think it is a bug in the
query parser. The BOM is a non-breakable space character. Leading space
characters should not harm.

--
Robert...

This may be germane:

Filename: trunk/pgadmin3/src/ui/pgadmin3.lng
Revision 2954 - (view) (download) - [select for diffs]
Modified Sun Nov 30 20:13:28 2003 GMT (6 years, 4 months ago) by andreas
File length: 1301 byte(s)
Diff to previous 2840
adding UTF-8 BOM

On Thu, Apr 22, 2010 at 10:41 AM, John Gage <jsmgage@numericable.fr> wrote:

This may be germane:

Filename: trunk/pgadmin3/src/ui/pgadmin3.lng
Revision 2954 - (view) (download) - [select for diffs]
Modified Sun Nov 30 20:13:28 2003 GMT (6 years, 4 months ago) by andreas
File length: 1301 byte(s)
Diff to previous 2840
adding UTF-8 BOM

No, that's a BOM being added to a multi-language source file.
pgAdmin's Unicode support has always written an appropriate BOM to
Unicode files. See
http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/pgadmin/utils/utffile.cpp

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

I think I should clarify my original "problem" and what I see as the
difficulty in general.

I saved a UTF-8 file with the pgAdmin Query tool, which added the BOM
to the beginning of the file during the save.

I then attempted to run the file using psql with the \i meta command.

I got a syntax error pointing at the very beginning of the file which
turned out to be on account of the BOM.

In other words, files saved with pgAdmin3 Query tool cannot then be
run as files of SQL commands by psql.

The two applications are incompatible at that level.

On Apr 22, 2010, at 11:50 AM, Dave Page wrote:

On Thu, Apr 22, 2010 at 12:02, John Gage <jsmgage@numericable.fr> wrote:

I think I should clarify my original "problem" and what I see as the
difficulty in general.

I saved a UTF-8 file with the pgAdmin Query tool, which added the BOM to the
beginning of the file during the save.

I then attempted to run the file using psql with the \i meta command.

I got a syntax error pointing at the very beginning of the file which turned
out to be on account of the BOM.

In other words, files saved with pgAdmin3 Query tool cannot then be run as
files of SQL commands by psql.

The two applications are incompatible at that level.

FYI, psql in PostgreSQL 9.0 will ignore UTF8 BOMs.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Additionally, if the Vim option "bomb" is set to "nobomb" it will
strip the BOM. This solves my "problem" by permitting editing files
in both Vim and pgAdmin3 Query tool and then being able to run the
files in psql.

:set nobomb [in Vim]

On Apr 22, 2010, at 12:12 PM, Magnus Hagander wrote:

You could also disable "Read and write Unicode UTF-8 files" in
Options->Preferences.
It will not write a BOM but you will not have UTF-8 either.

On Thu, Apr 22, 2010 at 07:29, John Gage <jsmgage@numericable.fr> wrote:

John Gage wrote:

http://en.wikipedia.org/wiki/Byte_order_mark

Tends to get added if you go through a Windows system. Useless for
utf-8 afaik. Confuse the hell out of you because various tools parse
and hide them then you pipe the file to a script and everything
falls over.

Bunch of scripts available here to remove them:
http://www.xs4all.nl/~mechiel/projects/bomstrip/

Correct. I found the following via Google.
"I created a file utf8.rb with this content: C:\>ruby -e "p
File.read('utf8.rb')" "\357\273\277puts \"Hello World\""
The "\357\273\277" part is the Byte Order Mark for UTF-8, my editor
automatically put it at the beginning of the file, because I saved it
as UTF-8."
At least it isn't some evil virus. Have to do Mr. WorkAround now.

FYI, this is fixed in Postgres 9.0:

Ignore leading UTF-8-encoded Unicode byte-order marker in
psql (Itagaki Takahiro)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com