Skip to content

Commit 0076f26

Browse files
hlinnakahlinnaka
committed
Implement IF NOT EXISTS for CREATE SEQUENCE.
Fabrízio de Royes Mello
1 parent 57ca1d4 commit 0076f26

File tree

8 files changed

+49
-2
lines changed

8 files changed

+49
-2
lines changed

‎doc/src/sgml/ref/create_sequence.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 [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
24+
CREATE [ TEMPORARY | TEMP ] [ IF NOT EXISTS ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
2525
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
2626
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
2727
[ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
@@ -89,6 +89,18 @@ SELECT * FROM <replaceable>name</replaceable>;
8989
</listitem>
9090
</varlistentry>
9191

92+
<varlistentry>
93+
<term><literal>IF NOT EXISTS</literal></term>
94+
<listitem>
95+
<para>
96+
Do not throw an error if a relation with the same name already exists.
97+
A notice is issued in this case. Note that there is no guarantee that
98+
the existing relation is anything like the sequence that would have
99+
been created - it might not even be a sequence.
100+
</para>
101+
</listitem>
102+
</varlistentry>
103+
92104
<varlistentry>
93105
<term><replaceable class="parameter">name</replaceable></term>
94106
<listitem>

‎src/backend/commands/sequence.c

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -122,6 +122,24 @@ DefineSequence(CreateSeqStmt *seq)
122122
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
123123
errmsg("unlogged sequences are not supported")));
124124

125+
/*
126+
* If if_not_exists was given and a relation with the same name already
127+
* exists, bail out. (Note: we needn't check this when not if_not_exists,
128+
* because DefineRelation will complain anyway.)
129+
*/
130+
if (seq->if_not_exists)
131+
{
132+
RangeVarGetAndCheckCreationNamespace(seq->sequence, NoLock, &seqoid);
133+
if (OidIsValid(seqoid))
134+
{
135+
ereport(NOTICE,
136+
(errcode(ERRCODE_DUPLICATE_TABLE),
137+
errmsg("relation \"%s\" already exists, skipping",
138+
seq->sequence->relname)));
139+
return InvalidOid;
140+
}
141+
}
142+
125143
/* Check and set all option values */
126144
init_params(seq->options, true, &new, &owned_by);
127145

@@ -210,7 +228,7 @@ DefineSequence(CreateSeqStmt *seq)
210228
stmt->options = NIL;
211229
stmt->oncommit = ONCOMMIT_NOOP;
212230
stmt->tablespacename = NULL;
213-
stmt->if_not_exists = false;
231+
stmt->if_not_exists = seq->if_not_exists;
214232

215233
seqoid = DefineRelation(stmt, RELKIND_SEQUENCE, seq->ownerId);
216234
Assert(seqoid != InvalidOid);

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3330,6 +3330,7 @@ _copyCreateSeqStmt(const CreateSeqStmt *from)
33303330
COPY_NODE_FIELD(sequence);
33313331
COPY_NODE_FIELD(options);
33323332
COPY_SCALAR_FIELD(ownerId);
3333+
COPY_SCALAR_FIELD(if_not_exists);
33333334

33343335
return newnode;
33353336
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1566,6 +1566,7 @@ _equalCreateSeqStmt(const CreateSeqStmt *a, const CreateSeqStmt *b)
15661566
COMPARE_NODE_FIELD(sequence);
15671567
COMPARE_NODE_FIELD(options);
15681568
COMPARE_SCALAR_FIELD(ownerId);
1569+
COMPARE_SCALAR_FIELD(if_not_exists);
15691570

15701571
return true;
15711572
}

‎src/backend/parser/gram.y

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3486,6 +3486,17 @@ CreateSeqStmt:
34863486
n->sequence = $4;
34873487
n->options = $5;
34883488
n->ownerId = InvalidOid;
3489+
n->if_not_exists = false;
3490+
$$ = (Node *)n;
3491+
}
3492+
| CREATE OptTemp SEQUENCE IF_P NOT EXISTS qualified_name OptSeqOptList
3493+
{
3494+
CreateSeqStmt *n = makeNode(CreateSeqStmt);
3495+
$7->relpersistence = $2;
3496+
n->sequence = $7;
3497+
n->options = $8;
3498+
n->ownerId = InvalidOid;
3499+
n->if_not_exists = true;
34893500
$$ = (Node *)n;
34903501
}
34913502
;

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1979,6 +1979,7 @@ typedef struct CreateSeqStmt
19791979
RangeVar *sequence; /* the sequence to create */
19801980
List *options;
19811981
Oid ownerId; /* ID of owner, or InvalidOid for default */
1982+
bool if_not_exists; /* just do nothing if it already exists? */
19821983
} CreateSeqStmt;
19831984

19841985
typedef struct AlterSeqStmt

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,8 @@ SELECT nextval('serialTest2_f6_seq');
9191

9292
-- basic sequence operations using both text and oid references
9393
CREATE SEQUENCE sequence_test;
94+
CREATE SEQUENCE IF NOT EXISTS sequence_test;
95+
NOTICE: relation "sequence_test" already exists, skipping
9496
SELECT nextval('sequence_test'::text);
9597
nextval
9698
---------

‎src/test/regress/sql/sequence.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,7 @@ SELECT nextval('serialTest2_f6_seq');
5959

6060
-- basic sequence operations using both text and oid references
6161
CREATE SEQUENCE sequence_test;
62+
CREATE SEQUENCE IF NOT EXISTS sequence_test;
6263

6364
SELECT nextval('sequence_test'::text);
6465
SELECT nextval('sequence_test'::regclass);

0 commit comments

Comments
 (0)