CODEWITHSUNDEEP
databasepostgresql
Haseena
Haseena

Reputation: 494

Error in create database postgresql

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.

Upvotes: 0

Views: 3135

Answers (3)

Dulangi_Kanchana
Dulangi_Kanchana

Reputation: 1233

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.

enter image description here

As shown by the yellow box, the databases created in pgadmin, by me, can try this

Upvotes: 1

Haseena
Haseena

Reputation: 494

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

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324375

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

Upvotes: 3

Related Questions