aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/commands/createas.c53
-rw-r--r--src/backend/commands/explain.c16
-rw-r--r--src/include/commands/createas.h2
-rw-r--r--src/test/regress/expected/matview.out38
-rw-r--r--src/test/regress/expected/select_into.out42
-rw-r--r--src/test/regress/sql/matview.sql23
-rw-r--r--src/test/regress/sql/select_into.sql21
7 files changed, 180 insertions, 15 deletions
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 6bf6c5a3106..009896bcee7 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -239,21 +239,9 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
PlannedStmt *plan;
QueryDesc *queryDesc;
- if (stmt->if_not_exists)
- {
- Oid nspid;
-
- nspid = RangeVarGetCreationNamespace(stmt->into->rel);
-
- if (get_relname_relid(stmt->into->rel->relname, nspid))
- {
- ereport(NOTICE,
- (errcode(ERRCODE_DUPLICATE_TABLE),
- errmsg("relation \"%s\" already exists, skipping",
- stmt->into->rel->relname)));
- return InvalidObjectAddress;
- }
- }
+ /* Check if the relation exists or not */
+ if (CreateTableAsRelExists(stmt))
+ return InvalidObjectAddress;
/*
* Create the tuple receiver object and insert info it will need
@@ -401,6 +389,41 @@ GetIntoRelEFlags(IntoClause *intoClause)
}
/*
+ * CreateTableAsRelExists --- check existence of relation for CreateTableAsStmt
+ *
+ * Utility wrapper checking if the relation pending for creation in this
+ * CreateTableAsStmt query already exists or not. Returns true if the
+ * relation exists, otherwise false.
+ */
+bool
+CreateTableAsRelExists(CreateTableAsStmt *ctas)
+{
+ Oid nspid;
+ IntoClause *into = ctas->into;
+
+ nspid = RangeVarGetCreationNamespace(into->rel);
+
+ if (get_relname_relid(into->rel->relname, nspid))
+ {
+ if (!ctas->if_not_exists)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already exists",
+ into->rel->relname)));
+
+ /* The relation exists and IF NOT EXISTS has been specified */
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already exists, skipping",
+ into->rel->relname)));
+ return true;
+ }
+
+ /* Relation does not exist, it can be created */
+ return false;
+}
+
+/*
* CreateIntoRelDestReceiver -- create a suitable DestReceiver object
*
* intoClause will be NULL if called from CreateDestReceiver(), in which
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 43f9b01e833..d797b5f53e0 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -435,6 +435,22 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es,
CreateTableAsStmt *ctas = (CreateTableAsStmt *) utilityStmt;
List *rewritten;
+ /*
+ * Check if the relation exists or not. This is done at this stage to
+ * avoid query planning or execution.
+ */
+ if (CreateTableAsRelExists(ctas))
+ {
+ if (ctas->objtype == OBJECT_TABLE)
+ ExplainDummyGroup("CREATE TABLE AS", NULL, es);
+ else if (ctas->objtype == OBJECT_MATVIEW)
+ ExplainDummyGroup("CREATE MATERIALIZED VIEW", NULL, es);
+ else
+ elog(ERROR, "unexpected object type: %d",
+ (int) ctas->objtype);
+ return;
+ }
+
rewritten = QueryRewrite(castNode(Query, copyObject(ctas->query)));
Assert(list_length(rewritten) == 1);
ExplainOneQuery(linitial_node(Query, rewritten),
diff --git a/src/include/commands/createas.h b/src/include/commands/createas.h
index 76292302542..d8cfb625228 100644
--- a/src/include/commands/createas.h
+++ b/src/include/commands/createas.h
@@ -29,4 +29,6 @@ extern int GetIntoRelEFlags(IntoClause *intoClause);
extern DestReceiver *CreateIntoRelDestReceiver(IntoClause *intoClause);
+extern bool CreateTableAsRelExists(CreateTableAsStmt *ctas);
+
#endif /* CREATEAS_H */
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 2c0760404d3..4b3a2e0cb73 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -630,3 +630,41 @@ drop cascades to materialized view matview_schema.mv_withdata2
drop cascades to materialized view matview_schema.mv_nodata1
drop cascades to materialized view matview_schema.mv_nodata2
DROP USER regress_matview_user;
+-- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
+CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1;
+CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error
+ERROR: relation "matview_ine_tab" already exists
+CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0; -- ok
+NOTICE: relation "matview_ine_tab" already exists, skipping
+CREATE MATERIALIZED VIEW matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- error
+ERROR: relation "matview_ine_tab" already exists
+CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- ok
+NOTICE: relation "matview_ine_tab" already exists, skipping
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW matview_ine_tab AS
+ SELECT 1 / 0; -- error
+ERROR: relation "matview_ine_tab" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0; -- ok
+NOTICE: relation "matview_ine_tab" already exists, skipping
+ QUERY PLAN
+------------
+(0 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- error
+ERROR: relation "matview_ine_tab" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- ok
+NOTICE: relation "matview_ine_tab" already exists, skipping
+ QUERY PLAN
+------------
+(0 rows)
+
+DROP MATERIALIZED VIEW matview_ine_tab;
diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out
index bf5c6bea045..43b8209d229 100644
--- a/src/test/regress/expected/select_into.out
+++ b/src/test/regress/expected/select_into.out
@@ -178,3 +178,45 @@ INSERT INTO b SELECT 1 INTO f;
ERROR: SELECT ... INTO is not allowed here
LINE 1: INSERT INTO b SELECT 1 INTO f;
^
+-- Test CREATE TABLE AS ... IF NOT EXISTS
+CREATE TABLE ctas_ine_tbl AS SELECT 1;
+CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
+ERROR: relation "ctas_ine_tbl" already exists
+CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
+NOTICE: relation "ctas_ine_tbl" already exists, skipping
+CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
+ERROR: relation "ctas_ine_tbl" already exists
+CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
+NOTICE: relation "ctas_ine_tbl" already exists, skipping
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
+ERROR: relation "ctas_ine_tbl" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
+NOTICE: relation "ctas_ine_tbl" already exists, skipping
+ QUERY PLAN
+------------
+(0 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
+ERROR: relation "ctas_ine_tbl" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
+NOTICE: relation "ctas_ine_tbl" already exists, skipping
+ QUERY PLAN
+------------
+(0 rows)
+
+PREPARE ctas_ine_query AS SELECT 1 / 0;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
+ERROR: relation "ctas_ine_tbl" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
+NOTICE: relation "ctas_ine_tbl" already exists, skipping
+ QUERY PLAN
+------------
+(0 rows)
+
+DROP TABLE ctas_ine_tbl;
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 70c4954d89a..4a4bd0d6b61 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -264,3 +264,26 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
DROP SCHEMA matview_schema CASCADE;
DROP USER regress_matview_user;
+
+-- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
+CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1;
+CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error
+CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0; -- ok
+CREATE MATERIALIZED VIEW matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- error
+CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- ok
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW matview_ine_tab AS
+ SELECT 1 / 0; -- error
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0; -- ok
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- error
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ SELECT 1 / 0 WITH NO DATA; -- ok
+DROP MATERIALIZED VIEW matview_ine_tab;
diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql
index 6c170ef9688..7e903c339a8 100644
--- a/src/test/regress/sql/select_into.sql
+++ b/src/test/regress/sql/select_into.sql
@@ -115,3 +115,24 @@ COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob';
SELECT * FROM (SELECT 1 INTO f) bar;
CREATE VIEW foo AS SELECT 1 INTO b;
INSERT INTO b SELECT 1 INTO f;
+
+-- Test CREATE TABLE AS ... IF NOT EXISTS
+CREATE TABLE ctas_ine_tbl AS SELECT 1;
+CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
+CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
+CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
+CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
+PREPARE ctas_ine_query AS SELECT 1 / 0;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
+DROP TABLE ctas_ine_tbl;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-20 14:20:39 +0000