Database Reference
In-Depth Information
postgres=# ALTER TABLE boxes ADD EXCLUDE USING gist
(position WITH &&);
NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "boxes_
position_exclusion" for table "boxes"
ALTER TABLE
which creates a new index named
boxes_position_excl
.
We can use the same syntax even with the basic datatypes. So a fourth way of performing our
first example would be as follows:
ALTER TABLE newcust ADD EXCLUDE (customerid WITH =);
which creates a new index named
newcust_customerid_excl
.
How it works...
Uniqueness is always enforced by an index.
Each index is defined with a datatype operator. When a new row is inserted or the set of
column values are updated, we use the operator to search for existing values that conflict
with the new data.
So, to enforce uniqueness, we need an index and a search operator defined on the datatypes
of the columns. When we define normal
UNIQUE
constraints, we simply assume we mean the
equality operator ("=") for the datatype. The
EXCLUDE
syntax offers a richer syntax to allow us
to express the same problem with different datatypes and operators.
There's more...
Unique constraints can be marked as "deferrable". However, there are a number of restrictions
on this that make this feature not very usable in PostgreSQL 9.0. The restrictions are as
follows:
F
You must define a constraint as
DEFERRABLE
on the
CREATE
TABLE
. You cannot
define this on a
CREATE
TABLE
AS
SELECT
, nor can these be added later with an
ALTER
TABLE
command.
F
You cannot mix deferrable unique constraints with Foreign Keys. You will get an
error message if you try to add a Foreign Key that refers to a unique constraint
that is deferrable.
It's likely that those restrictions will be lifted in later releases.
