[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] was: Postgres method / expression help to delete rela
From: |
Jim Busser |
Subject: |
Re: [Gnumed-devel] was: Postgres method / expression help to delete related records |
Date: |
Sun, 04 Sep 2011 16:58:48 -0700 |
On 2011-09-04, at 11:38 AM, Karsten Hilbert wrote:
> insert into ref.consumable_substance (
> description,
> atc_code,
> amount,
> unit
> ) (
> SELECT
> description,
> atc_code,
> amount,
> unit
> FROM
> staging.subs s_subs
> WHERE
> NOT EXISTS (
> SELECT 1
> FROM
> ref.consumable_substance r_cs
> WHERE
> r_cs.description = s_subs.description
> AND
> r_cs.amount = s_subs.amount
> AND
> r_cs.unit = s_subs.unit
> LIMIT 1
> )
> );
>
> (untested)
works after I adjusted for the fact that I had created my table not in staging
but as temporary:
CREATE TEMP TABLE subs (
description text,
atc_code text,
amount numeric,
unit text
);
I saw it as an advantage that AFAICT the above would auto-drop (conserving disk
space) at the end of the session. Is it your preference (and/or good practice)
that temporary files for import be written into the staging schema, as opposed
to the temporary schema? Maybe this depends if it is desired to retain a copy
of data in staging for post-hoc trouble-shooting?
-- Jim