current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

Is any way to check a database is already exists or not in PostgreSQL?

Iam a new one in PostgreSQL. And also need to check it from my java application via jdbc driver.

share|improve this question
3  
Geez. Is it really so hard to check the manual before posting such a question? –  a_horse_with_no_name Nov 15 '12 at 11:40
    
@a_horse_with_no_name ... or just run \h CREATE DATABASE in psql - no need to even open the manual. –  Craig Ringer Nov 15 '12 at 11:42

2 Answers 2

up vote 3 down vote

There is no IF NOT EXISTS option for CREATE DATABASE in PostgreSQL. See CREATE DATABASE.

You can check pg_database to see if the DB exists, and only attempt to create it if it does not.

You would need to do this via dblink, because of the limitation Frank points out in the comments:

regress=> SELECT create_database_if_not_exists('test');
ERROR:  CREATE DATABASE cannot be executed from a function or multi-command string
CONTEXT:  SQL statement "CREATE DATABASE test"
PL/pgSQL function create_database_if_not_exists(text) line 6 at EXECUTE statement
share|improve this answer
    
This is what I get on 9.1: "CREATE DATABASE cannot be executed from a function or multi-command string" –  Frank Heikens Nov 15 '12 at 12:51
    
@FrankHeikens Teach me for not testing all the cases; I checked the function syntax with an existing DB but didn't actually create one. –  Craig Ringer Nov 15 '12 at 22:29
up vote 0 down vote accepted

I found an alternate solution for this problem. By using the following query :

       ResultSet res = st.executeQuery("select count(*) from pg_catalog.pg_database where datname = 'sample'") ;
       res.next();
       int count = res.getInt("count");
       System.out.println("Count : " + count);
       if(count == 0) {
         st.executeUpdate("CREATE DATABASE sample");
       }

Its work fine

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.