Open
Description
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")
- 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