Thread: pg can create duplicated index without any errors even warnning
postgres=# create table t (a int, b int);
CREATE TABLE
postgres=# create index m on t(a);
CREATE INDEX
postgres=# create index m2 on t(a);
CREATE INDEX
postgres=# \d t
Table "demo.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
Indexes:
"m" btree (a)
"m2" btree (a)
CREATE TABLE
postgres=# create index m on t(a);
CREATE INDEX
postgres=# create index m2 on t(a);
CREATE INDEX
postgres=# \d t
Table "demo.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
Indexes:
"m" btree (a)
"m2" btree (a)
is this by design?
On Mon, Aug 5, 2019 at 7:34 PM Alex <zhihui.fan1213@gmail.com> wrote: > is this by design? Yes. Being able to do this is useful for several reasons. For example, it's useful to be able to create a new, equivalent index before dropping the original when the original is bloated. (You could use REINDEX instead, but that has some disadvantages that you might want to avoid.) Questions like this are better suited to the pgsql-general list. -- Peter Geoghegan
On Mon, Aug 05, 2019 at 08:16:11PM -0700, Peter Geoghegan wrote: > Yes. Being able to do this is useful for several reasons. For example, > it's useful to be able to create a new, equivalent index before > dropping the original when the original is bloated. (You could use > REINDEX instead, but that has some disadvantages that you might want > to avoid.) REINDEX CONCURRENTLY recently added to v12 relies on that heavily actually, so as you can finish with the same index definition twice in the state of swapping both index definitions. -- Michael
Attachment
Alex <zhihui.fan1213@gmail.com> writes:
> postgres=# create table t (a int, b int);
> CREATE TABLE
> postgres=# create index m on t(a);
> CREATE INDEX
> postgres=# create index m2 on t(a);
> CREATE INDEX
> is this by design?
Yes.
regards, tom lane