Re: Problem with left join when moving a column to another table - Mailing list pgsql-general

On Thu, 2013-06-20 at 16:22 -0700, David Johnston wrote:

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.

David,

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.