PostgreSQL import CSV File causes Syntax Error

Question

I'm attempting to import a CSV file into a database via the "COPY" command; however, I get the (what seems common) error that I need to be a superuser and that I should use "\copy" instead. However, when using \copy, I get a syntax error:

ERROR:  syntax error at or near "\"
LINE 1: \copy

With the caret pointing to "\". Here's my query:

\copy tablename(column2, column3, column4, column5) from '/home/uploads/data.csv' WITH DELIMITER ',' CSV HEADER'

I tried both "copy" and "\copy". The first giving me a superuser error the latter giving me that syntax error. Any idea on how to fix it? make it work?

I'm executing the command via myPgAdmin's sql input field.

The only other question I have is concerning the importing of the columns via tablename(column2, column3 and so on. Is that the correct syntax for that?

Answer 1

\copy is a psql (commandline) command. It is not a regular SQL command.

You will need to use COPY instead (but that requires the file to be present on the database server)

Answer 2

Please refer to the postgres manual for COPY.

In pgAdmin (or the sql string you pass via a script or other db connection) you would just use COPY with no "\" prefix.

so enter something like : COPY tablename....

You need to make sure that you have the relevant privileges to run the command, so in this case you need to be able to log into the database and have write access to 'tablename'. Postgres also needs to be able to reach the file, so the path /home/uploads/ should be accessible on the database server and the postgres user should be able to read the file - check the permissions to the file and directory.

Answer 3

On my 9.1 system the error I get is quite informative:

or_examples=> copy comp_table_test from '/tmp/test';
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

If you read the hint again carefully it notes you can copy from stdin. This is what \copy in psql actually does in the back-end. See the docs at http://www.postgresql.org/docs/8.3/static/sql-copy.html for more information.

You could then put the body of the copy into your query or at least that's how it works in psql.

Answer 4

copy coordina from '/tmp/filename.txt' WITH NULL AS ;

That's how I do it in pgAdmin3. But check userrights carefully, sometimes the rights are the problem. (user postgres on filename.txt / chmod 777)

Answer 5

Another method is to create a view for the sql query. Then use the \copy command.

Answer 6

If you get this error because you are trying to export data from PGAdmin then here is what you do:

1. Right click on the database name on the left hand pane
2. Select 'PSQL Tool'
3. Type your command here with the SQL code being on the same line then hit enter