Question 90405 in PostgreSQL Question posted in
The official documentation can be found here.

How to copy table to my computer from network database , if I haven't right superuser? POSTGRES – Postgresql

I want to copy a Postgres table in CSV format from a network database to my computer.
For example, here is its address

psql postgresql://login:[email protected]:5432/test_table

The problem is that I don’t have superuser rights and I can’t copy the table via pg_admin.

For example, if I make a request in pg_admin:

COPY test_table TO 'C:tmptest_table.csv' DELIMITER ',' CSV HEADER;

I get an error:

ERROR: must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT: Anyone can COPY to stdout or from stdin. psql’s copy command also works for anyone.
SQL state: 42501

As I understand it, it is possible to copy the table – but through the command line, right? How to do it in my case? Thank

Tags:

2

Answers


  1. 0

    Instead of using COPY with a path, use STDOUT. Then, redirect the output to a local path:

    psql -c "COPY test_table TO STDOUT DELIMITER ',' CSV HEADER" >> C:tmptest_table.csv

    See the documentation for COPY.

    In case you need this explanation: stdout stands for standard output, it means that the result of the command should be printed on your terminal. Using >> you redirect the output of the psql command to a file.

    Login or Signup to reply.
  2. 0

    I would just learn how to use the command line, but if you want to stick with pgAdmin4 you can right click on the table in the browser tree and then choose "Import/Export Data" and follow the dialog box. Doing that is basically equivalent to using copy from psql.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search