Database Reference
In-Depth Information
Duplicate indexes
Note that PostgreSQL allows you to have multiple indexes with exactly the same definition.
This is useful in some contexts, but can also be annoying if you accidentally create multiple
indexes. You can also have constraints defined using each of the aforementioned different
ways. Each of these ways enforce essentially the same constraint. Take care.
Uniqueness without indexes
It's possible to have uniqueness in a set of columns without creating an index. That might
be useful if all we want is to ensure uniqueness rather than allow index lookups.
To do that, you can:
F
Use a serial datatype
F
Manually alter the default to be the
nextval()
of a sequence
Each of these will provide a unique value for use as a row's key. The uniqueness is not enforced,
nor will there be a unique constraint defined. So, there is still a possibility that someone might
reset the sequence to an earlier value, which would eventually cause duplicate values.
You might also wish to have
mostly
unique data, such as using the
timeofday()
function
to provide ascending times to microsecond resolution.
Real World Example: IP address range allocation
The problem is assigning ranges of IP addresses while at the same time ensuring that we
don't allocate (or potentially allocate) the same addresses to different people or purposes.
This is easy to do if we keep track of each individual IP address, though much harder to do if
we want to deal solely with ranges of IP addresses.
Initially, you may think of designing the database as follows:
CREATE TABLE iprange
(iprange_start inet
,iprange_stop inet
,owner text);
INSERT INTO iprange VALUES
('192.168.0.1','192.168.0.16', 'Simon');
INSERT INTO iprange VALUES
('192.168.0.17','192.168.0.24', 'Greg');
INSERT INTO iprange VALUES
('192.168.0.32','192.168.0.64', 'Hannu');
However, you realize that there is no way to create a unique constraint that enforces the
constraint. You could create an after trigger that checks existing values, but it's going to
be messy.
