psql: Run commands in batch mode

Question

Consider a .sql file with a few selects and updates. I would like to run this file from a script using the psql utility:

psql -h whatever.rds.amazonaws.com -U user dbname -f commands.sql

The problem is that I get an interactive screen with the results of the query and an (0 rows)(END), which requires pressing esc in order to go back to the normal shell.

I have tried using --echo-all and/or --single-transaction following the psql manual, with no luck.

How do I run my sql file in non-interactive mode, only displaying the results an exiting the script?

Answer 1

For batch operation I generally use:

psql -v ON_ERROR_STOP=1 -P pager=off --single-transaction

... however, generally psql should detect that stdout is not a terminal and automatically turn pager off if you're redirecting its stdout.