From: Michael Burke <michael@engtech.ca>
To: Lane Van Ingen <lvaningen@esncc.com>
Cc: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: Database with "override" tables
Date: Tue, 06 Dec 2005 10:55:12 -0400
Message-ID: <4395A650.9000607@engtech.ca> (raw)
In-Reply-To: <EKEMKEFLOMKDDLIALABIAEMNCFAA.lvaningen@esncc.com>
References: <EKEMKEFLOMKDDLIALABIAEMNCFAA.lvaningen@esncc.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael@engtech.ca, lvaningen@esncc.com
Subject: Re: Database with "override" tables
In-Reply-To: <4395A650.9000607@engtech.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox