for writing: Permission denied in postgres using COPY (SELECT * from ) To DELIMITER ',' CSV HEADER";

Asked Oct 04 '14 at 04:01

Active Nov 21 '18 at 09:54

Viewed 2,748 times

1

My requirement is write the csv from postgres table data , then i facing the below issue , please give some tips to resolve this issue.

`org.postgresql.util.PSQLException: ERROR: could not open file`

when i use this query

`COPY (SELECT * from <table>) To <filename> DELIMITER ',' CSV HEADER";`

postgresql

edited Oct 04 '14 at 08:08

asked Oct 04 '14 at 04:01
Dinesh

11

1

2

1

Have you read the [fine COPY manual](http://www.postgresql.org/docs/current/static/sql-copy.html)? – mu is too short Oct 04 '14 at 04:19

@dinesh `COPY (SELECT * from mytable) To 'C:\csvfilename.csv' DELIMITER ',' CSV HEADER";` – Vivek S. Oct 04 '14 at 04:55

give `write` permission to `postgres` user – Hasan Ramezani Oct 04 '14 at 08:55

2 Answers2

2

You appear to be using PgJDBC.

Most likely you want to use the `CopyManager`, obtained from `PGConnection`, to `COPY` to/from the Java client. See this example, and the PgJDBC API docs.

If you instead intend to do `COPY` to and from the server file system, you must assign appropriate file system permissions so that the user the PostgreSQL server runs as - usually `postgres` - can read/write the desired locations.

edited May 23 '17 at 12:20
Community

1

1

answered Oct 04 '14 at 14:20
Craig Ringer

307,061

76

688

778

1

Save file where every user can. In this case user postgres.

`Copy (Select * From tablename To '/tmp/file.csv' With CSV DELIMITER ',';`

answered Nov 21 '18 at 09:54
Rafal

573

5

11

Question

My requirement is write the csv from postgres table data , then i facing the below issue , please give some tips to resolve this issue.

org.postgresql.util.PSQLException: ERROR: could not open file

when i use this query

COPY (SELECT * from <table>) To <filename> DELIMITER ',' CSV HEADER";

Have you read the [fine COPY manual](http://www.postgresql.org/docs/current/static/sql-copy.html)? — mu is too short, Oct 04 '14 at 04:19
@dinesh `COPY (SELECT * from mytable) To 'C:\csvfilename.csv' DELIMITER ',' CSV HEADER";` — Vivek S., Oct 04 '14 at 04:55

score 2 · Answer 1 · edited May 23 '17 at 12:20

You appear to be using PgJDBC.

Most likely you want to use the CopyManager, obtained from PGConnection, to COPY to/from the Java client. See this example, and the PgJDBC API docs.

If you instead intend to do COPY to and from the server file system, you must assign appropriate file system permissions so that the user the PostgreSQL server runs as - usually postgres - can read/write the desired locations.

score 1 · Answer 2 · answered Nov 21 '18 at 09:54

1

Save file where every user can. In this case user postgres.

Copy (Select * From tablename To '/tmp/file.csv' With CSV DELIMITER ',';

answered Nov 21 '18 at 09:54

Rafal

573
5
11