Running SQL script through psql gives syntax errors that don't occur in PgAdmin

Question

I have the following script to create a table:

-- Create State table.
DROP TABLE IF EXISTS "State" CASCADE;
CREATE TABLE "State" (
 StateID SERIAL PRIMARY KEY NOT NULL,
 StateName VARCHAR(50)
);

It runs fine in the query tool of PgAdmin. But when I try to run it from the command line using psql:

psql -U postgres -d dbname -f 00101-CreateStateTable.sql

I get a syntax error as shown below.

2: ERROR:  syntax error at or near ""
LINE 1: 
        ^
psql:00101-CreateStateTable.sql:6: NOTICE:  CREATE TABLE will create implicit sequence "State_stateid_seq" for serial column "State.stateid"
psql:00101-CreateStateTable.sql:6: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "State_pkey" for table "State"
CREATE TABLE

Why do I get a syntax error using psql and not with pgAdmin?

Euphuism

Answer 1

Run your file 00101-CreateStateTable.sql through a hex dumper. I'll bet you have a UTF-16 marker at the beginning of the file (before the "--" comment characters).

Interlaminate

0000000 ef bb bf 2d 2d 20 43 72 65 61 74 65 20 53 74 61 0000010 74 65 20 74 61 62 6c 65 2e 0a 44 52 4f 50 20 54 0000020 41 42 4c 45 20 49 46 20 45 58 49 53 54 53 20 22 0000030 53 74 61 74 65 22 20 43 41 53 43 41 44 45 3b 0a 0000040 43 52 45 41 54 45 20 54 41 42 4c 45 20 22 53 74 0000050 61 74 65 22 20 28 0a 09 53 74 61 74 65 49 44 20 0000060 53 45 52 49 41 4c 20 50 52 49 4d 41 52 59 20 4b 0000070 45 59 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 53 74 0000080 61 74 65 4e 61 6d 65 20 56 41 52 43 48 41 52 28 0000090 35 30 29 0a 29 3b 0000096 Does this mean anything to you? – Euphuism

It's the output from OSX hexdump on that file – Euphuism

I've just used a function of my text editor to open the file using UTF-16 encoding. This shows strange characters. When opening using UTF-8 it shows fine. – Euphuism

I think you're right. I saved the file using UTF-8 no BOM (byte order marker) and the syntax order is gone. – Euphuism

Yes that's it. The irony is that the editor people would use for such scripts (pgadmin) doesn't save them without the BOM then. Crazy. – Euphuism

The good news is that in PostgreSQL 9.0, psql will be able to deal with script files with BOM at the beginning (for UTF8 - it still doesn't deal natively with UTF16 files at all) – Caleb

The bad news is that PostgreSQL 9.2 still can't handle files with BOMs – Yesteryear

Some tools write a BOM for UTF-8 too, as a marker that "this is UTF-8 text". Even though UTF-8 does not require a BOM. – Jennifferjennilee

Answer 2

To remove BOM sequence on Ubuntu you can use bomstrip, bomstrip-files

Papery

Answer 3

What version(-s) do you use? IF EXISTS came with version 8.2, maybe you're connection with version 8.1 or older when you use psql.

Christchurch

Answer 4

Thank you guys. I have been struggling with this issue for a few weeks. I could not run my SQL scripts using PSQL. I thought I have some issues with my OS, now I know it is the BOM issue in my text file. I installed bomstrip packet in Ubuntu and now all my SQL scripts are working again.

Econah