diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 240ab93..7b77cb5 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [ +CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ like_option ... ] } @@ -32,7 +32,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE tablespace ] -CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name +CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] [ IF NOT EXISTS ] TABLE table_name OF type_name [ ( { column_name WITH OPTIONS [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint } @@ -164,6 +164,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 387d43e..1da8fb6 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -245,7 +245,8 @@ Boot_CreateStmt: ONCOMMIT_NOOP, (Datum) 0, false, - true); + true, + false); elog(DEBUG4, "relation created with oid %u", id); } do_end(); diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index d848ef0..20d78a2 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -903,11 +903,13 @@ heap_create_with_catalog(const char *relname, OnCommitAction oncommit, Datum reloptions, bool use_user_acl, - bool allow_system_table_mods) + bool allow_system_table_mods, + bool if_not_exists) { Relation pg_class_desc; Relation new_rel_desc; Acl *relacl; + Oid existing_relid; Oid old_type_oid; Oid new_type_oid; Oid new_array_oid = InvalidOid; @@ -921,10 +923,27 @@ heap_create_with_catalog(const char *relname, CheckAttributeNamesTypes(tupdesc, relkind, allow_system_table_mods); - if (get_relname_relid(relname, relnamespace)) + /* + * If the relation already exists, it's an error, unless the user specifies + * "IF NOT EXISTS". In that case, we just print a notice and do nothing + * further. + */ + existing_relid = get_relname_relid(relname, relnamespace); + if (existing_relid != InvalidOid) + { + if (if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", + relname))); + heap_close(pg_class_desc, RowExclusiveLock); + return InvalidOid; + } ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", relname))); + } /* * Since we are going to create a rowtype as well, also check for diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 435dfdd..52eef7c 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -223,7 +223,9 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio ONCOMMIT_NOOP, reloptions, false, - true); + true, + false); + Assert(toast_relid != InvalidOid); /* make the toast relation visible, else index creation will fail */ CommandCounterIncrement(); diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index ccb4599..516dbd2 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -687,7 +687,9 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace) ONCOMMIT_NOOP, reloptions, false, - true); + true, + false); + Assert(OIDNewHeap != InvalidOid); ReleaseSysCache(tuple); diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index f52e1d8..66b67dd 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -203,8 +203,10 @@ DefineSequence(CreateSeqStmt *seq) stmt->options = list_make1(defWithOids(false)); stmt->oncommit = ONCOMMIT_NOOP; stmt->tablespacename = NULL; + stmt->if_not_exists = false; seqoid = DefineRelation(stmt, RELKIND_SEQUENCE); + Assert(seqoid != InvalidOid); rel = heap_open(seqoid, AccessExclusiveLock); tupDesc = RelationGetDescr(rel); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 9b5ce65..39d76f0 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -546,8 +546,18 @@ DefineRelation(CreateStmt *stmt, char relkind) stmt->oncommit, reloptions, true, - allowSystemTableMods); + allowSystemTableMods, + stmt->if_not_exists); + /* + * If heap_create_with_catalog returns InvalidOid, it means that the user + * specified "IF NOT EXISTS" and the relation already exists. In that + * case we do nothing further. + */ + if (relationId == InvalidOid) + return InvalidOid; + + /* Store inheritance information for new rel. */ StoreCatalogInheritance(relationId, inheritOids); /* diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 1e14dca..fec73c6 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1506,6 +1506,7 @@ DefineCompositeType(const RangeVar *typevar, List *coldeflist) CreateStmt *createStmt = makeNode(CreateStmt); Oid old_type_oid; Oid typeNamespace; + Oid relid; if (coldeflist == NIL) ereport(ERROR, @@ -1523,6 +1524,7 @@ DefineCompositeType(const RangeVar *typevar, List *coldeflist) createStmt->options = list_make1(defWithOids(false)); createStmt->oncommit = ONCOMMIT_NOOP; createStmt->tablespacename = NULL; + createStmt->if_not_exists = false; /* * Check for collision with an existing type name. If there is one and @@ -1546,7 +1548,9 @@ DefineCompositeType(const RangeVar *typevar, List *coldeflist) /* * Finally create the relation. This also creates the type. */ - return DefineRelation(createStmt, RELKIND_COMPOSITE_TYPE); + relid = DefineRelation(createStmt, RELKIND_COMPOSITE_TYPE); + Assert(relid != InvalidOid); + return relid; } /* diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index d7a06bc..67c90a2 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -222,6 +222,8 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace) } else { + Oid relid; + /* * now set the parameters for keys/inheritance etc. All of these are * uninteresting for views... @@ -233,13 +235,16 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace) createStmt->options = list_make1(defWithOids(false)); createStmt->oncommit = ONCOMMIT_NOOP; createStmt->tablespacename = NULL; + createStmt->if_not_exists = false; /* * finally create the relation (this will error out if there's an * existing view, so we don't need more code to complain if "replace" * is false). */ - return DefineRelation(createStmt, RELKIND_VIEW); + relid = DefineRelation(createStmt, RELKIND_VIEW); + Assert(relid != InvalidOid); + return relid; } } diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index d299310..c28cf37 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -2174,7 +2174,9 @@ OpenIntoRel(QueryDesc *queryDesc) into->onCommit, reloptions, true, - allowSystemTableMods); + allowSystemTableMods, + false); + Assert(intoRelationId != InvalidOid); FreeTupleDesc(tupdesc); diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index e770e89..e8ed79d 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2518,6 +2518,7 @@ _copyCreateStmt(CreateStmt *from) COPY_NODE_FIELD(options); COPY_SCALAR_FIELD(oncommit); COPY_STRING_FIELD(tablespacename); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 5d83727..70b3c62 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1104,6 +1104,7 @@ _equalCreateStmt(CreateStmt *a, CreateStmt *b) COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(oncommit); COMPARE_STRING_FIELD(tablespacename); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index e7dae4b..e745201 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -1782,6 +1782,7 @@ _outCreateStmt(StringInfo str, CreateStmt *node) WRITE_NODE_FIELD(options); WRITE_ENUM_FIELD(oncommit, OnCommitAction); WRITE_STRING_FIELD(tablespacename); + WRITE_BOOL_FIELD(if_not_exists); } static void diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index b793c4d..69248af 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2212,6 +2212,23 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')' n->options = $9; n->oncommit = $10; n->tablespacename = $11; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE OptTemp TABLE IF_P NOT EXISTS qualified_name '(' + OptTableElementList ')' OptInherit OptWith OnCommitOption + OptTableSpace + { + CreateStmt *n = makeNode(CreateStmt); + $7->istemp = $2; + n->relation = $7; + n->tableElts = $9; + n->inhRelations = $11; + n->constraints = NIL; + n->options = $12; + n->oncommit = $13; + n->tablespacename = $14; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE OptTemp TABLE qualified_name OF any_name @@ -2227,6 +2244,22 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')' n->options = $8; n->oncommit = $9; n->tablespacename = $10; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE OptTemp TABLE IF_P NOT EXISTS qualified_name OF any_name + OptTypedTableElementList OptWith OnCommitOption OptTableSpace + { + CreateStmt *n = makeNode(CreateStmt); + n->relation = $7; + n->tableElts = $10; + n->ofTypename = makeTypeNameFromNameList($9); + n->ofTypename->location = @9; + n->constraints = NIL; + n->options = $11; + n->oncommit = $12; + n->tablespacename = $13; + n->if_not_exists = true; $$ = (Node *)n; } ; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 8960246..1815539 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -513,6 +513,13 @@ standard_ProcessUtility(Node *parsetree, RELKIND_RELATION); /* + * If "IF NOT EXISTS" was specified and the relation + * already exists, do nothing further. + */ + if (relOid == InvalidOid) + continue; + + /* * Let AlterTableCreateToastTable decide if this one * needs a secondary relation too. */ diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 8292273..07db4a3 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -61,7 +61,8 @@ extern Oid heap_create_with_catalog(const char *relname, OnCommitAction oncommit, Datum reloptions, bool use_user_acl, - bool allow_system_table_mods); + bool allow_system_table_mods, + bool if_not_exists); extern void heap_drop_with_catalog(Oid relid); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b591073..fec8d3c 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1375,6 +1375,7 @@ typedef struct CreateStmt List *options; /* options from WITH clause */ OnCommitAction oncommit; /* what do we do at COMMIT? */ char *tablespacename; /* table space to use, or NULL */ + bool if_not_exists; /* just do nothing if it already exists? */ } CreateStmt; /* ---------- diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index daecabb..6f65885 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -196,7 +196,11 @@ CREATE TABLE array_index_op_test ( i int4[], t text[] ); -CREATE TABLE test_tsvector( +CREATE TABLE IF NOT EXISTS test_tsvector( t text, a tsvector ); +CREATE TABLE IF NOT EXISTS test_tsvector( + t text +); +NOTICE: relation "test_tsvector" already exists, skipping diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out index e92cdf6..c314f72 100644 --- a/src/test/regress/expected/typed_table.out +++ b/src/test/regress/expected/typed_table.out @@ -2,6 +2,8 @@ CREATE TABLE ttable1 OF nothing; ERROR: type "nothing" does not exist CREATE TYPE person_type AS (id int, name text); CREATE TABLE persons OF person_type; +CREATE TABLE IF NOT EXISTS persons OF person_type; +NOTICE: relation "persons" already exists, skipping SELECT * FROM persons; id | name ----+------ diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index e43371e..f491e8c 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -232,8 +232,11 @@ CREATE TABLE array_index_op_test ( t text[] ); -CREATE TABLE test_tsvector( +CREATE TABLE IF NOT EXISTS test_tsvector( t text, a tsvector ); +CREATE TABLE IF NOT EXISTS test_tsvector( + t text +); diff --git a/src/test/regress/sql/typed_table.sql b/src/test/regress/sql/typed_table.sql index 4e81f1d..4eb92d7 100644 --- a/src/test/regress/sql/typed_table.sql +++ b/src/test/regress/sql/typed_table.sql @@ -2,6 +2,7 @@ CREATE TABLE ttable1 OF nothing; CREATE TYPE person_type AS (id int, name text); CREATE TABLE persons OF person_type; +CREATE TABLE IF NOT EXISTS persons OF person_type; SELECT * FROM persons; \d persons