Filtering by tags - Mailing list pgsql-general

What's the recommended way of storing "tags" in a database, and then
filtering based on the existence, or *non*-existence, of those tags on
some entities?

Our application stores contacts, where each contact may have any number
of tags. We do this with the tables contacts, contacts_tags and tags. We
also have segments, which defines "filters" on contacts based on
specific tags they must have and/or must *not* have. This is defined by
the tables segments and segments_tags. (See bottom of post for table
definitions).

Finding contacts matching a given segment which has BOTH positive
(required tags) and negative (non-existing tags) requirements is easy
enough (simplified):

SELECT segmentid, email
     FROM segments_tags st
         INNER JOIN contacts_tags ct USING (tagname)
             INNER JOIN contacts USING (email)
     WHERE st.tagtype = 1
     GROUP BY 1, 2
     HAVING COUNT(*)
         = (SELECT COUNT(*) FROM segments_tags
             WHERE segmentid = st.segmentid AND tagtype = 1)
EXCEPT
SELECT segmentid, email
     FROM segments_tags st
         INNER JOIN contacts_tags ct USING (tagname)
             INNER JOIN contacts USING (email)
     WHERE st.tagtype = 0;

However, segments which ONLY contain negative requirements (that's
"tagtype" = 0) doesn't work, for obvious reasons.

Is there a way to make this work with a single query for both cases?
Possibly using CTE (which I'm not very familiar with)?

Table definitions:
                           Table "public.contacts"
     Column     |            Type             |          Modifiers

---------------+-----------------------------+-----------------
  email         | email                       | not null
  name          | text                        |
  status        | character(1)                | not null default 'a'::bpchar
  statuschanged | timestamp without time zone |
Indexes:
     "contacts_pkey" PRIMARY KEY, btree (email)

Table "public.contacts_tags"
  Column  | Type  | Modifiers
---------+-------+-----------
  email   | email | not null
  tagname | text  | not null
Indexes:
     "contacts_tags_pkey" PRIMARY KEY, btree (email, tagname)
     "contacts_tags_tagname" btree (tagname)
Foreign-key constraints:
     "contacts_tags_email_fkey" FOREIGN KEY (email) REFERENCES
contacts(email) ON UPDATE CASCADE ON DELETE CASCADE
     "contacts_tags_tagname_fkey" FOREIGN KEY (tagname) REFERENCES
tags(tagname) ON UPDATE CASCADE ON DELETE CASCADE

                   Table "public.tags"
   Column   |            Type             |       Modifiers
-----------+-----------------------------+-----------------------
  tagname   | text                        | not null
  createdat | timestamp without time zone | not null default now()
Indexes:
     "tags_pkey" PRIMARY KEY, btree (tagname)

                 Table "public.segments"
    Column  |            Type             |   Modifiers
-------------+-----------------------------+---------------------
segmentid  | integer                     | not null default
nextval('segments_segmentid_seq'::regclass)
segmentname| text                        | not null
createdat  | timestamp without time zone | not null default now()
Indexes:
     "segments_pkey" PRIMARY KEY, btree (segmentid)

   Table "public.segments_tags"
   Column   |  Type   | Modifiers
-----------+---------+----------
  segmentid | integer | not null
  tagname   | text    | not null
  tagtype   | integer | not null
Indexes:
     "segments_tags_pkey" PRIMARY KEY, btree (segmentid, tagname)
Foreign-key constraints:
     "segments_tags_segmentid_fkey" FOREIGN KEY (segmentid) REFERENCES
segments(segmentid) ON UPDATE RESTRICT ON DELETE CASCADE
     "segments_tags_tagname_fkey" FOREIGN KEY (tagname) REFERENCES
tags(tagname) ON UPDATE CASCADE ON DELETE CASCADE


Regards,
-- a.