Database Reference
In-Depth Information
We create the unique index using the following statement:
warehouse_db=# CREATE UNIQUE INDEX idx_unique_id ON item
(item_id);
CREATE INDEX
The result can be seen using the following statement:
warehouse_db=# \d item;
Table "item"
Column | Type | Modifiers
------------+-----------------------+----------
item_id | integer | not null
item_name | text |
item_price | numeric |
item_data | text |
Indexes:
"idx_unique_id" UNIQUE, btree (item_id)
In all cases, a unique index ensures the integrity of data and throws an error in the
case of duplicity. This can be seen in the following example:
warehouse_db=# INSERT INTO item VALUES (1, 'boxing', 200,
'gloves');
warehouse_db=# INSERT INTO item VALUES (1, 'hockey', 300,
'shoes');
ERROR: duplicate key value violates unique constraint "
idx_unique_id "
DETAIL: Key (item_id)=(104) already exists.
Only B-tree, GiST, and GIN indexes support the unique index.
The expression index
We have discussed the creation of an index on a table's column, but in some cases,
there is a requirement to add an expression on one or more columns of the table.
For example, if we want to search for a case-insensitive item name, then the normal
way of doing this is as follows:
warehouse_db=# SELECT * FROM item WHERE UPPER(item_name) LIKE
'COFFEE';
 
Search WWH ::




Custom Search