Thread: ERROR: must be superuser to COPY to or from a file

Hello
I want to fill in a table from a file, but when I use the COPY command I get the following error:

mydb=> COPY weather FROM '/home/fideito/weather.txt';
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.

But, what can I do if I'm not root?

Jorge Alberto wrote:
> Hello
> I want to fill in a table from a file, but when I use the COPY command I
> get the following error:
>
> mydb=> COPY weather FROM '/home/fideito/weather.txt';
> 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.
>
> But, what can I do if I'm not root?

Use \copy (as the error message suggests) rather than copy.  That should
do it.

Sean

there is more than a way to skin a cat...


CREATE TABLE table1 (
   code char(5),
   name char(10)
);

synack@deimos db $ cat /tmp/tt.txt
1,kamchi
2,mahabat
3,kamila

postgres@deimos ~ $ cat /tmp/tt.txt | psql -h localhost -d tt -c "copy
table1 from stdin delimiter ',';"

postgres@deimos ~ $ psql -d tt -c "select * from table1;"
  code  |    name
-------+------------
  1     | kamchi
  2     | mahabat
  3     | kamila
(3 rows)


Rgds,

Kamchybek Jusupov
kjusupov@gmail.com



On Sep 29, 07, at 3:40 AM, Jorge Alberto wrote:

> Hello
> I want to fill in a table from a file, but when I use the COPY
> command I get the following error:
>
> mydb=> COPY weather FROM '/home/fideito/weather.txt';
> 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.
>
> But, what can I do if I'm not root?
>
>
>

thanks for your answer:
the command \copy works but the syntax is different

error:
mydb=> \copy weather FROM "/home/fideito/weather.txt";
"/home/fideito/weather.txt";: No such file or directory

error:
mydb=> \copy weather FROM "/home/fideito/weather.txt"
"/home/fideito/weather.txt": No such file or directory

correct syntax:
mydb=> \copy weather FROM /home/fideito/weather.txt

I have to delete the "" and the ";" to make it work.
Do you know why is that?

--- Jorge Alberto <csnmgeek@gmail.com> wrote:

> correct syntax:
> mydb=> \copy weather FROM /home/fideito/weather.txt
>
> I have to delete the "" and the ";" to make it work.
> Do you know why is that?

my guess is that \copy is a psql command not a PostgreSQL command.

So \copy has different rules than than the syntax that PostgreSQL directly interprets.

Regards,
Richard Broersma Jr.