pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConstraints for grouping
4+ messages / 2 participants
[nested] [flat]
* Constraints for grouping
@ 2007-09-03 18:31 PostgreSQL Admin <postgres@productivitymedia.com>
0 siblings, 1 reply; 4+ messages in thread
From: PostgreSQL Admin @ 2007-09-03 18:31 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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?
Thanks for any input,
J
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Constraints for grouping
@ 2007-09-03 20:35 Richard Broersma Jr <rabroersma@yahoo.com>
parent: PostgreSQL Admin <postgres@productivitymedia.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Broersma Jr @ 2007-09-03 20:35 UTC (permalink / raw)
To: PostgreSQL Admin <postgres@productivitymedia.com>; +Cc: pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Constraints for grouping
@ 2007-09-03 20:40 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Broersma Jr @ 2007-09-03 20:40 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; PostgreSQL Admin <postgres@productivitymedia.com>; +Cc: pgsql-sql
OOPS!
--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> CREATE UNIQUE 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 UNIQUE INDEX Only_one_row_true_per_catagory
> ON Your_table ( catigories, featured )
> WHERE featured = true;
I forgot the unique part of the DDL.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Constraints for grouping
@ 2007-09-04 00:21 PostgreSQL Admin <postgres@productivitymedia.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: PostgreSQL Admin @ 2007-09-04 00:21 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
> --- Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
>> CREATE UNIQUE 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 UNIQUE INDEX Only_one_row_true_per_catagory
>> ON Your_table ( catigories, featured )
>> WHERE featured = true;
>>
>
> I forgot the unique part of the DDL.
>
> Regards,
> Richard Broersma Jr.
>
>
Thanks for the information. I will check it out and get back to you.
Thanks again,
J
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-09-04 00:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-03 18:31 Constraints for grouping PostgreSQL Admin <postgres@productivitymedia.com>
2007-09-03 20:35 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-09-03 20:40 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-09-04 00:21 ` PostgreSQL Admin <postgres@productivitymedia.com>
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