DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=Exd/OYsYvffFjP9Yu4Kx+8+MqDRatgVEanLxHDPtnNfAg5fYh9jtqcEQoqtL5Ke0GXMcb5q365nQgMkA1qR0YLCEncIrJHGer5FIlzs4dwP7m6eZ/9pLt5f+1DtOvfI9N8PqO1qX1HwhDl7hVq1eZI+vP5WzwYM+/ow5tOeL4cg=;
Date: Mon, 3 Sep 2007 13:35:07 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Constraints for grouping
To: PostgreSQL Admin <postgres@productivitymedia.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <46DC5309.8090604@productivitymedia.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <602367.49974.qm@web31801.mail.mud.yahoo.com>

--- PostgreSQL Admin <postgres@productivitymedia.com> wrote:
> I have a question that I've thought in my head about using triggers, but
> I figure to ask people that do SQL more than I.  So, I have a table that
> I want two columns.
> 
> (1) A featured column which is for only 1 row, once it switched to
> another row than all other rows must be false
> 
>              title              | author_id | categories | featured
> --------------------------------+-----------+------+-----
>  Thierry Beta Release           |         3 |         11 | True
>  Different Approach  |         3 |         11 |
>  Ottawa Does Not Heart Hip-Hop  |         3 |         11 |
> 
> (2) A featured column by category and only allow category_feature equal
> to the number of categories.
> 
> Is SQL the way to go (and how - ideas), or should I use python for the
> heavy lifting?

Are you saying that you have two featured columns? One column for category features and the other
for article feature?

If I understand you correctly, Item 1 can be achieved nicely with partial indexes.
for example, if you only want to allow one row in the entire table to have "feature"= true:

CREATE INDEX Only_one_row_true
          ON Your_table ( featured )
       WHERE featured = true;

Or if you want to only allow 1 featured article per catagory then:

CREATE INDEX Only_one_row_true_per_catagory
          ON Your_table ( catigories, featured )
       WHERE featured = true;

This will ensure that the sum( featured = true ) <= sum( unique( catagories )).

Regards,
Richard Broersma Jr.