Date: Sat, 5 Oct 2024 11:22:52 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Request for Insights on ID Column Migration Approach
Message-ID: <20241005092252.2maykcbz2gbwwf4k@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
 <20240927222742.xmun3i57hjwiakuz@hjp.at>
 <729478.1727476655@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="j5ffu672uxegc2uh"
Content-Disposition: inline
In-Reply-To: <729478.1727476655@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/20241005092252.2maykcbz2gbwwf4k%40hjp.at>
Precedence: bulk


--j5ffu672uxegc2uh
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-27 18:37:35 -0400, Tom Lane wrote:
> "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).
>=20
> No, but you can attach an existing unique index as a primary key:
>=20
> regression=3D# create table t1 (f1 int not null);
> CREATE TABLE
> regression=3D# create unique index t1_f1 on t1(f1);
> CREATE INDEX
> regression=3D# 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


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

--j5ffu672uxegc2uh
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmcBBWwACgkQ8g5IURL+
KF1OMA//azx7yGo7echM/o2jdaXErXA/3jnPSrIPASMyWK4nEqGHswCrkd2AyJ2f
q+6X2a5UYKQybA4GPQdU3qZ22FVQfhyDRQb+zlujXa2KdKSMiGMEuJww14Pso+HH
lSso2S+uwGK/x1RdcXnWvugsaoUPw9y66R2Lw5AXx2CAsz4iCLRchOwj0xZUOrv8
nifp5NIDtkPuGvT1rVGSuSnthPVhn+75X6aQzGDincrzEmT81Vh8u1WkPUtxz2DQ
356sgCuUSLZVN22jKakhlVTp9QH76YaCqst6k79vYHdnLmHj4T3ZQiOX4u6rUJaV
TPY0GO/WsUtR9JCuiyt/Ct9ZYxbPHXjrXJQmCNy/eYewLUJfDmZ7Eo8dqYaT7x0N
knyW6THvpFYSHPZDcvxKIYLxWJHTU2Y3b6oVtvrbjDp2qLCDJBUzxGKFDO7v4nO3
gNZcsapIrSUI5pk0cwjOmNkD4oO2l1xewY4YiKffznOHZb1SdTvOY85Nd77Ao0CB
skHcV2O0b5TwQR2TM5liELIda9qQ73wkWUjgL+zaphrgXA8GVZnuKrAxEW4qhk4H
woYHAohDvyl25yueJ8FOd7MAuYQYgpE3FKfex4nsBWLHmejkgh6rpj00TZnU7wzV
keXrn8yu3vQITInP+WmcC1goefSezSAj4WJ8pHj7431k6fa1ljw=
=cI5F
-----END PGP SIGNATURE-----

--j5ffu672uxegc2uh--