Database Reference
In-Depth Information
The last method is important, because it allows you to specify a WHERE clause on the
index. This can be useful if you know that the column values are unique only in certain
circumstances. The resulting index is then known as a partial index.
If our data looked like the following:
postgres=# select * from partial_unique;
customerid | status | close_date
------------+--------+------------
1 | OPEN |
2 | OPEN |
3 | OPEN |
3 | CLOSED | 2010-03-22
(4 rows)
then we could put a partial index on the table to enforce uniqueness of
customerids
only
for
status
=
'OPEN'
, for example:
CREATE UNIQUE INDEX ON partial_unique (customerid)
WHERE status = 'OPEN';
If your uniqueness constraint needs to be enforced across more complex datatypes, then
there is a more advanced syntax you may need to use. A few examples will help here.
Let's start with the simplest example: Create a table of boxes and put sample data in it. This
may be the first time you've seen PostgreSQL's datatype syntax, so bear with me.
postgres=# CREATE TABLE boxes (name text, position box);
CREATE TABLE
postgres=# INSERT INTO boxes VALUES
('First', box '((0,0), (1,1))');
INSERT 0 1
postgres=# INSERT INTO boxes VALUES
('Second', box '((2,0), (2,1))');
INSERT 0 1
postgres=# SELECT * FROM boxes;
name | position
--------+-------------
First | (1,1),(0,0)
Second | (2,1),(2,0)
(2 rows)
We can see two boxes that neither touch nor overlap, based upon their (x, y) coordinates.
To enforce uniqueness here, we want to create a constraint that will throw out any attempt to
add a position that overlaps with any existing box. The overlap operator for the box datatype
is defined to be
&&
, so we use the following syntax to add the constraint:
