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 Can you write answer so I accept it?

gavenkoa
– gavenkoa

2019-01-02 17:02:05 +00:00
Commented Jan 2, 2019 at 17:02 — gavenkoa
– gavenkoa, Commented Jan 2, 2019 at 17:02

wildplasser · Accepted Answer · 2019-01-02 17:15:25Z

1

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]

answered Jan 2, 2019 at 17:15

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related