Thread: CREATE DATABASE IF NOT EXISTS in PostgreSQL

Hi, hackers

When I try to use CREATE DATABASE IF NOT EXISTS in PostgreSQL, it complains
this syntax is not supported.  We can use the following command to achieve
this, however, it's not straightforward.

     SELECT 'CREATE DATABASE mydb'
     WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec

Why don't support CREATE DATABASE IF NOT EXISTS syntax in PostgreSQL?

I create a patch for this, any suggestions?

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

Japin Li <japinli@hotmail.com> writes:
> Why don't support CREATE DATABASE IF NOT EXISTS syntax in PostgreSQL?

FWIW, I'm generally hostile to CREATE IF NOT EXISTS semantics across
the board, because of its exceedingly squishy semantics: it ensures
that an object by that name exists, but you have exactly no guarantees
about its properties or contents.  The more complex the object, the
bigger that problem becomes ... and a whole database is the most
complex sort of object we have.  So IMV, the fact that we don't have
this "feature" is a good thing.

We do have DROP DATABASE IF EXISTS, and I think using that followed
by CREATE is a much better-defined approach.

            regards, tom lane

On Mon, 28 Feb 2022 at 01:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Japin Li <japinli@hotmail.com> writes:
>> Why don't support CREATE DATABASE IF NOT EXISTS syntax in PostgreSQL?
>
> FWIW, I'm generally hostile to CREATE IF NOT EXISTS semantics across
> the board, because of its exceedingly squishy semantics: it ensures
> that an object by that name exists, but you have exactly no guarantees
> about its properties or contents.

Thanks for the explanation!  I think it is the database user who should
guarantee the properties and contents of a database.
CREATE IF NOT EXISTS is just a syntax sugar.

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.