How to execute multiple sql files in postgreSQL linux?

Question

I have many .sql files in a folder (/home/myHSH/scripts) in linux debian. I want to know the command to execute or run all sql files inside the folder into postgreSQL v9.1 database.

PostgreSQL informations:

Database name=coolDB
User name=coolUser

Nice to have: if you know how to execute multiple sql files through GUI tools too like pgAdmin3.

Titty

Answer 1

From your command line, assuming you're using either Bash or ZSH (in short, anything but csh/tcsh):

for f in *.sql;
do
    psql coolDB coolUser -f "$f"
done

Torpedoman

I find this answer to be a much better option as it runs the files in alphanumerical order. So if you have files that are dependent on the order this will handle it correctly whereas xargs will run them any order. – Unswerving

Answer 2

The find command combined with -exec or xargs can make this really easy.

If you want to execute psql once per file, you can use the exec command like this

find . -iname "*.sql" -exec psql -U username -d databasename -q -f {} \;

-exec will execute the command once per result.

The psql command allows you to specify multiple files by calling each file with a new -f argument. e.g. you could build a command such as

psql -U username -d databasename -q -f file1 -f file2

This can be accomplished by piping the result of the find to an xargs command once to format the files with the -f argument and then again to execute the command itself.

find . -iname "*.sql" | xargs printf -- ' -f %s' | xargs -t psql -U username -d databasename -q

Mencher

with multiple -f attribute, psql takes a single connection or multiple connections proportionate to -f count? if proportionate, will be parallel? – Conjunctivitis