create database test if not exists

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

To create database you'll use the CREATE DATABASE command:
(this part is well documented in Postgres manual)
Let me know if you have more questions.

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...

is it possible to do this without using a pl-function?

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:
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?

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?

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 ;)

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...

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