I'm using PostgreSQL 9.1.X

I am trying to build psql script to print results without a header but including a footer.


From the document above

\pset tuples_only

will turn both header and footer off. and

\pset footer off

will turn footer off only.

Is there a way in psql to turn the header off and keep the footer on?

I know there are many ways to work around this issue using shell/perl/whatever text tool you like, however I am wondering why there is a config for the footer but not one for the header?

id <--this line I don't want
---- <-- this line I don't want either
 1  <-- this line, yes
(1 row) <-- yes, I want it!
  • Are you on Unix? If so, you can use head/tail/grep/awk/sed/a million other things
    – Philᵀᴹ
    Sep 12, 2012 at 20:42

4 Answers 4


When executing psql from shell you can use -t (prints tuples only) option:

$ psql -t -c "SELECT version();"
 PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Within psql use \t to turn off printing header and row count.

  • This seems more consistent with future builds than cutting off some lines from the head and tail. Any PostgreSQL implementation that change the outlook of the header and footer will break those scripts that assume the number of discardable lines.
    – karatedog
    Jul 17, 2020 at 15:22
  • 3
    If you are only looking for a single value I'd use -A -t. Dec 20, 2021 at 9:27
  • -1 does not answer the question - the op specifically wanted to keep the footer and turn off only the header.
    – user9645
    Sep 21, 2023 at 11:46

My solution is not quite turning off but rather discarding headers.

You can try to tail the query output:

\o | tail -n +2

With \o, you can redirect output to a file or a pipe, like in this case. This solution has its flaw, too: at least in my case, after execution of SELECT [...], I don't get back to a prompt unless I press a key. And the first output row appears after a prompt. If you then redirect output to a file, it shouldn't be a problem though.

This behaviour can be avoided if you set the PAGER environmental variable appropriately and always use pager is psql:

$ export PAGER='tail -n +3'

$ psql -U postgres -d test
psql (9.1.4, server 9.1.5)

test=# \pset pager always
Pager is always used.

test=# select * from a;
  2 | b
  3 | b
(2 rows)

In the psql version that comes with PostgreSQL 9.2 you can use the \setenv command for convenience (I mean that you don't have to set an env variable which may affect other applications as well).

  • nice hack, good to know pager option. but really there is no option for header itself? Maybe I should check postgresql's mailist
    – skong
    Sep 14, 2012 at 15:20
  • @sinbadblue No. As you've already checked, no. You can post a feature request, though :) Would be a useful advancement. Sep 14, 2012 at 19:10

Have you tried \a, \pset pager off and \pset tuples_only?

Let's start with \a and \pset pager off first:

$ psql
psql (9.3.22, server 10.13)
WARNING: psql major version 9.3, server major version 10.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

=> \a
Output format is unaligned.
=> \pset pager off
Pager usage is off.
=> select version();
PostgreSQL 10.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

This removes most of the header formatting but still has the header name.

We introduce \pset tuples_only as well we get:

=> \pset tuples_only
Showing only tuples.
appstudiojobs=> select version();
PostgreSQL 10.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

So, this removes everything, i.e. both header and footer.

i.e. still doesn't meet the OP's exact requirements (i.e. header off, footer on), but, it is one step closer.

  • \a is what I was looking for, thanks!
    – kolypto
    Dec 1, 2021 at 11:32

You need to add in a psql command line option -P "footer=off". This option keep the columns titles in the result.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.