How do you copy data from file to table in SQL?

Question

How do you copy data from a file to a table in SQL? I'm using pgAdmin3 on a Macbook.

The table name is tutor, and the name of the file is tutor.rtf.

I use the following query:

COPY tutor
FROM /Users/.../tutor.rtf
WITH DELIMITER ',';

but got the error "permission denied'.

The file is not locked. So how do you solve this problem? Or is there any other quicker way to copy data from file to table except for INSERT INTO ... VALUE(); ?

My knee jerk reaction is to say "Please post the result of `ls -l /Users/.../tutor.rtf` so that we can all see the file has the proper permissions. (since you say the file is not locked)" On second look, do you have one too many dots in there? Or did you just omit the full path for brevity? I'd request you paste the original path if you changed it for this post. — Prashant Kumar, Dec 09 '13 at 23:27

score 0 · Answer 1 · answered Dec 09 '13 at 01:35

COPY opens the file using the PostgreSQL server backend, so it requires that the user postgresql runs as have read permission (for COPY FROM) for the file in question. It also requires the same SQL-level access rights to the table as INSERT, but I suspect it's file permissions that're getting you here.

Most likely the postgres or postgres_ (depending on how you installed PostgreSQL) user doesn't have read access to /Users/somepath/tutor.rtf or some parent directory of that file.

The easiest solution is to use psql's \copy command, which reads the file using the client permissions, rather than those of the server, and uses a path relative to the client's current working directory. This command is not available in PgAdmin-III.

Newer PgAdmin-III versions have the Import command in the table context menu. See importing tables from file in the PgAdmin-III docs. This does the equivalent of psql's \copy command, reading the file with the access rights of the PgAdmin-III application.

Alternately you can use the server-side COPY command by making sure every directory from /Users up somepath has world-execute rights - meaning users can traverse it, cd into it, etc, but can't list its contents without r rights too. Then either set the file to group postgres and make sure it has group read rights, or make it world-readable.

How do you copy data from file to table in SQL?

1 Answers1