Unexpected creation of duplicate unique constraints in Postgres-CodePudding

I am writing an idempotent schema change script for a Postgres 12 database. However I noticed that if I include the IF NOT EXISTS in an ADD COLUMN statement then even if the column already exists it is adding duplicate Indexes for the uniqueness constraint which already exists. Simple example:

-- set up base table
CREATE TABLE IF NOT EXISTS test_table 
(id SERIAL PRIMARY KEY
);

-- statement intended to be idempotent
ALTER TABLE test_table 
ADD COLUMN IF NOT EXISTS name varchar(50) UNIQUE;

Running this script creates a new index test_table_name_key[n] each time it is run. I can't find anything in the Postgres documentation and don't understand why this is allowed to happen? If I break it into two parts eg:

ALTER TABLE test_table 
ADD COLUMN IF NOT EXISTS name varchar(50);
ALTER TABLE 
ADD CONSTRAINT test_table_name_key UNIQUE (name);

Then the transaction fails because Postgres rejects the creation of a constraint which already exists (which I can then catch in a DO EXCEPTION block). As far as I can tell this is because doing it by this approach I am forced to give the constraint a name. This constrasts with the ALTER COLUMN SET NOT NULL which can be run multiple times without error or side effects as far as I can tell.

Question: why does it add a duplicate unique constraint and are there any problems with having multiple identical indexes on a table column? (I think this is a subtle 'error' and only spotted it by chance so am concerned it may arise in a production situation)

CodePudding user response：

You can create multiple unique constraints on the same column as long as they have different names, simply because there is nothing in the PostgreSQL code that forbids that. Each unique constraint will create a unique index with the same name, because that is how unique constraints are implemented.

This can be a valid use case: for example, if the index is bloated, you could create a new constraint and then drop the old one.

But normally, it is useless and does harm, because each index will make data modifications on the table slower.