CODEWITHSUNDEEP
postgresql
Badr4si
Badr4si

Reputation: 87

syntax error at or near "(" COPY FROM WITH ( FORMAT csv, DELIMITER E'\t', QUOTE '*', HEADER false, ENCODING 'UTF8')

I'm Importing data from a txt file This is the table

CREATE TABLE test.geonames_load(geonameid INTEGER PRIMARY KEY,
  name VARCHAR(200),
  asciiname VARCHAR(200),
  alternatenames VARCHAR,
  latitude FLOAT8,
  longitude FLOAT8,
  feature_class char(1),
  feature_code VARCHAR(10),
  country_code VARCHAR(2), 
  cc2 VARCHAR(60),
  admin1 VARCHAR(20),
  admin2 VARCHAR(80),
  admin3 VARCHAR(20),
  admin4 VARCHAR(20),
  population INTEGER,
  elevation INTEGER,
  dem INTEGER,
  timezone VARCHAR(40),
  modification VARCHAR(18)
);

After I tried to copy from a txt file 

COPY test.geonames_load FROM 'C:Program Files/PostgreSQL/8.4/data/US/US.txt' WITH (
  FORMAT csv,
  DELIMITER E'\t',
  QUOTE '*',
  HEADER false,
  ENCODING 'UTF8'
);

But it shows me an Error 

ERROR: syntax error at or near "("
LINE 1: ... FROM 'C:Program Files/PostgreSQL/8.4/data/US/US.txt' WITH (
                                                                      ^
********** Error **********

ERROR: syntax error at or near "("
SQL State: 42601
Character: 83

Upvotes: 7

Views: 23210

Answers (2)

Jeremy Thompson
Jeremy Thompson

Reputation: 65534

SQL Error [42601]: ERROR: syntax error at or near "(" Position: 51

For me the reason was due to missing a Column header, eg no middle name header:

ID, FirstName, , Surname

Correcting the CSV file with the header and removing reserved characters from column headers fixed it.

Upvotes: 0

Daniel Vérité
Daniel Vérité

Reputation: 61506

The syntax of COPY has changed quite a bit in version 9.0 compared to 8.4

Assuming you're using version 8.4, based on this .../PostgreSQL/8.4/... path, the syntax that applies is documented here:

http://www.postgresql.org/docs/8.4/static/sql-copy.html

And it does not allow any parenthesis after the WITH keyword that appeared in 9.0, nor the ENCODING option that appeared in 9.1

It looks like you need to adapt the statement to your exact version of PostgreSQL.

Upvotes: 3

Related Questions