A friend reported this issue to me and I find it a bit strange and even after spending some time on this, I couldn't really figure out what's going wrong. See attached two SQL files, bad.sql and good.sql. They look the exact same in the editor. In fact, the good.sql is created by copying lines from bad.sql in the editor. There is probably some control character that differentiate the two files, but ":set list" in vim does not show anything.
Now, if I use 8.4.9 psql to connect to the server running 9.0.10, I get the following results with bad.sql
$ psql postgres
psql (8.4.9, server 9.0.10)
WARNING: psql version 8.4, server version 9.0.
Some psql features might not work.
Type "help" for help.
postgres=# \i bad.sql
psql:bad.sql:4: ERROR: syntax error at or near ""
LINE 1:
^
psql:bad.sql:12: NOTICE: CREATE TABLE will create implicit sequence "history_id_seq" for serial column "
history.id"
psql:bad.sql:12: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "history_pkey" for table "history"
CREATE TABLE
postgres=#
Notice the syntax error above.
Now, if I run the good.sql, I don't see any errors.
$ psql postgres
psql (8.4.9, server 9.0.10)
WARNING: psql version 8.4, server version 9.0.
Some psql features might not work.
Type "help" for help.
postgres=# \i good.sql
DROP TABLE
psql:good.sql:12: NOTICE: CREATE TABLE will create implicit sequence "history_id_seq" for serial column "
history.id"
psql:good.sql:12: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "history_pkey" for table "history"
CREATE TABLE
Finally, if I use psql from 9.0.10 release, both the files run without any errors. See output from the offending SQL file below.
$ ./install/bin/psql postgres
psql (9.0.10)
Type "help" for help.
postgres=# \i bad.sql
DROP TABLE
psql:bad.sql:12: NOTICE: CREATE TABLE will create implicit sequence "history_id_seq" for serial column "
history.id"
psql:bad.sql:12: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "history_pkey" for table "history"
CREATE TABLE
While I'm almost certain that this has something to do with special characters that my naked eyes can not see, all my attempts to spot the difference has failed. So I really have two questions:
1. What's the difference between these files ?
2. Why 9.0 psql works fine with that difference, but 8.4 psql does not ?
Any suggestions ?
Thanks,
Pavan