Re: multi-row check constraints?

On Tue, 2007-03-20 at 13:21 -0700, Angva wrote:
> Dear Postgres fans,
>
> Hi, I was wondering what is the best way to achieve a multi-row check
> constraint. For example, you have a table with two columns: ID and
> percent, no primary key. The goal is to enforce that all values of
> percent, per ID, add up to exactly 100%. I come from an Oracle
> background, and what you would probably do on Oracle is create a
> materialized view with the sum(percent) grouped by ID, then put a
> constraint on the sum column. This problem is also solvable using
> triggers, but it's messy and imposes a lot of serialization. Not to
> mention easy to get wrong.
>
> So, I've come across this problem in Postgres and was hoping someone
> could steer me in the right direction.
>

Your Oracle solution is interesting, and can indeed be implemented in
PostgreSQL in exactly the same way. Look at materialized views here:

http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

Another way to do it without using an entire materialized view is to
obtain a row level lock on the ID using SELECT ... WHERE id=123 FOR
UPDATE. To do this you need to have a table that contains all the IDs
and where id has a unique index to prevent race conditions when adding
new IDs.

What are you trying to do exactly? Why does the table have no primary
key?

Regards,
Jeff Davis