33

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran psql against that included the other files however I don't remember the syntax of this script.

3 Answers 3

40

Your script could look like this:

BEGIN;
\i file1.sql
\i file2.sql
COMMIT;

Or you could do something like this:

cat file1.sql file2.sql | psql -1 -f -
2
  • 1
    Understanding pipes in the shell helped me get the last command.
    – ma11hew28
    Commented Sep 24, 2015 at 15:13
  • Your example with BEGIN; COMMIT; does not work, you need semi-columns at the end of each \i line:
    – nichochar
    Commented Feb 14, 2019 at 22:53
3

You could also simply do:

psql -c "BEGIN TRANSACTION;" -f file1.sql -f file2.sql -c "COMMIT;"

This will actually run in four different chained parts through the psql:

  1. Run BEGIN TRANSACTION;
  2. Run file1.sql
  3. Run file2.sql
  4. Run COMMIT;
0

I did it using find in unix / linux with the path to the file sql past with rep_sql parameter with "read" instruction :

find -P ${rep_sql} -type f -name *.sql -execdir psql -d "mabase" -f {} +

It says: find not following links files whatever the name with ext as «.sql» and execute in the directory of the file psql working with mabase and execute the file found.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.