Thomas Lockhart <lockhart@fourpalms.org> writes:
>> Where are these two new clauses used?
> I haven't looked yet at UNIQUE.
UNIQUE is defined by SQL92 section 8.9:
<unique predicate> ::= UNIQUE <table subquery>
1) Let T be the result of the <table subquery>.
2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.
A UNIQUE constraint (implemented by a unique index in PG) is essentially
an assertion holding that "UNIQUE (SELECT some-columns FROM table)" is
true at all times.
It'd be possible to implement the UNIQUE predicate using a simple
variant of our existing Unique/Group By support. But the amount of
work needed still seems out of proportion to the demand for the
feature ;-). I note that SQL92 does not consider this an entry-level
SQL feature --- which is odd considering that unique constraints,
which are defined in terms of the unique predicate, *are* entry SQL.
But in terms of usefulness I think the SQL authors got this right.
regards, tom lane