Re: How to create unique constraint on NULL columns

On Fri, Jul 15, 2005 at 20:08:32 +0300,
  Andrus <eetasoft@online.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.