Does the unique constraint in Postgres create an index?

Question

Let's say that I have a table that looks like the following:

CREATE TABLE products (
   product_no integer UNIQUE NOT NULL,
   name text,
   price numeric
);

Then if I insert values into the table, will postgres (or any similar DBMS) actually check through each row of the table or is an index automatically created? If there is no index, this becomes expensive really fast [O(N!)].

Gordon Linoff · Accepted Answer · 2020-03-06 03:36:21Z

3

Yes and no. Postgres checks that the product_no has not already been inserted. It does so by checking an index, not the individual rows.

The unique constraint is implemented using an index, so checking for duplication is O(log n) not O(n).

answered Mar 6, 2020 at 3:36

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Ariel A Over a year ago

Ok. But what about on a distributed system where there are multiple computers storing many many rows of data?

Gordon Linoff Over a year ago

@ArielA . . . Should be the same basic idea, using a distributed index -- it depends what you mean by "distributed" system. The unique index can be partitioned, for instance.

Laurenz Albe Over a year ago

@ArielA A partitioned unique index must contain the partitioning key. If you shard across multiple databases, the same restriction does apply.

Collectives™ on Stack Overflow

Does the unique constraint in Postgres create an index?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related