On Thu, 2013-06-20 at 16:22 -0700, David Johnston wrote:
David,Jason Long-2 wrote >> Jason Long-2 wrote > > > There is a unique constraint on the real price table. I hadn't thought > of how I will enforce the constraint across two tables. > size_id and area_id will have to be unique across both > t_price_base_table and t_price_original_with_area_id. I will want to > drop area_id from t_price_original_with_area_id. > > What is the best way to implement the cross table unique constraint? Don't. If size+area is a unique constraint then there should be a table that defines valid pairs and creates a PRIMARY KEY over them. Per my original comment your issue isn't JOINs (well, your biggest issue anyway) but your model. The fact that you couldn't write a good query simply exposed the problems in the model. This is not uncommon. I would need a lot more information (and time) than I have now to offer any design thoughts on your schema; though I do find the unique constraint over size+area to be unusual - as well as using that as a foreign key from the item table. You haven't specified the domain for this model but using homes as an example I would use a 'model' table with "model_id, size, area" as columns. A particular house would then link in "model" and "price". You could possibly further restrict that certain models can only sell for certain prices if necessary - in which case you would have "model_price" and possibly "house_model_price" tables (the later could be an FK). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-left-join-when-moving-a-column-to-another-table-tp5760187p5760220.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I really appreciate your help. I had not used WITH or any of the syntax you showed me before. Pretty cool.
I normally just write a bunch of views to build complex queries.
Does the syntax you showed me have performance benefits vs joining a bunch of views together?
I spent way to much time trying to get the query to work, and all I needed to do was write a view
create or replace view price.v_offerprice_pipe as
select op.id,
op.price,
op.active,
op.stditem,
op.version,
opp.size_id,
opp.weight_id,
opp.grade_id,
opp.endfinish_id,
opp.manufacturer_id,
opp.condition_id,
opp.area_id
from price.t_offerprice_pipe opp
join price.t_offerprice op on op.id=opp.id;
This allowed me to move (price, active, stditem, version) to the base table without breaking any of my views with very minimal change to the view definitions.
I just had to replace any references to price.t_offerprice_pipe with the view price.v_offerprice_pipe in any of the views that were complaining about dropping the columns.
I decided not to move area_id to the base table for now. Without being able to properly do a cross table unique constraint, it will stay where it is currently.
