bug: Inconsistant Constraints Post migration

[bbernays]

Postgres has an optimization that stops the creation of duplicate indexes in the case of a Unique constraint being applied to the primary key. So the following two DML statements are not equivalent:

This is how we migrate users to the new primary key:

CREATE TABLE IF NOT EXISTS "aws_table_initial" (
    "_cq_id" uuid UNIQUE NOT NULL,
    "account_id" text,
    CONSTRAINT "aws_table_initial_cqpk" PRIMARY KEY ("account_id")
)
ALTER TABLE aws_table_initial DROP CONSTRAINT aws_table_initial_cqpk
ALTER TABLE aws_table_initial ADD CONSTRAINT aws_table_initial_cqpk PRIMARY KEY ("_cq_id")

2. Creating a new table with only _cq_id as the PK:

CREATE TABLE IF NOT EXISTS "aws_table_initial_v2" (
    "_cq_id" uuid UNIQUE NOT NULL,
    "account_id" text,
    CONSTRAINT "aws_table_initial_cqpk" PRIMARY KEY ("_cq_id")
)

Statement (1) results in two constraints on cq_id (aws_table_initial_cqpk and aws_table_initial__cq_id_key) while statement (2) results in only a single constraint aws_table_initial_v2_cqpk