Postgres - Batch file to drop and recreate database
For the complete script scroll to the bottom of the page for the GitHub link Nothing kills the soul faster than repetition. Rather than repeating a task, it is best to spend the time scripting the task to reduce the time required to perform it. It’s fun to do as well. Rather than going through each step to drop and recreate a PostgreSQL database, we’ll script it using a MS-DOS batch file. Let’s breakdown the steps that the batch file will perform when called:
- Accept the name of a database
- Attempt to find the `psql.exe` file in `Program Files` and `Program Files (x86)`
- Drop the database, if it exists using psql.exe
- Create a new database with the same name using psql.exe
Accept the name of a database
The intention is that a database name will be passed to the batch file as an argument. Batch file arguments are retrieved using %1, %2, %3… and so on, the idea is that %1 reflects the first argument, %2 the second and so on. Since %1 isn’t a good variable name we’ll immediately store the argument in a variable like so:
```batchSET DB_NAME=%1
### Find the psql.exe file
Traditionally PostgreSQL will be installed to one of two possible locations (as previously mentioned) so let's check which directory has the file we are looking for like so:
```batchIF EXIST "C:\Program Files\PostgreSQL\bin\psql.exe" SET PSQL_DIR="C:\Program Files\PostgreSQL\bin\"
IF EXIST "C:\Program Files (x86)\PostgreSQL\bin\psql.exe" SET PSQL_DIR="C:\Program Files (x86)\PostgreSQL\bin\"
Drop the database, if it exists using psql.exe
Having determined where the psql.exe file is located, we can now use some command line parameters to run our SQL statement to drop the database. This command will prompt the user to specify a password:
```batchpsql.exe -h localhost/Mehaul -p 5432 -U postgres -c “drop database if exists %DB_NAME%”
<table>
<tbody>
<tr>
<td><strong>Argument</strong></td>
<td><strong>Description</strong></td>
</tr>
<tr>
<td>`-h`</td>
<td>the host where the PostgreSQL service can be found</td>
</tr>
<tr>
<td>`-p`</td>
<td>the port where the PostgreSQL service listens</td>
</tr>
<tr>
<td>`-U`</td>
<td>the username which should be used to connect to the service</td>
</tr>
<tr>
<td>`-c`</td>
<td>a single SQL command which will be executed and then psql.exe will exit</td>
</tr>
</tbody>
</table>
### Create a new database with the same name using psql.exe
For this step we repeat the previous step but this time we modify the `-c` parameter value to execute SQL in order to create the database:
```batchpsql.exe -h localhost/Mehaul -p 55432 -U postgres -c "create database %DB_NAME%"
Get the code on GitHub References: PSQL Command Line