From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Request for Insights on ID Column Migration Approach
In-reply-to: <20240927222742.xmun3i57hjwiakuz@hjp.at>
References: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com> <20240927222742.xmun3i57hjwiakuz@hjp.at>
Comments: In-reply-to "Peter J. Holzer" <hjp-pgsql@hjp.at>
	message dated "Sat, 28 Sep 2024 00:27:42 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <729477.1727476655.1@sss.pgh.pa.us>
Date: Fri, 27 Sep 2024 18:37:35 -0400
Message-ID: <729478.1727476655@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/729478.1727476655%40sss.pgh.pa.us>
Precedence: bulk

"Peter J. Holzer" <hjp-pgsql@hjp.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