Doesn't Postgres reuse unique indexes for unique key constraint?

Question

Having experience with Oracle I assumed that each unique constraint would reuse unique index.

I created schema population script that creates named unique index and then same unique constraint. In that way I hoped to set index name explicitly rather than relay on Postgres default naming schema.

As experiment was shown I got two indexes with same definition in a result:

CREATE UNIQUE INDEX agent_ux ON agent (branch_id, initials);
ALTER TABLE agent ADD CONSTRAINT agent_uk UNIQUE (branch_id, initials);

select indexname from pg_indexes where tablename = 'agent';
agent_ux
agent_uk

Doesn't Postgres reuse unique indexes for unique key constraint?

NOTE I can't drop index, corresponding to unique constraint (error says about related constraint), but index is automatically deleted if I delete constraint.

wildplasser wildplasser · Accepted Answer · 2019-01-02T17:15:25

In postgres, creating a UNIQUE constraint automatically creates an index. You can also create the constraint by promoting an existing index, using the ALTER TABLE ttt add constraint ccc USING xxx syntax: Documentation

ALTER TABLE agent
   ADD CONSTRAINT agent_uk UNIQUE USING agent_ux;

[untested]

Doesn't Postgres reuse unique indexes for unique key constraint?

1 Answers