Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 08309aa

Browse filesBrowse files
MasaoFujiiMasaoFujii
committed
Implement IF NOT EXIST for CREATE INDEX.
Fabrízio de Royes Mello, reviewed by Marti Raudsepp, Adam Brightwell and me.
1 parent 171c377 commit 08309aa
Copy full SHA for 08309aa

File tree

Expand file treeCollapse file tree

11 files changed

+85
-5
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

11 files changed

+85
-5
lines changed
Open diff view settings
Collapse file

‎doc/src/sgml/ref/create_index.sgml‎

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_index.sgml
+13-1Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
24+
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
2525
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
2626
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
2727
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -126,6 +126,18 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</
126126
</listitem>
127127
</varlistentry>
128128

129+
<varlistentry>
130+
<term><literal>IF NOT EXISTS</literal></term>
131+
<listitem>
132+
<para>
133+
Do not throw an error if a relation with the same name already exists.
134+
A notice is issued in this case. Note that there is no guarantee that
135+
the existing index is anything like the one that would have been created.
136+
Index name is required when <literal>IF NOT EXISTS</literal> is specified.
137+
</para>
138+
</listitem>
139+
</varlistentry>
140+
129141
<varlistentry>
130142
<term><replaceable class="parameter">name</replaceable></term>
131143
<listitem>
Collapse file

‎src/backend/catalog/index.c‎

Copy file name to clipboardExpand all lines: src/backend/catalog/index.c
+16-1Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -674,6 +674,8 @@ UpdateIndexRelation(Oid indexoid,
674674
* will be marked "invalid" and the caller must take additional steps
675675
* to fix it up.
676676
* is_internal: if true, post creation hook for new index
677+
* if_not_exists: if true, do not throw an error if a relation with
678+
* the same name already exists.
677679
*
678680
* Returns the OID of the created index.
679681
*/
@@ -697,7 +699,8 @@ index_create(Relation heapRelation,
697699
bool allow_system_table_mods,
698700
bool skip_build,
699701
bool concurrent,
700-
bool is_internal)
702+
bool is_internal,
703+
bool if_not_exists)
701704
{
702705
Oid heapRelationId = RelationGetRelid(heapRelation);
703706
Relation pg_class;
@@ -773,10 +776,22 @@ index_create(Relation heapRelation,
773776
elog(ERROR, "shared relations must be placed in pg_global tablespace");
774777

775778
if (get_relname_relid(indexRelationName, namespaceId))
779+
{
780+
if (if_not_exists)
781+
{
782+
ereport(NOTICE,
783+
(errcode(ERRCODE_DUPLICATE_TABLE),
784+
errmsg("relation \"%s\" already exists, skipping",
785+
indexRelationName)));
786+
heap_close(pg_class, RowExclusiveLock);
787+
return InvalidOid;
788+
}
789+
776790
ereport(ERROR,
777791
(errcode(ERRCODE_DUPLICATE_TABLE),
778792
errmsg("relation \"%s\" already exists",
779793
indexRelationName)));
794+
}
780795

781796
/*
782797
* construct tuple descriptor for index tuples
Collapse file

‎src/backend/catalog/toasting.c‎

Copy file name to clipboardExpand all lines: src/backend/catalog/toasting.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
342342
rel->rd_rel->reltablespace,
343343
collationObjectId, classObjectId, coloptions, (Datum) 0,
344344
true, false, false, false,
345-
true, false, false, true);
345+
true, false, false, true, false);
346346

347347
heap_close(toast_rel, NoLock);
348348

Collapse file

‎src/backend/commands/indexcmds.c‎

Copy file name to clipboardExpand all lines: src/backend/commands/indexcmds.c
+8-1Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -610,7 +610,14 @@ DefineIndex(Oid relationId,
610610
stmt->isconstraint, stmt->deferrable, stmt->initdeferred,
611611
allowSystemTableMods,
612612
skip_build || stmt->concurrent,
613-
stmt->concurrent, !check_rights);
613+
stmt->concurrent, !check_rights,
614+
stmt->if_not_exists);
615+
616+
if (!OidIsValid(indexRelationId))
617+
{
618+
heap_close(rel, NoLock);
619+
return indexRelationId;
620+
}
614621

615622
/* Add any requested comment */
616623
if (stmt->idxcomment != NULL)
Collapse file

‎src/backend/nodes/copyfuncs.c‎

Copy file name to clipboardExpand all lines: src/backend/nodes/copyfuncs.c
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2907,6 +2907,7 @@ _copyIndexStmt(const IndexStmt *from)
29072907
COPY_SCALAR_FIELD(deferrable);
29082908
COPY_SCALAR_FIELD(initdeferred);
29092909
COPY_SCALAR_FIELD(concurrent);
2910+
COPY_SCALAR_FIELD(if_not_exists);
29102911

29112912
return newnode;
29122913
}
Collapse file

‎src/backend/nodes/equalfuncs.c‎

Copy file name to clipboardExpand all lines: src/backend/nodes/equalfuncs.c
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1210,6 +1210,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
12101210
COMPARE_SCALAR_FIELD(deferrable);
12111211
COMPARE_SCALAR_FIELD(initdeferred);
12121212
COMPARE_SCALAR_FIELD(concurrent);
1213+
COMPARE_SCALAR_FIELD(if_not_exists);
12131214

12141215
return true;
12151216
}
Collapse file

‎src/backend/parser/gram.y‎

Copy file name to clipboardExpand all lines: src/backend/parser/gram.y
+26Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6434,6 +6434,32 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
64346434
n->isconstraint = false;
64356435
n->deferrable = false;
64366436
n->initdeferred = false;
6437+
n->if_not_exists = false;
6438+
$$ = (Node *)n;
6439+
}
6440+
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS index_name
6441+
ON qualified_name access_method_clause '(' index_params ')'
6442+
opt_reloptions OptTableSpace where_clause
6443+
{
6444+
IndexStmt *n = makeNode(IndexStmt);
6445+
n->unique = $2;
6446+
n->concurrent = $4;
6447+
n->idxname = $8;
6448+
n->relation = $10;
6449+
n->accessMethod = $11;
6450+
n->indexParams = $13;
6451+
n->options = $15;
6452+
n->tableSpace = $16;
6453+
n->whereClause = $17;
6454+
n->excludeOpNames = NIL;
6455+
n->idxcomment = NULL;
6456+
n->indexOid = InvalidOid;
6457+
n->oldNode = InvalidOid;
6458+
n->primary = false;
6459+
n->isconstraint = false;
6460+
n->deferrable = false;
6461+
n->initdeferred = false;
6462+
n->if_not_exists = true;
64376463
$$ = (Node *)n;
64386464
}
64396465
;
Collapse file

‎src/include/catalog/index.h‎

Copy file name to clipboardExpand all lines: src/include/catalog/index.h
+2-1Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -60,7 +60,8 @@ extern Oid index_create(Relation heapRelation,
6060
bool allow_system_table_mods,
6161
bool skip_build,
6262
bool concurrent,
63-
bool is_internal);
63+
bool is_internal,
64+
bool if_not_exists);
6465

6566
extern void index_constraint_create(Relation heapRelation,
6667
Oid indexRelationId,
Collapse file

‎src/include/nodes/parsenodes.h‎

Copy file name to clipboardExpand all lines: src/include/nodes/parsenodes.h
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2256,6 +2256,7 @@ typedef struct IndexStmt
22562256
bool deferrable; /* is the constraint DEFERRABLE? */
22572257
bool initdeferred; /* is the constraint INITIALLY DEFERRED? */
22582258
bool concurrent; /* should this be a concurrent index build? */
2259+
bool if_not_exists; /* just do nothing if index already exists? */
22592260
} IndexStmt;
22602261

22612262
/* ----------------------
Collapse file

‎src/test/regress/expected/create_index.out‎

Copy file name to clipboardExpand all lines: src/test/regress/expected/create_index.out
+10Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,12 @@
66
-- BTREE
77
--
88
CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
9+
CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops);
10+
NOTICE: relation "onek_unique1" already exists, skipping
11+
CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops);
12+
ERROR: syntax error at or near "ON"
13+
LINE 1: CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_...
14+
^
915
CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
1016
CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
1117
CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
@@ -2290,10 +2296,14 @@ create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
22902296
CREATE TABLE concur_heap (f1 text, f2 text);
22912297
-- empty table
22922298
CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
2299+
CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1);
2300+
NOTICE: relation "concur_index1" already exists, skipping
22932301
INSERT INTO concur_heap VALUES ('a','b');
22942302
INSERT INTO concur_heap VALUES ('b','b');
22952303
-- unique index
22962304
CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
2305+
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1);
2306+
NOTICE: relation "concur_index2" already exists, skipping
22972307
-- check if constraint is set up properly to be enforced
22982308
INSERT INTO concur_heap VALUES ('b','x');
22992309
ERROR: duplicate key value violates unique constraint "concur_index2"

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.