By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

create database test if not exists

P: 9
create database test if not exists. Someone know how to do this in postgreSQL?
Dec 15 '06 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 534
I think the least known part here is finding out whether database exists.
Here you can try something like this:

Expand|Select|Wrap|Line Numbers
  1. select count(*) from pg_catalog.pg_database where datname = 'test' ;
To create database you'll use the CREATE DATABASE command:
Expand|Select|Wrap|Line Numbers
  1. CREATE DATABASE name
  2.     [ [ WITH ] [ OWNER [=] dbowner ]
  3.            [ LOCATION [=] 'dbpath' ]
  4.            [ TEMPLATE [=] template ]
  5.            [ ENCODING [=] encoding ] ]
  6.  
(this part is well documented in Postgres manual)
Let me know if you have more questions.
Dec 17 '06 #2

P: 9
Thank you. The problem is how to use "if" or "case" before create database... i've created a plpgsql function, but this seems to only work from the command prompt and not trough jdbc...

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION createdbtest() RETURNS void AS $t$
  2. BEGIN
  3.     SELECT datname FROM pg_database WHERE datname='test'';
  4.     IF datname='test' 
  5.         THEN CREATE DATABASE test PASSWORD 'test';
  6.     END IF;
  7. END;
  8. $t$ LANGUAGE plpgsql;
  9.  
is it possible to do this without using a pl-function?
Dec 18 '06 #3

P: 9
forget the last post. I almost found a solution using a pl/pgsql function. but i get this error: "ERROR: CREATE DATABASE
cannot be executed from a function"...

It works fine when i try to create a new role (only when it does not exists) though.

here is the code:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION createusertest() RETURNS void AS '
  2. DECLARE user_rec RECORD;
  3. BEGIN
  4. select into user_rec * FROM pg_user WHERE usename=''test''; 
  5. IF user_rec.usename IS NULL THEN CREATE USER test PASSWORD ''test'';
  6. END IF;
  7. END;
  8. ' LANGUAGE plpgsql;
  9.  
the big question is: how can i create a new database when it does not exists and at the same time not getting an error when it exists?
Dec 18 '06 #4

Expert 100+
P: 534
Do you really have to employ the function here, or to be more to the point do you HAVE to run it all in a single shot?

I assume you have some backend process, which gets the database name from GUI, or some other source. At this point you can run the SQL I posted earlier to find out whether the database with given name exists.
If it does, issue the appropriate warning, or error message.
If not run the sql to create database.

The only possible catch here ir a race condition which may take place when different processes would attempt to check on the same name and create database at the same time... but this can (probably) happen even if you pack your code in a single module.

Am I missing something?
Dec 18 '06 #5

P: 9
Sorry for not being very specific. We have a sql ant task, only used in development, where we set up the database. This task shall run both when the database does exist and when it does not. So we canít do something like if(not exists){ create database}, because we call a ďpureĒ sql script from the ant task. We have to do this in sql or pl/pgsql or something I guess. But it seems like I canít create a database from inside a function, and I canít use (sql) CASE either, so Iím kind of lostÖ

It was so easy in MySQL :(, but anyway I still like postgres better ;)
Dec 18 '06 #6

P: 9
well, i found a not very elegant solution. Set onerror="continue" in the ant sql-task. it still generates error when the database exists offcourse, but it works ok i guess...
Dec 19 '06 #7

Expert 100+
P: 534
Optionally you can try to run a shell script in this task.
This is not a very elegant solution either :(
I don't really like it, but perhaps its worth trying.
Here's the basic idea
Expand|Select|Wrap|Line Numbers
  1. #!/bin/sh
  2. ########################################################
  3. # - create a test database if it does not exist.
  4. # if necessary some variables may be passed
  5. # with arguments; db password, path to psql,
  6. # name of the database, db owner... anything else
  7. #-----------------------------------------------------
  8.  
  9. # if we get the db password, we put it in our temp
  10. # env., otherwise psql may pause and prompt for it.
  11. if [ "$1" ] ; then
  12.    export PGPASSWORD="$1"
  13. fi
  14.  
  15. # may check the args to see if dbname
  16. # was provided
  17. dbname=test
  18.  
  19. # sql to check whether given database exist
  20. sql1="select count(1) from pg_catalog.pg_database where datname = '$dbname'"
  21.  
  22. # sql to create database (add other params as needed)
  23. sql2="create database $dbname"
  24.  
  25. # depending on how PATH is set psql may require a fully qualified path
  26. cmd="psql -t -c \"$sql1\""
  27.  
  28. db_exists=`eval $cmd`
  29.  
  30. if [ $db_exists -eq 0 ] ; then
  31.    # create the database, discard the output
  32.    cmd="psql -t -c \"$sql2\" > /dev/null 2>&1"
  33.    eval $cmd
  34. fi
  35.  
  36. # exit with success status
  37. exit 0
  38.  
  39.  
Dec 19 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.