Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers 4

up vote 3 down vote accepted

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

share|improve this answer
    
How would you import CSV files directly through SQL rather than using a commandline? –  antjanus Aug 23 '12 at 19:45
    
@antjanus: you would use the COPY command as I have mentioned (and adam explained in detail) –  a_horse_with_no_name Aug 24 '12 at 6:27
    
again, that requires superuser permissions and isn't safe, right? –  antjanus Aug 24 '12 at 17:42
add comment

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.

share|improve this answer
add comment
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)

share|improve this answer
    
you have to be a db superuser to do that though hence the original complaint. –  Chris Travers Aug 31 '12 at 2:29
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.