Working with Indexes - PostgreSQL Developer's Guide - page 66

item_data | text |
Indexes:
"item_pkey" PRIMARY KEY, btree (item_unique)
Here is an example of an implicit creation of a unique index by deining unique
constraints:
warehouse_db=# ALTER TABLE item ADD CONSTRAINT primary_key UNIQUE
(item_unique);
The preceding SQL statement alters the table and adds a unique constraint on the
item table's item_id column, and this statement also implicitly creates a unique
index (B-tree).
Here, we restart with the original table:
warehouse_db=# \d item;
Table "item"
Column | Type | Modifiers
-------------+-----------------------+-----------
item_unique | integer | not null
item_name | text |
item_price | numeric |
item_data | text |
Indexes:
"item_pkey" PRIMARY KEY, btree (item_unique)
"primary_key" UNIQUE CONSTRAINT, btree (item_unique)
The ALTER command adds a unique constraint to the item_id column and can be
used as the primary key.
Explicitly creating an index using the CREATE
INDEX command
We have discussed how a unique index can be implicitly created on a table, but
there is a way to create a unique index explicitly using the already discussed CREATE
INDEX command as follows:
warehouse_db=# CREATE TABLE item
(
item_id INTEGER PRIMARY KEY,
item_name TEXT,
item_price NUMERIC,
item_data TEXT
);