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.
1I 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.
1Here's another option. It does have the advantage that you don't have to remember psql option names, etc.
psql ... | cat 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
psql -U user -P pager=off -d database -c 'SQL'; 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 ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaGppaGV%2BeIWOqaasrJensrS9y2aboquRl7mmecyoqZ5ln6rBscHT