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.
Attachments:
v1-0001-Add-CREATE-DATABASE-IF-NOT-EXISTS-syntax.patchtext/x-patchDownload
From 7971893868e6eedc7229d28442f07890f251c42b Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Sun, 27 Feb 2022 22:02:59 +0800
Subject: [PATCH v1] Add CREATE DATABASE IF NOT EXISTS syntax
---
doc/src/sgml/ref/create_database.sgml | 2 +-
src/backend/commands/dbcommands.c | 19 +++++++++++++++----
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/parser/gram.y | 9 +++++++++
src/include/nodes/parsenodes.h | 1 +
6 files changed, 28 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
index f70d0c75b4..74af9c586e 100644
--- a/doc/src/sgml/ref/create_database.sgml
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE DATABASE <replaceable class="parameter">name</replaceable>
+CREATE DATABASE <replaceable class="parameter">name</replaceable> [ IF NOT EXISTS ]
[ [ WITH ] [ OWNER [=] <replaceable class="parameter">user_name</replaceable> ]
[ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ]
[ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ]
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index c37e3c9a9a..f3e5e930f9 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -557,10 +557,21 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
* message than "unique index violation". There's a race condition but
* we're willing to accept the less friendly message in that case.
*/
- if (OidIsValid(get_database_oid(dbname, true)))
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_DATABASE),
- errmsg("database \"%s\" already exists", dbname)));
+ {
+ Oid check_dboid = get_database_oid(dbname, true);
+ if (OidIsValid(check_dboid))
+ {
+ if (!stmt->if_not_exists)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_DATABASE),
+ errmsg("database \"%s\" already exists", dbname)));
+
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_DATABASE),
+ errmsg("database \"%s\" already exists, skipping", dbname)));
+ return check_dboid;
+ }
+ }
/*
* The source DB can't have any active backends, except this one
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index d4f8455a2b..83ead22931 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4045,6 +4045,7 @@ _copyCreatedbStmt(const CreatedbStmt *from)
COPY_STRING_FIELD(dbname);
COPY_NODE_FIELD(options);
+ COPY_SCALAR_FIELD(if_not_exists);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index f1002afe7a..f9a89fa4a8 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1722,6 +1722,7 @@ _equalCreatedbStmt(const CreatedbStmt *a, const CreatedbStmt *b)
{
COMPARE_STRING_FIELD(dbname);
COMPARE_NODE_FIELD(options);
+ COMPARE_SCALAR_FIELD(if_not_exists);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a03b33b53b..72e4f642d9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10395,6 +10395,15 @@ CreatedbStmt:
CreatedbStmt *n = makeNode(CreatedbStmt);
n->dbname = $3;
n->options = $5;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE DATABASE IF_P NOT EXISTS name opt_with createdb_opt_list
+ {
+ CreatedbStmt *n = makeNode(CreatedbStmt);
+ n->dbname = $6;
+ n->options = $8;
+ n->if_not_exists = true;
$$ = (Node *)n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1617702d9d..71c828ac4d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3295,6 +3295,7 @@ typedef struct CreatedbStmt
NodeTag type;
char *dbname; /* name of database to create */
List *options; /* List of DefElem nodes */
+ bool if_not_exists; /* just do nothing if it already exists? */
} CreatedbStmt;
/* ----------------------
--
2.25.1
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.