CREATE DATABASE IF NOT EXISTS in PostgreSQL
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t45603psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 03:12 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t45603_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t45603_1 && git checkout t45603_1Patchset v1 (message #1) is on t45603_1
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.