Re: Request for Insights on ID Column Migration Approach

"Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> writes:
> As you can see, adding the primary key takes just as much time as
> creating the unique index. So it doesn't look like PostgreSQL is able to
> take advantage of the existing index (which makes sense since it still
> has to create a new index).

No, but you can attach an existing unique index as a primary key:

regression=# create table t1 (f1 int not null);
CREATE TABLE
regression=# create unique index t1_f1 on t1(f1);
CREATE INDEX
regression=# alter table t1 add primary key using index t1_f1;
ALTER TABLE

If you build the unique index concurrently, this needn't involve
much downtime.

regards, tom lane