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

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:\tmp\test_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

CodePudding user response：

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:\tmp\test_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.

CodePudding user response：

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.