In postgres how do I add index to existing table?

postgresql indexing

21,683

Well, this error message:

ERROR: relation "my_index" already exists

is pretty clear, isn't it.

You are trying to create an index with the name of an existing index or table. You need to use a different name.

21,683

Author by

user1467855

Updated on July 06, 2020

Comments

user1467855 almost 4 years
In postgres how do I add index to existing table?

I tried following but it's not working:
```
CREATE INDEX my_index ON my_table USING btree(a_column);
```
and then this:
```
CREATE INDEX my_index ON my_table USING btree(a_column);
```
But neither works.

I am using ant to do a db migration. And when I do ant db-migrate-apply-postgresql I keep getting the error
```
 [echo] ERROR:  relation "my_index" already exists
```
- Erwin Brandstetter almost 12 years
  
  The syntax you have there should work (Two identical examples?). Else you need to include the error message to make this a useful question. Or refer to the manual
- user1467855 almost 12 years
  
  I edit to add context and include error message.
- madth3 almost 12 years
  
  Error seems clear. Tried using other name for the index?
user1467855 almost 12 years

But in reality it's not true. I have no such table or index.
a_horse_with_no_name almost 12 years

@user1467855: apparently you have.
user1467855 almost 12 years

actually I am trying to add indexes to three different tables. For each table, the index would have the same name, "my_index". But ant is not adding index to any of the tables. Just as three different tables can have fields named id, this should not be a problem.
a_horse_with_no_name almost 12 years

@user1467855: an index name - just like a table name - must be unique. You cannot have three indexes with the same name (just as you cannot have three tables with the same name)
Andrew T Finnell almost 12 years

@user1467855 No database will let you use the same name. The index is scoped outside of the Table. Columns are scoped inside the table. That's why all tables can have a column with the same name.
Erwin Brandstetter almost 12 years

@user1467855: Make it a habit to prefix the indexes with the tablename a_my_index, b_my_index, ..
user1467855 almost 12 years

@Andrew Finnell, I try to give you upvote, but apparently I am too new here for that privilege. Thanks!