Best way to execute sql scripts using psql

Usually, the most desired way to run an sql script through psql is 1) with the ability to feed it variables that get evaluated by the script, and 2) with the expectation that the script die after the first error that gets encountered (rather than continuing on, as is the default).

Here's a sample that shows these two traits in action.

First, make a wrapper shell script to control how you feed your sql script to psql. In this example, we assume that there are two exactly two required arguments we want to give our shell script that we want to feed to our sql script.

#!/bin/bash

set -e
set -u

if [ $# != 2 ]; then
    echo "please enter a db host and a table suffix"
    exit 1
fi

export DBHOST=$1
export TSUFF=$2

psql \
    -X \
    -U user \
    -h $DBHOST \
    -f /path/to/sql/file.sql \
    --echo-all \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --set TSUFF=$TSUFF \
    --set QTSTUFF=\'$TSUFF\' \
    mydatabase

psql_exit_status = $?

if [ $psql_exit_status != 0 ]; then
    echo "psql failed while trying to run this sql script" 1>&2
    exit $psql_exit_status
fi

echo "sql script successful"
exit 0

A few notes on the above shell script:

• TNUM is fed to psql as both a bare value and a value wrapped in single quotes, so that we can use the value in table or schema names (TSUFF, unquoted) or in strings (QTSUFF, quoted)
• We use -X to ensure we do not use the current unix users's .psqlrc file
• We echo all messages to the console, so we know what sql is being executed (handy in the face of failures)
• we use ON_ERROR_STOP to stop our sql script as soon as something goes wrong
• We set AUTOCOMMIT off so that we do not get a commit after every statement in file.sql; instead, wherever there is a "commit;" in file.sql, there will be a commit. If you want the entire script to run as one transaction, put a "commit;" at then end of your sql script (and nowhere else); otherwise, the script will run successfully and then not get committed (roll back) at the end! See also the next tip on the --single-transaction flag.

Here are the contents of /path/to/sql/file.sql:

begin;
drop index this_index_:TSUFF;
commit;

begin;
create table new_table_:TSUFF (
    greeting text not null default '');
commit;

begin;
insert into new_table_:TSUFF (greeting)
values ('Hello from table ' || :QTSUFF);
commit;