如何隐藏 Psql 输出的结果集装饰

如何在 psql 的输出中隐藏列名和行数？

我通过 psql 运行一个 SQL 查询:

psql --user=myuser -d mydb --output=result.txt -c "SELECT * FROM mytable;"

我期待的结果是:

1,abc
2,def
3,xyz

但我得到的是:

id,text
-------
1,abc
2,def
3,xyz
(3 rows)

当然，事后过滤前两行和后两行并不是不可能的，但是有没有办法只用 psql 来做到这一点呢？通过阅读它的页面，我看到了控制字段分隔符的选项，但是没有看到隐藏无关输出的选项。

63055 次浏览
小开
最佳答案

You can use the -t or --tuples-only option:

psql --user=myuser -d mydb --output=result.txt -t -c "SELECT * FROM mytable;"

Edited (more than a year later) to add:

You also might want to check out the COPY command. I no longer have any PostgreSQL instances handy to test with, but I think you can write something along these lines:

psql --user=myuser -d mydb -c "COPY mytable TO 'result.txt' DELIMITER ','"

(except that result.txt will need to be an absolute path). The COPY command also supports a more-intelligent CSV format; see its documentation.

小开

You can also redirect output from within psql and use the same option. Use \o to set the output file, and \t to output tuples only (or \pset to turn off just the rowcount "footer").

\o /home/flynn/queryout.txt
\t on
SELECT * FROM a_table;
\t off
\o

Alternatively,

\o /home/flynn/queryout.txt
\pset footer off
. . .
小开

usually when you want to parse the psql generated output you would want to set the -A and -F ...

    # generate t.col1, t.col2, t.col3 ...
while read -r c; do test -z "$c" || echo  , $table_name.$c  | \
perl -ne 's/\n//gm;print' ; \
done < <(cat << EOF | PGPASSWORD=${postgres_db_useradmin_pw:-} \
psql -A -F  -v -q -t -X -w -U \
${postgres_db_useradmin:-} --port $postgres_db_port --host $postgres_db_host -d \
$postgres_db_name -v table_name=${table_name:-}
SELECT column_name
FROM information_schema.columns
WHERE 1=1
AND table_schema = 'public'
AND table_name   =:'table_name'  ;
EOF
)
echo -e "\n\n"

You could find example of the full bash call here:


提交答案