开发者

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

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 e开发者_运维知识库rror 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?


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


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


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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜