postgresql psql - 将命令结果保存到文件

psql - save results of command to a file

提问by pstanton

I'm using psql's \dtto list all tables in a database and I need to save the results.

我正在使用 psql\dt列出数据库中的所有表，我需要保存结果。

What is the syntax to export the results of a psql command to a file?

将 psql 命令的结果导出到文件的语法是什么？

回答by jhwist

From psql's help (\?):

来自 psql 的帮助 ( \?)：

\o [FILE] send all query results to file or |pipe

\o [FILE] 将所有查询结果发送到文件或 |管道

The sequence of commands will look like this:

命令序列如下所示：

[wist@scifres ~]$ psql db
Welcome to psql 8.3.6, the PostgreSQL interactive terminal

db=>\o out.txt
db=>\dt
db=>\q

回答by intgr

The psql \ocommand was already described by jhwist.

\ojhwist 已经描述了psql命令。

An alternative approach is using the COPY TOcommand to write directly to a file on the server. This has the advantage that it's dumped in an easy-to-parse format of your choice -- rather than psql's tabulated format. It's also very easy to import to another table/database using COPY FROM.

另一种方法是使用COPY TO命令直接写入服务器上的文件。这样做的好处是它以您选择的易于解析的格式转储——而不是 psql 的表格格式。使用COPY FROM.

NB! This requires superuser privileges and will write to a file on the server.

注意！这需要超级用户权限并将写入服务器上的文件。

Example: COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';')

例子： COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';')

Creates a CSV file with ';' as the field separator.

创建一个带有“;”的 CSV 文件 作为字段分隔符。

As always, see the documentation for details

与往常一样，请参阅文档以获取详细信息

回答by Aakash Gupta

\copywhich is a postgres command can work for any user. Don't know if it works for \dt or not, but general syntax is reproduced from the following link Postgres SQL copy syntax

\copy这是一个 postgres 命令，适用于任何用户。不知道它是否适用于 \dt ，但从以下链接中复制了一般语法Postgres SQL copy syntax

\copy (select * from tempTable limit 100) to 'filenameinquotes' with header delimiter as ','

The above will save the output of the select query in the filename provided as a csv file

以上将把选择查询的输出保存在作为 csv 文件提供的文件名中

EDIT:

编辑：

For my psql server the following command works this is an older version v8.5

对于我的 psql 服务器，以下命令有效，这是一个较旧的版本 v8.5

copy (select * from table1) to 'full_path_filename' csv header;

回答by Yavuz

Use o parameter of pgsql command.

使用 pgsql 命令的 o 参数。

-o, --output=FILENAME send query results to file (or |pipe)

-o, --output=FILENAME 将查询结果发送到文件（或 |管道）

psql -d DatabaseName -U UserName -c "SELECT * FROM TABLE" -o /root/Desktop/file.txt

回答by Stephen

COPY tablename TO '/tmp/output.csv' DELIMITER ',' CSV HEADER;this command is used to store the entire table as csv

COPY tablename TO '/tmp/output.csv' DELIMITER ',' CSV HEADER;此命令用于将整个表存储为 csv

回答by Daniil Mashkin

If you got the following error ufgtoolspg=> COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';') ; 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.

如果出现以下错误 ufgtoolspg=> COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';') ; 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.

you can run it in this way:

你可以这样运行它：

psql somepsqllink_or_credentials -c "COPY (SELECT foo, bar FROM baz) TO STDOUT (format csv, delimiter ';')" > baz.csv

psql somepsqllink_or_credentials -c "COPY (SELECT foo, bar FROM baz) TO STDOUT (format csv, delimiter ';')" > baz.csv

回答by Er.Ankit H Gandhi

Use below query to store result in csv file

使用以下查询将结果存储在 csv 文件中

\copy (your query) to 'file path' csv header;

\copy（您的查询）到“文件路径”csv 标题；

Example

例子

\copy (select name,date_order from purchase_order) to '/home/ankit/Desktop/result.csv' cvs header;

\copy (select name,date_order from purchase_order) 到 '/home/ankit/Desktop/result.csv' cvs 头；

Hope this help you.

希望这对你有帮助。

回答by hlovdal

I assume that there exist some internal psql command for this, but you could also run the scriptcommand from util-linux-ngpackage:

我假设存在一些内部 psql 命令，但您也可以script从util-linux-ng包运行该命令：

DESCRIPTION Script makes a typescript of everything printed on your terminal.

描述 脚本为终端上打印的所有内容制作打字稿。

回答by Eduardo Lucio

This approach will work with any psql command from the simplest to the most complexwithout requiring any changes or adjustments to the original command.

这种方法适用于从最简单到最复杂的任何 psql 命令，而无需对原始命令进行任何更改或调整。

NOTE:For Linux servers.

注意：对于 Linux 服务器。

---

---

• Save the contents of your command to a file

• 将命令的内容保存到文件中

MODEL

模型

read -r -d '' FILE_CONTENT << 'HEREDOC'
[COMMAND_CONTENT]

HEREDOC
echo -n "$FILE_CONTENT" > sqlcmd

EXAMPLE

例子

read -r -d '' FILE_CONTENT << 'HEREDOC'
DO $f$
declare
    curid INT := 0;
    vdata BYTEA;
    badid VARCHAR;
    loc VARCHAR;
begin
FOR badid IN SELECT some_field FROM public.some_base LOOP
    begin
    select 'ctid - '||ctid||'pagenumber - '||(ctid::text::point) [0]::bigint
        into loc
        from public.some_base where some_field = badid;
        SELECT file||' '
        INTO vdata
        FROM public.some_base where some_field = badid;
    exception
        when others then
        raise notice 'Block/PageNumber - % ',loc;
            raise notice 'Corrupted id - % ', badid;
            --return;
    end;
end loop;
end;
$f$;

HEREDOC
echo -n "$FILE_CONTENT" > sqlcmd

• Run the command

• 运行命令

MODEL

模型

sudo -u postgres psql [some_db] -c "$(cat sqlcmd)" >>sqlop 2>&1

sudo -u postgres psql [some_db] -c "$(cat sqlcmd)" >>sqlop 2>&1

EXAMPLE

例子

sudo -u postgres psql some_db -c "$(cat sqlcmd)" >>sqlop 2>&1

sudo -u postgres psql some_db -c "$(cat sqlcmd)" >>sqlop 2>&1

• View/track your command output

• 查看/跟踪您的命令输出

cat sqlop

cat sqlop

Done! Thanks! =D

完毕！谢谢！=D

回答by Roman Rhrn Nesterov

Approach for docker

码头工人的方法

via psql command

通过 psql 命令

 docker exec -i %containerid% psql -U %user% -c '\dt' > tables.txt

or query from sql file

或从 sql 文件查询

docker exec -i %containerid% psql -U %user% < file.sql > data.txt