diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index 930d876..0ffe8b1 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -22,7 +22,9 @@ PostgreSQL documentation
CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]
+CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ]
CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ]
+CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name
@@ -62,6 +64,17 @@ CREATE SCHEMA AUTHORIZATION user_name
+ IF NOT EXISTS
+
+
+ Do not throw an error if a schema with the same name already exists.
+ A notice is issued in this case, but an error is threw if used with
+ schema_element.
+
+
+
+
+
schema_name
@@ -95,6 +108,7 @@ CREATE SCHEMA AUTHORIZATION user_name and GRANT are accepted as clauses
within CREATE SCHEMA. Other kinds of objects may
be created in separate commands after the schema is created.
+ Throw an error if used with IF NOT EXISTS.
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index ec8aa17..8a62341 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1376,6 +1376,7 @@ CreateExtension(CreateExtensionStmt *stmt)
csstmt->schemaname = schemaName;
csstmt->authid = NULL; /* will be created by current user */
csstmt->schemaElts = NIL;
+ csstmt->if_not_exists = false;
CreateSchemaCommand(csstmt, NULL);
/*
diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c
index cd5ce06..b01c571 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -51,6 +51,7 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString)
Oid saved_uid;
int save_sec_context;
AclResult aclresult;
+ bool if_not_exists = stmt->if_not_exists;
GetUserIdAndSecContext(&saved_uid, &save_sec_context);
@@ -95,12 +96,21 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString)
SetUserIdAndSecContext(owner_uid,
save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
- /* Create the schema's namespace */
- namespaceId = NamespaceCreate(schemaName, owner_uid, false);
-
- /* Advance cmd counter to make the namespace visible */
- CommandCounterIncrement();
-
+ /* If schema already exists, skip */
+ namespaceId = get_namespace_oid(schemaName, true);
+ if (OidIsValid(namespaceId) && if_not_exists) {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_SCHEMA),
+ errmsg("schema \"%s\" already exists, skipping",
+ schemaName)));
+ } else {
+
+ /* Create the schema's namespace */
+ namespaceId = NamespaceCreate(schemaName, owner_uid, false);
+
+ /* Advance cmd counter to make the namespace visible */
+ CommandCounterIncrement();
+ }
/*
* Temporarily make the new namespace be the front of the search path, as
* well as the default creation target namespace. This will be undone at
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 139b1bd..c07beae 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3614,6 +3614,7 @@ _copyCreateSchemaStmt(const CreateSchemaStmt *from)
COPY_STRING_FIELD(schemaname);
COPY_STRING_FIELD(authid);
COPY_NODE_FIELD(schemaElts);
+ COPY_SCALAR_FIELD(if_not_exists);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index cebd030..bf7d90e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1910,6 +1910,7 @@ _equalCreateSchemaStmt(const CreateSchemaStmt *a, const CreateSchemaStmt *b)
COMPARE_STRING_FIELD(schemaname);
COMPARE_STRING_FIELD(authid);
COMPARE_NODE_FIELD(schemaElts);
+ COMPARE_SCALAR_FIELD(if_not_exists);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0d3a20d..77382b1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1169,8 +1169,28 @@ CreateSchemaStmt:
n->schemaname = $5;
n->authid = $5;
n->schemaElts = $6;
+ n->if_not_exists = false;
$$ = (Node *)n;
}
+ | CREATE SCHEMA IF_P NOT EXISTS OptSchemaName AUTHORIZATION RoleId OptSchemaEltList
+ {
+ CreateSchemaStmt *n = makeNode(CreateSchemaStmt);
+ /* One can omit the schema name or the authorization id. */
+ if ($6 != NULL)
+ n->schemaname = $6;
+ else
+ n->schemaname = $8;
+ n->authid = $8;
+ if ($9 != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("IF NOT EXISTS cannot be used with schema elements"),
+ parser_errposition(@3)));
+ n->schemaElts = $9;
+ n->if_not_exists = true;
+ $$ = (Node *)n;
+ }
+
| CREATE SCHEMA ColId OptSchemaEltList
{
CreateSchemaStmt *n = makeNode(CreateSchemaStmt);
@@ -1178,8 +1198,25 @@ CreateSchemaStmt:
n->schemaname = $3;
n->authid = NULL;
n->schemaElts = $4;
+ n->if_not_exists = false;
$$ = (Node *)n;
}
+ | CREATE SCHEMA IF_P NOT EXISTS ColId OptSchemaEltList
+ {
+ CreateSchemaStmt *n = makeNode(CreateSchemaStmt);
+ /* ...but not both */
+ n->schemaname = $6;
+ n->authid = NULL;
+ if ($7 != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("IF NOT EXISTS cannot be used with schema elements"),
+ parser_errposition(@3)));
+ n->schemaElts = $7;
+ n->if_not_exists = true;
+ $$ = (Node *)n;
+ }
+
;
OptSchemaName:
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4fe644e..fbf778c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1157,6 +1157,7 @@ typedef struct CreateSchemaStmt
char *schemaname; /* the name of the schema to create */
char *authid; /* the owner of the created schema */
List *schemaElts; /* schema components (list of parsenodes) */
+ bool if_not_exists; /* just do nothing if schema already exists? */
} CreateSchemaStmt;
typedef enum DropBehavior
diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out
index 7c26da5..21514ee 100644
--- a/src/test/regress/expected/namespace.out
+++ b/src/test/regress/expected/namespace.out
@@ -9,6 +9,19 @@ CREATE SCHEMA test_schema_1
a serial,
b int UNIQUE
);
+-- verify if schema already exists
+CREATE SCHEMA test_schema_1;
+ERROR: schema "test_schema_1" already exists
+CREATE SCHEMA IF NOT EXISTS test_schema_1;
+NOTICE: schema "test_schema_1" already exists, skipping
+CREATE SCHEMA IF NOT EXISTS test_schema_1
+ CREATE TABLE abc (
+ a serial,
+ b int UNIQUE
+ );
+ERROR: IF NOT EXISTS cannot be used with schema elements
+LINE 1: CREATE SCHEMA IF NOT EXISTS test_schema_1
+ ^
-- verify that the objects were created
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
diff --git a/src/test/regress/sql/namespace.sql b/src/test/regress/sql/namespace.sql
index 919f72a..98f2901 100644
--- a/src/test/regress/sql/namespace.sql
+++ b/src/test/regress/sql/namespace.sql
@@ -13,6 +13,15 @@ CREATE SCHEMA test_schema_1
b int UNIQUE
);
+-- verify if schema already exists
+CREATE SCHEMA test_schema_1;
+CREATE SCHEMA IF NOT EXISTS test_schema_1;
+CREATE SCHEMA IF NOT EXISTS test_schema_1
+ CREATE TABLE abc (
+ a serial,
+ b int UNIQUE
+ );
+
-- verify that the objects were created
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');