SQL 如何在 Psql 输出中隐藏结果集装饰
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9934264/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to hide result set decoration in Psql output
提问by Cerin
How do you hide the column names and row count in the output from psql?
如何在 psql 的输出中隐藏列名和行数？
I'm running a SQL query via psql with:
我正在通过 psql 运行 SQL 查询：
psql --user=myuser -d mydb --output=result.txt -c "SELECT * FROM mytable;"
and I'm expecting output like:
我期待输出如下：
1,abc
2,def
3,xyz
but instead I get:
但我得到：
id,text
-------
1,abc
2,def
3,xyz
(3 rows)
Of course, it's not impossible to filter the top two rows and bottom row out after the fact, but it there a way to do it with only psql? Reading over its manpage, I see options for controlling the field delimiter, but nothing for hiding extraneous output.
当然，事后过滤掉前两行和底行也不是不可能，但是有没有办法只用 psql 来做到这一点？阅读其联机帮助页，我看到了用于控制字段分隔符的选项，但没有用于隐藏无关输出的选项。
回答by ruakh
You can use the -tor --tuples-onlyoption:
您可以使用-t或--tuples-only选项：
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 COPYcommand. I no longer have any PostgreSQL instances handy to test with, but I think you can write something along these lines:
您可能还需要检查的COPY命令。我不再有任何 PostgreSQL 实例可以方便地进行测试，但我认为您可以按照以下方式编写一些内容：
psql --user=myuser -d mydb -c "COPY mytable TO 'result.txt' DELIMITER ','"
(except that result.txtwill need to be an absolute path). The COPYcommand also supports a more-intelligent CSV format; see its documentation.
（除了result.txt需要是绝对路径）。该COPY命令还支持更智能的 CSV 格式；请参阅其文档。
回答by epic_fil
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 \psetto turn off just the rowcount "footer").
您还可以从 psql 内部重定向输出并使用相同的选项。使用 \o 设置输出文件，使用 \t 仅输出元组（或仅\pset关闭行数“页脚”）。
\o /home/flynn/queryout.txt
\t on
SELECT * FROM a_table;
\t off
\o
Alternatively,
或者，
\o /home/flynn/queryout.txt
\pset footer off
. . .
回答by Yordan Georgiev
usually when you want to parse the psql generated outputyou would want to set the -Aand -F...
通常当你想解析 psql 生成的输出时，你会想要设置-A和-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:
您可以在此处找到完整的 bash 调用示例：
