X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 12A439DCC94 for ; Wed, 7 Dec 2005 10:14:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04046-05 for ; Wed, 7 Dec 2005 10:14:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtpout.eastlink.ca (smtpout.eastlink.ca [24.222.0.30]) by postgresql.org (Postfix) with ESMTP id 9914E9DCCFB for ; Wed, 7 Dec 2005 10:14:34 -0400 (AST) Received: from ip04.eastlink.ca ([24.222.10.20]) by mta01.eastlink.ca (Sun Java System Messaging Server 6.2-4.03 (built Sep 22 2005)) with ESMTP id <0IR400D1VSWCS380@mta01.eastlink.ca> for pgsql-sql@postgresql.org; Wed, 07 Dec 2005 10:14:38 -0400 (AST) Received: from blk-137-99-147.eastlink.ca (HELO [192.168.1.3]) ([24.137.99.147]) by ip04.eastlink.ca with ESMTP; Wed, 07 Dec 2005 10:11:12 -0400 Date: Wed, 07 Dec 2005 10:11:03 -0400 From: Michael Burke Subject: Re: Database with "override" tables In-reply-to: To: Lane Van Ingen Cc: PGSQL-SQL Message-id: <4396ED77.5000608@engtech.ca> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT X-Accept-Language: en X-BrightmailFiltered: true X-Brightmail-Tracker: AAAAAA== References: User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20051007 Debian/1.7.12-1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/56 X-Sequence-Number: 23598 Lane Van Ingen wrote: >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? > > I fought with the UNION method for a while before backing off to my extremely-long-query system with a FULL JOIN: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; It's not so bad until you get to cross-reference other tables that have override tables, but then it just becomes pretty ugly. So I'll stick with that for now. If the override table did indeed override entire records in the original table, the UNION would have worked beautifully and probably made things cleaner. Thanks for the advice. Mike.