"Andrus" <kobruleht2@xxxxxx> writes: > PostgreSQL runs commands > create table test ( test integer primary key ); > create index i1 on test(test); > create index i2 on test(test); > without any error. > Now there are 3 same indexes on table. > How to fix this so that duplicate indexes are not allowed ? Simple: don't do that. Having the system try to prevent this has been proposed, and rejected, before. The main argument against has been that it'd prevent some occasionally-useful procedures. Instead of REINDEX, which locks out use of the index (for a long time, if table is big), you can make a new index in parallel with CREATE INDEX CONCURRENTLY, and then drop the old index, and not deny service to any query for very long. Another objection is that it's not always clear which indexes are redundant --- eg, should we forbid indexes on both (a,b) and (b,a)? In your above example the indexes aren't even really identical --- the pkey one is unique and the others not. regards, tom lane
