PostgreSQL 9.3 – How to Run Commands in Batch Mode with psql

postgresql-9.3psql

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?

Best Answer

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.

Related Solutions

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

The -1 option to psql causes it to wrap a file specified by -f in a BEGIN..COMMIT block, making it a transaction.

Otherwise, add the BEGIN and COMMIT commands to your script so that it becomes a single transaction.

pg_dump Restore – Using psql with –disable-triggers

@dezso had the completely right idea:

All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data

The only thing left now was to make it happen.

So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created said table using the definition provided in my pgAdmin (but it can be done manually, too).

I left out triggers and constraints, as well as Foreign Keys, and then proceeded to "updating" the original table with the data from the backup table like the following:

BEGIN TRANSACTION;
ALTER TABLE table DISABLE TRIGGER ALL;

UPDATE table SET 
    (column1, column2, ...) = 
    (table_backup.column1, table_backup.colum2, ...)
FROM table_backup WHERE table.pk_column = table_backup.pk_column;

ALTER TABLE table ENABLE TRIGGER ALL;
-- I didn't but you can drop table_backup here
COMMIT;

So I am finally back with my original data, ready for the next testrun ;)

Best Answer

Related Solutions

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

pg_dump Restore – Using psql with –disable-triggers

Related Question