Text Size: Normal / Large

Home → Documentation → Manuals → PostgreSQL 8.0

PostgreSQL 8.0.12 Documentation
Prev	Fast Backward	Chapter 11. Indexes	Fast Forward	Next

11.4. Unique Indexes

Indexes may also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column.

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

Currently, only B-tree indexes can be declared unique.

When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.

PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. The index covers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Prev	Home	Next
Multicolumn Indexes	Up	Indexes on Expressions

User Comments

Timothy Hinchcliffe <pgdocs80 AT tim.hinchcliffe.peerex.net>

29 Mar 2006 10:29:46

If you have a multi-column unique index and wish to treat NULL values in some columns as equals then create an additional unique index which misses that column from the index and has a where clause of "is not null" for said column.

Example:
CREATE TABLE user_andor_company (
user VARCHAR,
company VARCHAR NOT NULL,
UNIQUE (user,company)
);

CREATE UNIQUE INDEX user_andor_company_company_key ON user_andor_company (company) WHERE user IS NOT NULL;

INSERT INTO user_andor_company VALUES ('user1','company1');
-- Success

INSERT INTO user_andor_company (company) VALUES ('company1');
-- Success

INSERT INTO user_andor_company VALUES ('user2','company1');
-- Success

INSERT INTO user_andor_company (company) VALUES ('company1');
-- ERROR: duplicate key violates unique constraint "user_andor_company_company_key"

Add Comment

Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.

In order to submit a comment, you must have a community account.

* denotes required field