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.
0
Add a comment
|
3 Answers
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
2 Comments
Frank Heikens
This is what I get on 9.1: "CREATE DATABASE cannot be executed from a function or multi-command string"
Craig Ringer
@FrankHeikens Teach me for not testing all the cases; I checked the function syntax with an existing DB but didn't actually create one.
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
Comments
Provided you do not have many databases made in postgreSQL
Here is a simple way to see what databases you have and do not have
Can use pgadmin ,and expand database column and see what databases you have and dont have.
As shown by the yellow box, the databases created in pgadmin, by me, can try this
