Re: Request for Insights on ID Column Migration Approach

On 2024-09-27 18:37:35 -0400, Tom Lane wrote:
> "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

Ah, yes. I thought that was possible but I checked the docs[1] and
didn't see it. I checked again before writing this mail and still didn't
see it. Only when I started copy-pasting relevant parts of th synopsis
into the mail did I notice "ADD table_constraint_using_index". I'll get
new glasses soon, I promise. ;-)

hp

[1] https://www.postgresql.org/docs/current/sql-altertable.html

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"