PostgreSQL disable more output

Question

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.

Kendo

Answer 1

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.

Irrecusable

That stops it from using a pager but it doesn't stop the output, no? I'm guessing you'd need PAGER="/dev/null" psql db -P pager=always -f sql.sql to make it always kill output. – Timer

Answer 2

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.

Ayotte

This answer was more helpful than anything about -P in the man pages. Thanks! – Socalled

Answer 3

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

psql ... | cat

Mcnully

Answer 4

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

Timer

While this info is sound in principle, it doesn't work at all in this specific case, because psql does not send the informative messages to stderr. They are intermingled with data on stdout. – Limey

Suddenly I realise that your point is that the final '&>' case can be used by the OP to stop the pager, by suppressing all output. I'll remove my downvote if you amend your answer in any way. – Limey

Answer 5

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

Safeconduct

Usually it's better to explain a solution instead of just posting some rows of anonymous code. You can read How do I write a good answer, and also Explaining entirely code-based answers – Quietly

Answer 6

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

Liggitt

Answer 7

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

Classless

Answer 8

This syntax worked for me in an SSH session:

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

Rustle