Re: PostgreSQL equivalent of the Oracale 'unique' qualifier

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Wm.A.Stafford wrote:
>> I hope the subject says it all. I'm porting an Oracle-centric
>> application to PostgreSQL and the Oracle sql is full of the 'unique'
>> qualifier. I'm assuming PostgreSQL does not support 'unique' since
>> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
>> substitute or a technique to get the same result?

> You gotta be kidding. Of course Postgres supports UNIQUE.

Actually, there is a <unique predicate> in SQL92, which we've not gotten
around to implementing ... but from the subsequent discussion it seems
that what the OP is looking at is something else, ie, a gratuitously
nonstandard spelling of the DISTINCT modifier for aggregate functions :-(

A <unique predicate> is syntactically like EXISTS:

<unique predicate> ::= UNIQUE <table subquery>

General Rules

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.

(This matches up with the behavior of unique constraints/unique indexes
because the spec actually defines a unique constraint in terms of the
truth of a unique predicate.)

Hmm ... note that there is a difference between this definition of
uniqueness and the behavior of DISTINCT, which is that two rows
containing nulls can be "the same" according to DISTINCT, but they'll
never be "the same" according to UNIQUE. Is it possible that Oracle's
UNIQUE aggregate modifier is not just a relabeling of DISTINCT, but uses
a two-nulls-are-different-from-each-other definition unlike DISTINCT?
If so, and if this fine point is critical to the OP's code, he's gonna
have a bit of a problem.

regards, tom lane