CODEWITHSUNDEEP
sqlwindowspostgresqlbatch-filepsql
Kost
Kost

Reputation: 21

Run postgres and sql requests from one file

Could you help me create windows start.bat file which allow run psql.exe and do something like next:

  1. Delete db1 if exist

  2. Create db1

  3. Connect db1

  4. Create table t1

  5. Delete db2 if exist

  6. Create db2

  7. Connect db2

  8. Create table t2

I can do it step by step from the psql console, but don't understand how to do it from a batch *.bat file. For example if I write a string like: psql -U postgres db1, it connects to the db1, and stop execute other script cmds.. that's problem.

Upvotes: 0

Views: 158

Answers (1)

user330315
user330315

Reputation:

You need to put all the commands you whish to run into a single .sql script (a text file).

e.g.: setup_all.sql

drop database if exists db1;
create database db1;
-- switch to the newly created database
\connect db1
create table t1 (id integer);
drop database if exists db2;
create database db2;
create table t2 (id integer);

Then pass the script with the -f parameter to psql

psql -U postgres -d template1 -f setup_all.sql

If you put the above into a batch file make sure you specify the full path to the SQL script. Alternatively put the .bat and .sql into the same directory and use:

psql -U postgres -d template1 -f %~dp0setup_all.sql

The %~dp0 contains the directory in which the batch file is stored, so the SQL script will automatically be searched in the same directory.

You need to make sure the initial connection is not to the database you want to drop. So either connect to the template1 or postgres database.

Upvotes: 3

Related Questions