Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

Error 42P07 - Are index names unique in Postgres?

06 July,2023 by Rambler

Question: Are index names unique in Postgres? I'm attempting to create an index with the same name on two different tables and getting the error message:

ERROR: relation "idx_myindex" already exists SQL state: 42P07

 

Answer:  Indexes,  tables, views,sequences, functions,view, materialized view, & foreign table are stored in the pg_class and are unique per schema. 

When troubleshooting this problem ensure you're refering the unique schema . If you need to define the correct valid schema read How to change PostgreSQL default schema

In effect the schema acts as a namespace , with a requirement to define unique names for these objects. 

This example , illustrates an example of trying to create an index with the same name but on different tables. These tables are created on the same schema

create table myschema.test1
(id int,
myvalues varchar(20))


create table myschema.test2
(id int,
myvalues varchar(20))

CREATE INDEX idx_myindex
ON test1(id);

CREATE INDEX idx_myindex
ON test2(id);

 

 

ERROR: relation "idx_myindex" already exists SQL state: 42P07

 

If you need commands for troubleshooting use PostgreSQL cheatsheet


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Error 42P07 - Are index names unique in Postgres?


dba-ninja.com