I am running a script on my PostgreSQL server:

psql db -f sql.sql

from bash or in a cron script.

It keeps trying to paginate the output with more or less.

How do I disable result pagination in psql?

All I want to do is change the data, I don't care about any output.

8 Answers

To disable pagination but retain the output, use:

\pset pager off

To remember this setting, add it to your ~/.psqlrc, e.g. like this: echo \\pset pager off >> ~/.psqlrc

See the psql manual.

On older versions of Pg it was just a toggle, so \pset pager

To completely suppress query output, use \o /dev/null in your psql script.

To suppress psql's informational output, run it with -q or set QUIET=1 in the environment.

---

To produce results and throw them away you can redirect stdout to /dev/null with:

psql db -f sql.sql >/dev/null

You can redirect both stdout and stderr with:

psql db -f sql.sql >&/dev/null

but I don't recommend that, as it'll throw away error information that might warn you something isn't going right. You're also producing results and throwing them away, which is inefficient; you're better off just not producing them in the first place by adjusting your queries.

1

I was looking for this too, I found the way in a similar question on ServerFault:

psql -P pager=off <other params>

turns off the paging thing, without suppressing output.

1

Here's another option. It does have the advantage that you don't have to remember psql option names, etc.

psql ... | cat

0

bash, being a shell, has 2 streams you can redirect that output data: stdout and stderr, because this output needs to be redirected somewhere, linux has a specific 'discard everything' node reachable through /dev/null. Everything you send there will just disappear into the void.

(shells also have an input stream but I'll ignore this here since you asked for suppressing output)

These streams are represented by numbers: 1 for stdout and 2 for stderr.

So if you want to redirect just stdout you'd do that with the < and > operators (basically where it points to is where the data flows to)

suppose we want to suppress stdout (redirect to /dev/null):

psql db -f sql.sql > /dev/null

As you can see this is stdout is default, no stream number has been used if you wanted to use the stream number you'd write

psql db -f sql.sql 1> /dev/null

Now if you want to suppress stderror (stream number 2), you'd use

psql db -f sql.sql 2> /dev/null

You could also redirect one stream to another, for example stderror to stdout, which is useful if you want to save all output somewhere, regular and errors.

psql db -f sql.sql 2>&1 > log.txt

mind you there can not be spaces between 2>&1

Finally and sometimes most interesting is the fact that you can suppress all output by using &>, for when you want it 'perfectly quiet'

psql db -f sql.sql &> /dev/null

2

psql -U user -P pager=off -d database -c 'SQL';

1

psql db -f sql.sql > /dev/null

That is called PAGER

Just input \pset pager 0 into the command, to turn off

then

Enter \pset pager 1 if you want to turn it on again. It used for long output

This syntax worked for me in an SSH session:

psql db user --command "select * from table007" -A -P pager

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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