Re: How to create unique constraint on NULL columns

pgsql-general-owner(at)postgresql(dot)org wrote on 07/15/2005 02:49:09 PM:

> On Fri, Jul 15, 2005 at 20:08:32 +0300,
> Andrus <eetasoft(at)online(dot)ee> wrote:
> >
> > So I'll think still continuing to use null as unrestricted department
> > access.
> >
> > Is it reasonable to create unique constraint using
> >
> > CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx
> > ON permission (user_id, permission_id, COALESCE(department_id,'ALL'))
>
> If you are going to do this a partial index is a better way to go.
> Something like:
> CREATE UNIQUE INDEX user_id_permission_id_null ON permission
> WHERE department_id IS NULL;
>
> However either of these let you insert and entry for "ALL" while also
> having entries for individual departments.

That's a lot of overhead for doing something very simple, like defining a
department key that means ALL and a row in the foreign table for it to
point to. Maintaining indices is a nontrivial performance trade-off.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings