8

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.

Improve this question
2
  • I'm not convinced this should've been closevoted and most of the names are unfamilar so I'd say they came from the visual-studio tag.followers. Nominating for re-opening, though honestly this isn't really anything to do with visual studio so I'm removing the tag. Commented May 12, 2013 at 23:54
  • Yeah thank you for whatever you did. I'm not sure I have understood what "closevoted" is because I'm very new to the stackoverflow forums. But thank you for your contribution in any way! :) Commented May 13, 2013 at 14:36

1 Answer 1

Reset to default
10

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you very much! I will try that it was very helpfull and clear!
@GeorgeElementSotiriou Glad to help. PostgreSQL is an OK database for embedding in an application but please make your application install its copy of PostgreSQL on a non-default port (ie: not 5432), service account and database location so it does not conflict with any PostgreSQL the user might install themselves. Your installer should create its own service or your app should start/stop PostgreSQL on demand its self using pg_ctl.
In my case (perl with Postgres 9.3) I had to connect to the database 'postgres' as user 'postgres' before I could create a database.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.