Copying a Postgres table and using the same name index names

Question

Why can't different postgres tables use indexes with the same name?

I'm trying to alter a number of tables from basic tables to monthly partitioned tables. The normal solution seems to be

• Create a partitioned copy (empty) of the source table CREATE TABLE copy (LIKE original INCLUDING INDEXES) PARTITION BY RANGE xxxx
• Copy the data from source table to the copy. INSERT INTO copy (SELECT * FROM original WHERE ???)
• Drop the source DROP TABLE original
• Rename the partitioned table. ALTER TABLE copy RENAME TO original

My current issue is that although the CREATE TABLE ... LIKE ... INCLUDING INDEXES will use all the same field names and datatypes, it will make up a new set of index names. For a variety of reasons, the indexes on these databases need to conform to a fixed naming convention.

Is there a way of copying a table and copying the index names to?

Answer 1

Two indexes in the same schema cannot have the same name. Tables, indexes, views, sequences, materialized views, composite data types and foreign tables all share the same namespace, and the combination of schema name and object name must be unique.

The only way to have the same index names on both tables would be to create the copy in a different schema. But CREATE TABLE ... (LIKE ... INCLUDING INDEXES) will assign its own names to the indexes anyway.

Your best choice is perhaps to create the copy in a different schema and then rename the indexes that PostgreSQL created to your liking.

Here is another idea: pg_dump -s the tables, create a new database, restore the dump there, rename the schema, pg_dump -s again and restore that dump to the original database.