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.
>From 7971893868e6eedc7229d28442f07890f251c42b Mon Sep 17 00:00:00 2001
From: Japin Li <japi...@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
