BUG #17118: ALTER TABLE doesn't discard redundant UNIQUE constraints as CREATE TABLE will

The following bug has been logged on the website:

Bug reference: 17118
Logged by: Richard Zuber
Email address: zuberre(at)gmail(dot)com
PostgreSQL version: 13.3
Operating system: Debian 13.3-1.pgdg100+1 (Docker Image)
Description:

Hello,

I have created a table in a fresh database like so:

CREATE TABLE foo (
foo_id int NOT NULL,
CONSTRAINT foo_pk PRIMARY KEY (foo_id),
CONSTRAINT foo_uq UNIQUE (foo_id)
);

In order to view the indexes created, I run the following statement:

SELECT idx.relname AS index_name,
insp.nspname AS index_schema,
tbl.relname AS table_name,
tnsp.nspname AS table_schema
FROM pg_index pgi
JOIN pg_class idx ON idx.oid = pgi.indexrelid
JOIN pg_namespace insp ON insp.oid = idx.relnamespace
JOIN pg_class tbl ON tbl.oid = pgi.indrelid
JOIN pg_namespace tnsp ON tnsp.oid = tbl.relnamespace
WHERE pgi.indisunique AND tnsp.nspname = 'public';

The output I get is:

index_name | index_schema | table_name | table_schema
------------+--------------+------------+--------------
foo_pk | public | foo | public
(1 row)

I've noted that the documentation states: "Each unique constraint should
name a set of columns that is different from the set of columns named by any
other unique or primary key constraint defined for the table. (Otherwise,
redundant unique constraints will be discarded.)".

However, if I create the table like this (again in a fresh database):

CREATE TABLE foo (
foo_id int NOT NULL,
CONSTRAINT foo_pk PRIMARY KEY (foo_id)
);

ALTER TABLE foo ADD CONSTRAINT foo_uq UNIQUE (foo_id);

and then again query for the unique indices with the statement above I
get:

index_name | index_schema | table_name | table_schema
------------+--------------+------------+--------------
foo_pk | public | foo | public
foo_uq | public | foo | public
(2 rows)

There isn't any similar note in the documentation about discarding redundant
unique constraints on ALTER, so the documentation is consistent with
Postgres' behavior here. That said, I would have expected the ALTER TABLE
statement to also drop the redundant index due to the primary key, yielding
an identical database schema to the CREATE version.

OS info:
SELECT version();
version

------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
Note my test database is running under docker, hosted by a 20.04 Ubuntu x86
machine.

For some context on why this might matter to a user, I use a SQL generation
program (pgmodeler) as the "font of truth" for my schema design. SQL gets
exported from the tool and then compared to a series of patch files that
should end in an identical schema to the pgmodeler export. I utilize the
migra tool to compare differences and raise a build error if any are found.
I had inadvertently created a redundant index like the one described above
in an earlier patch. Older versions of pgmodeler appear to use the ALTER
approach thus creating the redundant index, but the tool now simply uses the
CREATE approach which drops the redundant index. When this issue cropped
up, I had initially thought it was a migra bug. For my own purposes, simply
dropping the redundant constraint was the ultimate solution.

I wanted to bring this to the attention of the community if it needed to be
addressed further. Perhaps just a note in the documentation would be
helpful to someone in the future if it doesn't make sense to change the
behavior of ALTER TABLE to match CREATE. Otherwise maybe this mailing list
entry can help someone out with a similar issue in the future.

Thanks to all of you who work on PostgreSQL; it's an amazing feat of
engineering and a pleasure to work with!

Thanks,
Richard Zuber