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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |