How can I remove the result set decoration from the Psql output?

Problem

How can the column names and row count in the psql output be hidden?

I’m using psql to run a SQL query that looks like this:

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

I’m hoping for anything along the lines of:

1,abc
2,def
3,xyz

Instead, I receive the following:

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

Of course, filtering the top two rows and bottom row out after the fact isn’t impossible, but is there a method to do it with simply psql? I notice choices for manipulating the field delimiter in its manpage, but none for hiding unnecessary output.

Asked by Cerin

Solution #1

The -t or —tuples-only options can be used:

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

Edited (over a year later) to include:

You should also look into the COPY command. I don’t have any PostgreSQL instances to test with right now, but I believe you could write something like this:

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

(With the exception that result.txt must be an absolute path). See the description for the COPY command, which also supports a more sophisticated CSV format.

Answered by ruakh

Solution #2

You can also use the same option to redirect output from within psql. Set the output file with o, and only output tuples with t. (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
. . .

Answered by epic_fil

Solution #3

Normally, if you want to parse the psql produced output, you’d use the -A and -F options…

    # 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 can see an example of a complete bash call here:

Answered by Yordan Georgiev

Post is based on https://stackoverflow.com/questions/9934264/how-to-hide-result-set-decoration-in-psql-output