Create a postgreSQL database programmatically [closed]

Question

I am building an application in Visual Studio 2012, which I would like every time it is run to check if a certain postgres database exists and if not then create one, with specified tables etc.

I've looked around in Google without luck. I want the database to be created from within my application programmatically but if someone knows if and how I can do this using an installer wizard that would be good too. The application is meant to be running on windows only but on all machines without exceptions.

Answer 1

Irrespective of the tools and programming languages used the approach you'll want to use for this is the same:

1. In your program, during startup connect to the template1 or postgres databases that're always available in a PostgreSQL install and issue a SELECT 1 FROM pg_database WHERE datname = ? and as the first parameter pass the desired database name.

2. Check the result set that is returned. If a row is returned then database exists, you're done, no further action required. If no row is returned then the database doesn't exist and you need to create it, so:

3. Issue a CREATE DATABASE mydatabasename; with any desired options like OWNER, ENCODING, etc per the manual to create the database its self. The new database will be empty.

4. Populate the database either by connecting to the new database in your application and sending a sequence of SQL commands from your application directly, or by invoking the psql command on the shell to read a sql script file and send that to the database. I'd generally prefer to run the SQL directly within my application.

If you instead want to create the DB during install that's mostly up to you and your installer, but it'll usually be as simple as a CREATE DATABASE call after PostgreSQL has started, then feeding psql a script.