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
