CODEWITHSUNDEEP
sqlpostgresqlconstraintsruntime
Ariel A
Ariel A

Reputation: 604

Does the unique constraint in Postgres create an index?

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!)].

Upvotes: 1

Views: 177

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

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).

Upvotes: 3

Related Questions