PostgreSQL从远程服务器将结果导出为CSV [英] PostgreSQL export result as CSV from remote server

问题描述

我已经阅读了所有其他解决方案，但都无法满足我的需要，我不使用Java，没有超级用户权限，并且服务器中未安装API。

I have read all other solutions and none adapts to my needs, I do not use Java, I do not have super user rights and I do not have API's installed in my server.

我在远程PostgreSQL服务器上具有选择权限，我想在其中远程运行查询并将其结果导出到本地服务器中的.csv文件中。

I have select rights on a remote PostgreSQL server and I want to run a query in it remotely and export its results into a .csv file in my local server.

一旦我设法建立与服务器的连接，我首先必须定义数据库，然后是模式，然后是表，这使得以下代码行不起作用：

Once I manage to establish the connection to the server I first have to define the DB, then the schema and then the table, fact that makes the following lines of code not work:

\copy schema.products TO '/home/localfolder/products.csv' CSV DELIMITER ','

copy (Select * From schema.products) To '/home/localfolder/products.csv' With CSV;

我还尝试了以下bash命令：

I have also tried the following bash command:

 psql -d DB -c "select * from schema.products;" > /home/localfolder/products.csv

并将其记录为以下结果：

and logging it with the following result:

-bash：home / localfolder / products.csv：无此类文件或目录

-bash: home/localfolder/products.csv: No such file or directory

如果有人能对此有所启发，我将不胜感激。

I would really appreciate if someone can show a light on this.

推荐答案

您尝试过吗？我现在没有psql对其进行测试。

Have you tried this? I do not have psql right now to test it.

echo "COPY (SELECT * from schema.products) TO STDOUT with CSV HEADER" | psql -o '/home/localfolder/products.csv'

详细信息：

-o filename   Put  all  output into file filename.  The path must be writable by the client.
echo builtin + piping (|) pass command to psql

这篇关于PostgreSQL从远程服务器将结果导出为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！