ThisCalculator

Everyday

PostgreSQL Unique: Constraint or Index, Added Safely

Updated · Standard formula, shown below

Average pace 5.00 min/km
Average speed
12.00 km/h
Result
Finish time
50 min
You entered
Distance
10 km
You entered

To enforce uniqueness in PostgreSQL, you can use either a unique constraint or a unique index. The safe way to add one without blocking writes is to use CREATE UNIQUE INDEX CONCURRENTLY; if you need a constraint for foreign key references, create the index first and then add the constraint using ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX. This article explains the difference, how to choose, and the exact steps to add uniqueness safely on a live table.

Formula

Although the calculator above is unrelated to PostgreSQL, it demonstrates the site’s standard format. For the topic at hand, the “formula” is the SQL syntax itself:

CREATE UNIQUE INDEX [ CONCURRENTLY ] name ON table (column);

And for a constraint:

ALTER TABLE table ADD CONSTRAINT name UNIQUE USING INDEX index_name;

Variables: table is the target table, column is the column or expression to enforce uniqueness on, index_name is the previously created unique index, and name is the constraint name.

Worked Example

Suppose you have a table users with a column email and you want to ensure no two rows share the same email. The default values in the calculator block are just placeholders; here we use a real example.

  1. Create the unique index concurrently:
CREATE UNIQUE INDEX CONCURRENTLY users_email_idx ON users (email);
  1. If you need a constraint (for foreign keys), attach it:
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_idx;

This two-step approach avoids a long write lock that would block inserts and updates. The concurrent index build takes a brief lock at the start and end, but allows normal operations in between.

Unique Constraint vs. Unique Index: What’s the Difference?

In PostgreSQL, a unique constraint is implemented using a unique index behind the scenes. The practical differences are:

  • Constraint is a logical rule; it appears in information_schema and can be referenced by foreign keys. It also allows ON CONFLICT to work with the constraint name.
  • Index is a physical structure; it can be used for query performance and can include additional columns (INCLUDE) or be partial (WHERE clause).
  • You can drop an index without dropping the constraint, but dropping a constraint drops its index automatically.
  • For a foreign key to reference a column, a unique constraint (or a unique index) must exist on that column, but the index must be a plain unique index, not a partial one.

How to Choose: Constraint or Index?

Choose a constraint when you want to enforce a business rule and possibly reference it from another table. Choose an index when you need a unique index for performance or a partial unique index (e.g., unique only on non-deleted rows). In most cases, a unique constraint is the right default because it communicates intent and supports foreign keys.

Adding Uniqueness Safely on a Live Table

Adding a unique constraint directly with ALTER TABLE ADD CONSTRAINT takes a full table lock and blocks writes for the entire duration. For large tables, that can be minutes or hours. The safe method:

  1. Create a unique index concurrently: CREATE UNIQUE INDEX CONCURRENTLY ...
  2. If you need a constraint, attach it using ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX ...

This two-step process minimizes downtime. The concurrent index build does not block writes, but it does take a brief lock at the beginning and end. If the build fails (e.g., duplicate rows), the index is left in an invalid state and must be dropped. Always check for duplicates before starting.

Common Problems and Pitfalls

  • Duplicate rows: The index creation fails if duplicates exist. Find them with SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1; and resolve them first.
  • Concurrent build failure: If a concurrent index build fails, it leaves an invalid index. You must drop it before retrying.
  • Lock timeouts: Even with CONCURRENTLY, the initial lock can wait behind other transactions. Set a reasonable lock_timeout to avoid indefinite waits.
  • Using a partial unique index for a constraint: You cannot attach a partial index to a constraint. If you need a partial unique rule, keep it as an index.
  • Nulls: In PostgreSQL, NULL values are considered distinct, so multiple NULLs are allowed in a unique column. If you need to treat NULLs as equal, use a unique index with COALESCE or a partial index.

Performance and Cost Considerations

A unique index adds overhead to every insert and update because the database must check for duplicates. The cost is proportional to the size of the index and the number of rows. A concurrent build uses more I/O and CPU than a regular build, but it avoids blocking writes. The index also uses disk space. For very large tables, consider building the index during off-peak hours and testing the duplicate check beforehand.

Alternatives and Related Features

  • Exclusion constraints: For more complex uniqueness (e.g., no overlapping time ranges), use EXCLUDE with GiST indexes.
  • Primary key: A primary key is a unique constraint plus a NOT NULL constraint. Use it when the column is the row identifier.
  • Unique index on expressions: Use CREATE UNIQUE INDEX ON table (lower(email)); for case-insensitive uniqueness.
  • ON CONFLICT: With a unique index or constraint, you can use INSERT ... ON CONFLICT to handle duplicates gracefully.

Frequently Asked Questions

Can I add a unique constraint without locking the table?

No, adding a constraint directly locks the table. But you can create a unique index concurrently and then attach it as a constraint. The attach operation takes a brief lock, but it is very fast because the index already exists.

What happens if a concurrent unique index build fails?

The index is left in an invalid state. You must drop it with DROP INDEX and fix the underlying issue (usually duplicates) before retrying. The table remains usable during the failed build.

Can I use a unique index instead of a unique constraint for a foreign key?

Yes, a foreign key can reference a unique index, as long as the index is not partial and the columns match. However, it’s clearer to use a constraint because it appears in metadata and is easier to manage.

How do I find duplicate rows before creating a unique index?

Run a query like SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1; to list duplicate values. You can then decide how to handle them (delete, update, or add a condition).

Does a unique index allow NULL values?

Yes, in PostgreSQL, NULL values are considered distinct, so multiple NULLs are allowed. If you need to treat NULLs as duplicates, use a unique index on COALESCE(col, 'some_sentinel') or a partial index.

Related

Formula used
in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.