Re: PostgreSQL equivalent of the Oracale 'unique' qualifier

Le mardi 21 novembre 2006 00:47, Tom Lane a écrit :
> 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.

In a migration from Informix we just done at dalibo, we found an UNIQUE
constraint (and index) used where two rows containing NULL in a column where
to be considered the same row, and had to rewrite it:

- CREATE unique index pki_exception on exception (id_classement, domaine,
id_categorie);
+ CREATE unique index pki_exception on exception (coalesce(id_classement,-1),
coalesce(domaine,''), coalesce(id_categorie,-1));

Then we have a behavior similar as Informix one:
insert into exception (id_classement, domaine, id_categorie)
values (1, null, 1);

insert into exception (id_classement, domaine, id_categorie)
values (1, null, 1);

=> error

Without the coalesce's, no error would have risen.

Hope this helps,
--
Dimitri Fontaine
http://www.dalibo.com/