From: Richard Broersma Jr <rabroersma@yahoo.com>
To: PostgreSQL Admin <postgres@productivitymedia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Constraints for grouping
Date: Mon, 3 Sep 2007 13:35:07 -0700 (PDT)
Message-ID: <602367.49974.qm@web31801.mail.mud.yahoo.com> (raw)
In-Reply-To: <46DC5309.8090604@productivitymedia.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, postgres@productivitymedia.com
Subject: Re: Constraints for grouping
In-Reply-To: <602367.49974.qm@web31801.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox