106

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.

Cerin
  • 60,957
  • 96
  • 316
  • 522

3 Answers3

143

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.

ruakh
  • 175,680
  • 26
  • 273
  • 307
  • 30
    alternatively, if you want the header but not the row-count footer, run psql with --pset="footer=off" – Gabriel Burt Mar 21 '13 at 14:36
  • 4
    `COPY` can indeed be a valid alternative, but the file will end up on the server, and not on the machine where psql is run... – fvu Sep 01 '15 at 14:23
  • @fvu: Good point. I usually ran `psql` *on* the server, so for me that wasn't an issue . . . – ruakh Sep 01 '15 at 15:36
  • If you want the file to end up on the client filesystem use the psql's **[\COPY](http://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY)** (i.e. add a backslash in front of COPY command) – Alex Bitek Sep 30 '15 at 12:22
  • 3
    I think @GabrielBurt comment deserves to be a standalone answer. That's exactly what I was looking for to generate a csv with header but without the pesky "`(nnnn rows)`" footer. – Pierre D Sep 26 '16 at 23:14
  • @gabriel-burt comment is the correct answer `--pset="footer=off"`, Gabriel should post an aswer with just that. – Merlin May 13 '18 at 14:50
  • 1
    @Merlin: No, his comment is not the correct answer to the OP's question. A lot of people have found it useful, and that's great, but let's not pretend it's something it's not. – ruakh May 13 '18 at 22:16
  • Add the `--no-align` option to avoid obtaining unneeded whitespace in the output. – Diomidis Spinellis Aug 24 '21 at 16:45
32

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
. . .
Adobe
  • 12,967
  • 10
  • 85
  • 126
epic_fil
  • 659
  • 7
  • 15
9

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:

Yordan Georgiev
  • 5,114
  • 1
  • 56
  • 53
  • 2
    Well done for actually answering the question and winning the "Yet again the best answer is at the bottom of the page" prize. – Dino Dini Sep 10 '22 at 21:59