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

Question

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;

you could build a single file to execute cat ing the files together with printf begin+end around — amdixon
– amdixon, Commented Jul 21, 2015 at 11:01
@amdixon Yes, I could, but each of the files also contain \i commands. So the result is going to be the same. — George Georgiev
– George Georgiev, Commented Jul 21, 2015 at 11:06
@amdixon The files are automatically generated, I don't control them. — George Georgiev
– George Georgiev, Commented Jul 21, 2015 at 11:11
Either a sub-shell: (echo "BEGIN;"; cat file1.sql; cat file2.sql; ... echo "COMMIT;") | psql ... or use a here-document. — joop
– joop, Commented Jul 21, 2015 at 11:27

TianyuZhu · Accepted Answer · 2016-07-14 15:41:37Z

23

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

cat file*.sql | psql -1

answered Jul 14, 2016 at 15:41

TianyuZhu

8527 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

joop · Accepted Answer · 2015-07-21 11:34:21Z

13

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.

answered Jul 21, 2015 at 11:34

joop

4,5431 gold badge18 silver badges26 bronze badges

1 Comment

George Georgiev Over a year ago

Yes, the sub-shell works, because of the pipe. Didn't think to pipe the files into psql. Thank you for the help.

Gary - Stand with Ukraine · Accepted Answer · 2015-07-21 11:31:26Z

5

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

answered Jul 21, 2015 at 11:31

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

1 Comment

George Georgiev Over a year ago

I used the first solution, but reckon this one would work either.

BingLi224 · Accepted Answer · 2019-06-29 19:27:22Z

3

FYI, for Windows command line:

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

answered Jun 29, 2019 at 19:27

BingLi224

4622 gold badges6 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related