Re: Difference between UNIQUE constraint vs index

This is more or less correct, I was looking for performance gains on
the [possible] differences during DML and DDL.

If Jim is correct, is there a particular reason that PostgreSQL does
not behave like other RDBMs without a SET ALL DEFERRED? Or is this a
discussion best left for -HACKERS?

On 2/27/07, Jim C. Nasby <jim(at)nasby(dot)net> wrote:
> On Tue, Feb 27, 2007 at 06:43:51PM -0600, John Jawed wrote:
> > Is there any difference as far as when the "uniqueness" of values is
> > checked in DML between a unique index vs a unique constraint? Or is
> > the only difference syntax between unique indices and constraints in
> > PostgreSQL?
>
> Syntax only, AFAIK. I prefer using constraints if I actually want to
> constrain the data; it makes it clear that it's a restriction.
>
> In some databases if you know that an index just happens to be unique
> you might gain some query performance by defining the index as unique,
> but I don't think the PostgreSQL planner is that smart. There can also
> be some additional overhead involved with a unique index (vs
> non-unique), such as when two backends try and add the same key at the
> same time (one of them will have to block).
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>