2

I am new to the PostgreSQL database. What my visual c++ application needs to do is to create multiple tables and add/retrieve data from them.

Each session of my application should create a new and distinct database. I can use the current date and time for a unique database name.

There should also be an option to delete all the databases.

I have worked out how to connect to a database, create tables, and add data to tables. I am not sure how to make a new database for each run or how to retrieve number and name of databases if user want to clear all databases.

Please help.

Improve this question
3
  • Please be more specific. PostgreSQL version? How are you connecting to PostgreSQL - via ODBC, ADO, native libpq, libpqtypes/libpqxx, Npgsql, NHibernate, what? Commented Jul 25, 2012 at 13:47
  • would use latest version like 9.1.4.1. And I will be connecting using libpq. Commented Jul 25, 2012 at 15:50
  • if you know how to issue CREATE TABLE with libpq, you can do CREATE DATABASE based on almost the same code. Commented Jul 25, 2012 at 18:03

1 Answer 1

Reset to default
3

See the libpq examples in the documentation. The example program shows you how to list databases, and in general how to execute commands against the database. The example code there is trivial to adapt to creating and dropping databases.

Creating a database is a simple CREATE DATABASE SQL statement, same as any other libpq operation. You must connect to a temporary database (usually template1) to issue the CREATE DATABASE, then disconnect and make a new connection to the database you just created.

Rather than creating new databases, consider creating new schema instead. Much less hassle, since all you need to do is change the search_path or prefix your table references, you don't have to disconnect and reconnect to change schemas. See the documentation on schemas.

I question the wisdom of your design, though. It is rarely a good idea for applications to be creating and dropping databases (or tables, except temporary tables) as a normal part of their operation. Maybe if you elaborated on why you want to do this, we can come up with solutions that may be easier and/or perform better than your current approach.

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

9 Comments

each execution of my application needs few (7-8) tables. And at each execution, a fresh set of tables should be created. Old tables should persist till user wants. Now, for creating a different set of tables at each execution, I think making a different database with time stamp can help me. And, giving user option of removing old tables, I can easily remove all databases which will in-turn remove all tables. before creating new one for the current session.
also, the link you gave me, give examples of creating tables. Did not find anything about creating database. Maybe I am new so don't know the exact command/syntax.
@Garfield The second link is to the CREATE DATABASE command syntax. It's an ordinary SQL command like any other, so you can use it in a PQexec call. You must connect to a database like template1 to issue the CREATE DATABASE then disconnect and make a new connection to your new database to work with it.
@Garfield As for your design: If the only need to exist for a single application session, use CREATE TEMPORARY TABLE so they're auto dropped on disconnect. You might also find PostgreSQL 9.1's UNLOGGED table feature interesting if the tables contain data that you don't mind losing if something goes wrong. In general creating tables and databases on the fly suggests you might not be using the right tool for the job, though.
Rather than creating new databases, I suggest you create schema instead, so you can keep using the same database. Much less hassle. See the documentation
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.