Does creating a unique constraint also create an index in Postgres?

Question

I'm cleaning up some unused indexes on some tables. Before removing them I'm looking up to see how they were created. Some of them looks like they were added when the table was created by adding a constraint somename unique int the sql script. It makes me think there are multiple ways to create an index. Does creating a table like this create an index automatically?

(
    id serial not null,
    name text not null,
    other_name text not null,
    constraint constraint_or_index
        unique (name, other_name)
);

user330315 · Accepted Answer · 2020-03-04 20:06:39Z

2

Quote from the manual

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint

answered Mar 4, 2020 at 20:06

user330315

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

5 Comments

Jonathan Over a year ago

Thank you. Do you think they are safe to remove if they aren't being used?

user330315 Over a year ago

@Jonathan: only you can tell. If you don't need the unique constraint any more then you can remove it.

Jonathan Over a year ago

Hmm... If I drop the automatically created index would this also remove the constraint? I don't think I want to do that. I'll have to go through these indexes and see which of them are created via unique constraint and not remove them. Thanks again for your help.

user330315 Over a year ago

@Jonathan: yes, you can't have a unique constraint without a unique index

Jonathan Over a year ago

Thanks again! This kept me from doing something rash, lol. :)

Collectives™ on Stack Overflow

Does creating a unique constraint also create an index in Postgres?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related