UNIQUE Constraint in PostgreSQL

The UNIQUE constraint prevents duplicate values from being stored in a column or group of columns. Every time a row is inserted or updated, PostgreSQL checks the existing values and rejects the operation if a duplicate is found. PostgreSQL automatically creates a unique B-tree index to enforce this efficiently, so no separate index is needed on the same column.

Syntax

Inline column constraint:

CREATE TABLE table_name (
  col data_type UNIQUE
);

Named table-level constraint (recommended for multi-column or named constraints):

CREATE TABLE table_name (
  col1 data_type,
  col2 data_type,
  CONSTRAINT constraint_name UNIQUE (col1, col2)
);

Practical Example

Create a products table where SKUs and barcodes must be globally unique:

CREATE TABLE categories (
  category_id   SERIAL PRIMARY KEY,
  category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  product_id    SERIAL PRIMARY KEY,
  sku           VARCHAR(30) NOT NULL,
  barcode       VARCHAR(13),
  product_name  VARCHAR(200) NOT NULL,
  category_id   INT REFERENCES categories (category_id),
  CONSTRAINT uq_products_sku     UNIQUE (sku),
  CONSTRAINT uq_products_barcode UNIQUE (barcode)
);

Attempt to insert two rows with the same SKU:

INSERT INTO products (sku, product_name) VALUES ('SKU-ALPHA', 'USB-C Hub');
INSERT INTO products (sku, product_name) VALUES ('SKU-ALPHA', 'USB-A Hub');

-- ERROR:  duplicate key value violates unique constraint "uq_products_sku"
-- DETAIL:  Key (sku)=(SKU-ALPHA) already exists.

NULL values are permitted — multiple products can have a NULL barcode:

INSERT INTO products (sku, product_name) VALUES ('SKU-001', 'Mechanical Keyboard');
INSERT INTO products (sku, product_name) VALUES ('SKU-002', 'Trackpad');
-- Both rows have barcode = NULL — this succeeds because NULL ≠ NULL

Verify the constraint and its backing index:

\d products
-- Indexes:
--   "products_pkey" PRIMARY KEY, btree (product_id)
--   "uq_products_sku" UNIQUE CONSTRAINT, btree (sku)
--   "uq_products_barcode" UNIQUE CONSTRAINT, btree (barcode)

Composite UNIQUE Constraints and Low-Lock Approach for Existing Tables

Composite uniqueness — the combination of values must be distinct, not each individual value:

CREATE TABLE order_line_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL DEFAULT 1,
  CONSTRAINT uq_order_product UNIQUE (order_id, product_id)
);

This allows the same product to appear in different orders, but prevents duplicate line items for the same product within one order.

For tables with heavy concurrent traffic, adding uniqueness without locking requires two steps:

-- Step 1: build the index without blocking writes (takes a ShareUpdateExclusiveLock)
CREATE UNIQUE INDEX CONCURRENTLY uq_products_sku_idx
  ON products (sku);

-- Step 2: promote the index to a named constraint (brief ACCESS EXCLUSIVE lock)
ALTER TABLE products
  ADD CONSTRAINT uq_products_sku
  UNIQUE USING INDEX uq_products_sku_idx;

If the CONCURRENTLY build fails, it leaves an INVALID index behind. Drop and retry:

SELECT indexname, indisvalid
FROM pg_indexes
JOIN pg_index ON pg_indexes.indexname = (SELECT relname FROM pg_class WHERE oid = indexrelid)
WHERE tablename = 'products';

DROP INDEX CONCURRENTLY uq_products_sku_idx;
-- then retry CREATE UNIQUE INDEX CONCURRENTLY

Testing with Vela

Before adding a UNIQUE constraint to a production table, you need to confirm no duplicate values already exist in that column. Vela’s database branching lets you run the uniqueness audit and the CREATE UNIQUE INDEX CONCURRENTLY build on a copy-on-write clone of production data — letting you measure index build time, spot pre-existing duplicates, and test the USING INDEX promotion step — all without risk to the live database.

Production Tips

• UNIQUE constraints allow multiple NULL values. If you need at most one NULL, use a partial unique index: CREATE UNIQUE INDEX ON products (sku) WHERE sku IS NOT NULL;
• Use the CONCURRENTLY index approach when adding uniqueness to large, active tables to avoid prolonged locks that block writes.
• Name constraints explicitly (e.g., uq_products_sku) so migration rollbacks and error messages are self-descriptive.
• A UNIQUE constraint’s backing index serves lookup queries automatically — there is no need to create a separate regular index on the same column.
• Before adding a UNIQUE constraint, identify existing duplicates: SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1; — clean these up first.
• To check whether a CONCURRENTLY index build is still in progress, query pg_stat_progress_create_index.