Обсуждение: Identical command-line command will not work with \i metacommand and filename

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?

Thanks,

John Gage

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:

> Do you have some funky hidden characters at the beginning of the file?
>
>    run:
>
>    od -a CopySql.sql
>

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

On Apr 22, 2010, at 3:34 AM, Scott Mead wrote:
>    run:
>
>    od -a CopySql.sql
>
>   Look at the beginning, that'll show you character by character
> what's in there (should reveal anything hidden).
>

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 Apr 22, 2010, at 9:37 AM, Richard Huxton 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/
>
> --
>  Richard Huxton
>  Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

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

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 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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:

> FYI, psql in PostgreSQL 9.0 will ignore UTF8 BOMs.

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