I work with Postgres 11 and would like to change the index that backs the primary key of a large table (~750 million rows). The primary key is the
id and I would like to include an extra column using the
INCLUDE term. This needs to happen without a table rewrite (i.e. no new/changed columns).
Dropping the current
PRIMARY KEY constraint isn't convenient, because a lot of other tables reference the target table. I suppose it would be possible to drop the
FOREIGN KEY constraints on all those tables, then drop the
PRIMARY KEY constraint, recreate using a new index and then recreate the
FOREIGN KEY constraints. Is there a better way to do it?