Le mardi 21 novembre 2006 00:47, Tom Lane a écrit : > Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> 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/
Attachment:
pgp2Kw1K47GQ2.pgp
Description: PGP signature
