Reputation: 223

Execute several .sql files in a single transaction using PostgreSQL and bash

Say I have the files:

file1.sql
file2.sql
file3.sql

I need all three files to be executed in a single transaction. I'm looking for a bash script like:

psql -h hostname -U username dbname -c "
begin;
\i file1.sql
\i file2.sql
\i file3.sql
commit;"

This fails with an error: Syntax error at or near "\".

I also tried connecting to the DB first and then executing the fails, like that:

psql dbname
begin;
\i file1.sql
\i file2.sql
\i file3.sql
commit;

This also fails, because the 'begin' command executes only when the connection is terminated.

Is it possible to execute several .sql files in a single transaction using PostgreSQL and bash?

Edit:

The rough structure of each of the files is similar:

SET CLIENT_ENCODING TO 'WIN1251';
\i file4.sql
\i file5.sql
<etc>
RESET CLIENT_ENCODING;

Upvotes: 17

Answers (4)

Reputation: 462

FYI, for Windows command line:

FOR /F "usebackq" %A IN (`dir *.sql /b/a-d`) DO psql -f %A

Upvotes: 3

Reputation: 852

You can also use the -1 or --single-transaction option to execute all your scripts in a transaction:

cat file*.sql | psql -1

Upvotes: 23

Reputation: 4513

Either use a sub-shell:

#!/bin/sh
 (echo "BEGIN;"; cat file1.sql; cat file2.sql; echo "COMMIT;") \
 | psql -U the_user the_database

#eof

or use a here-document:

#!/bin/sh
psql -U the_user the_database <<OMG
BEGIN;

\i file1.sql

\i file2.sql

COMMIT;
OMG

#eof

NOTE: in HERE-documents there will be no globbing, so file*sql will not be expanded. Shell-variables will be expanded, even within quotes.

Upvotes: 13

Reputation: 7541

I'd create new files for the startup (start transaction, set encoding etc) and finish (commit).

Then run something like:

cat startup.sql file*.sql finish.sql | psql dbname

Upvotes: 5