pgsql-general-owner@xxxxxxxxxxxxxx wrote on 07/15/2005 02:49:09 PM: > On Fri, Jul 15, 2005 at 20:08:32 +0300, > Andrus <eetasoft@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
