Not quite sure how to answer this, but one thought does occur to me: I was perhaps assuming that an override table would override an entire record in the 'original' table(that is what we are doing), and we require that critical fields in the override field be NOT NULL (and in some cases, provide DEFAULT values). Will that help?
-----Original Message----- From: Michael Burke [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 9:55 AM To: Lane Van Ingen Cc: PGSQL-SQL Subject: Re: [SQL] Database with "override" tables Lane Van Ingen wrote: >I think I have a similar situation involving the naming of assets, where >the usual asset description is used, but users can enter a description in >a separate table which 'overrides' the original name with a name that is >more familiar to the individual. > >IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two >select statements, like this: > select <override values> from foo1 > union > select <normal values> from foo2 > where <record not in foo1>; > >Hope this helps. > > > That almost works, and it is a much cleaner query than I had before. However, there's a possibility that some columns in the overridden table are NULL (indicating that the original value should be used). So, a particular asset may contain a description and price; the price may be NULL, meaning the read-only value should be used, but the user may have attached a special description as we previously outlined. What I'm looking for is the ability to, perhaps, "overlay" foo2 onto foo1, joined on foo1_id. Then, NULL values in foo2 become "transparent" and we see the foo1 values behind them. Presently I am using COALESCE() for every pair individually, ie. COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ... and then doing a FULL JOIN. This works. I'm starting to wonder if it's worth the extra hassle, I may just use your suggested UNION method instead. Thanks again, Mike. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
