Re: [Gnumed-devel] was: Postgres method / expression help to delete related records

[Jim Busser]

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