Skip to content

Commit 08309aa

Browse 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

File tree

11 files changed

+85
-5
lines changed

11 files changed

+85
-5
lines changed

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

Lines 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>

‎src/backend/catalog/index.c

Lines 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

‎src/backend/catalog/toasting.c

Lines 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

‎src/backend/commands/indexcmds.c

Lines 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)

‎src/backend/nodes/copyfuncs.c

Lines 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
}

‎src/backend/nodes/equalfuncs.c

Lines 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
}

‎src/backend/parser/gram.y

Lines 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
;

‎src/include/catalog/index.h

Lines 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,

‎src/include/nodes/parsenodes.h

Lines 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
/* ----------------------

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

Lines 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"

‎src/test/regress/sql/create_index.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,10 @@
88
--
99
CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
1010

11+
CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops);
12+
13+
CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops);
14+
1115
CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
1216

1317
CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
@@ -711,10 +715,12 @@ create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
711715
CREATE TABLE concur_heap (f1 text, f2 text);
712716
-- empty table
713717
CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
718+
CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1);
714719
INSERT INTO concur_heap VALUES ('a','b');
715720
INSERT INTO concur_heap VALUES ('b','b');
716721
-- unique index
717722
CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
723+
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1);
718724
-- check if constraint is set up properly to be enforced
719725
INSERT INTO concur_heap VALUES ('b','x');
720726
-- check if constraint is enforced properly at build time

0 commit comments

Comments
 (0)