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

Answer 3

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: