How to hide result set decoration in Psql output

Question

How do you hide the column names and row count in the output from psql?

I'm running a SQL query via psql with:

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.

Answer 1

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.

Answer 2

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 . . .