Unique indexes

If you need values in some of the columns to be unique, you can specify your index as UNIQUE.

When a unique index is applied to two or more columns, the combined values in these columns can't be duplicated in multiple rows. Note that because a NULL value is treated as a distinct value, you can have multiple NULL values in a column with a unique index.

If a table has a primary key or a UNIQUE constraint defined, a corresponding unique index is created automatically.

Syntax

CREATE UNIQUE INDEX index_name ON table_name(column_list);

Example

This example uses the categories table from the Northwind sample database.

View the contents of the categories table:

northwind=# SELECT * FROM categories  LIMIT 5;

 category_id | category_name  |                        description                         | picture
-------------+----------------+------------------------------------------------------------+---------
           4 | Dairy Products | Cheeses                                                    | \x
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales                | \x
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings | \x
           7 | Produce        | Dried fruit and bean curd                                  | \x
           3 | Confections    | Desserts, candies, and sweet breads                        | \x
(5 rows)

Create a UNIQUE index for the category_id column in the categories table.

northwind=# CREATE UNIQUE INDEX index_category_id
              ON categories(category_id);

List the index created using the following command:

northwind=# SELECT indexname,
                   indexdef
            FROM   pg_indexes
            WHERE  tablename = 'categories';

     indexname     |                                        indexdef
-------------------+-----------------------------------------------------------------------------------------
 categories_pkey   | CREATE UNIQUE INDEX categories_pkey ON public.categories USING lsm (category_id HASH)
 index_category_id | CREATE UNIQUE INDEX index_category_id ON public.categories USING lsm (category_id HASH)
(2 rows)

After the CREATE statement is executed, any attempt to insert a new category with an existing category_id will result in an error.

northwind=# INSERT INTO categories(category_id, category_name, description) VALUES (1, 'Savories', 'Spicy chips and snacks');

ERROR:  duplicate key value violates unique constraint "categories_pkey"

Insert a row with a new category_id and verify its existence in the table.

northwind=# INSERT INTO categories(category_id, category_name, description) VALUES (9, 'Savories', 'Spicy chips and snacks');

northwind=# SELECT * FROM categories;

 category_id | category_name  |                        description                         | picture
-------------+----------------+------------------------------------------------------------+---------
           4 | Dairy Products | Cheeses                                                    | \x
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales                | \x
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings | \x
           7 | Produce        | Dried fruit and bean curd                                  | \x
           9 | Savories       | Spicy chips and snacks                                     |
           3 | Confections    | Desserts, candies, and sweet breads                        | \x
           8 | Seafood        | Seaweed and fish                                           | \x
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal                        | \x
           6 | Meat/Poultry   | Prepared meats                                             | \x
(9 rows)

Learn more

• Unique index with HASH column ordering
• UNIQUE constraint
• Indexes on JSON attributes
• Benefits of Index-only scan
• CREATE TABLE