Re: Insert Question - Mailing list pgsql-novice

On Jun 26, 2007, at 11:39 , Phillip Nelson wrote:

> I have an ivfm_tmp table and am trying to insert all records from
> that table into inventory_data if they do not already exist.
> Furthermore, I need to join commissions from ivfmx_tmp table if
> there are existing records for that id.

> I need to default the ivfmx_tmp.comm1, comm2, and comm3 fields to
> 0.00 if there is no join found and insert the record into
> inventory_data. Is this possible?

When two tables are JOINed, the result includes only rows for which
the JOIN condition is true. As you've discovered, when joining
ivmf_tmp to ivfmx_tmp USING (id), there will be no rows returned from
ivfm unless there are corresponding rows in ivfmx_tmp (and vice versa).

Note I'm using USING (id) rather than ON (ivfm_tmp.id = ivfmx_tmp.id)
which has the nice property of returning only one id column rather
than two (one for each ivfm_tmp and ivfmx_tmp).

What you're looking for is a LEFT JOIN. For A LEFT JOIN B, all rows
of A will be returned. Corresponding rows in B will also be returned,
and when there are no corresponding rows in B, NULL will be returned
in place of B's columns.

So, we've got:

INSERT into inventory_data
        (id, cat, desc, seq, type, bcode1, comm1, comm2, comm3)
SELECT ivfm_tmp.id
        , ivfm_tmp.cat
        , ivfm_tmp.desc
        , ivfm_tmp.seq
        , ivfm_tmp.type
        , ivfm_tmp.bcode1
        , ivfmx_tmp.comm1
        , ivfmx_tmp.comm2
        , ivfmx_tmp.comm3
FROM ivfm_tmp
LEFT JOIN ivfmx_tmp USING (id)
WHERE ivfm_tmp.id NOT IN (
       SELECT inventory_data.id
       FROM inventory_data);

It's a good habit to list the columns in an INSERT statement as I've
done here. This helps insulate the INSERT statement from DDL that may
occur on the table you're inserting into as well as being self-
documenting.

Now, you want to insert 0.00 when comm1, comm2, and comm3 when
there's no corresponding rows in ivfmx_tmp -- in otherwords, when
comm1, comm2, and comm3 are NULL. The COALESCE function can help you
out here. COALESCE returns its first argument which is not NULL.

INSERT into inventory_data
        (id, cat, desc, seq, type, bcode1, comm1, comm2, comm3)
SELECT ivfm_tmp.id
        , ivfm_tmp.cat
        , ivfm_tmp.desc
        , ivfm_tmp.seq
        , ivfm_tmp.type
        , ivfm_tmp.bcode1
        , COALESCE(ivfmx_tmp.comm1, 0.00) as comm1
        , COALESCE(ivfmx_tmp.comm2, 0.00) as comm2
        , COALESCE(ivfmx_tmp.comm3, 0.00) as comm3
FROM ivfm_tmp
LEFT JOIN ivfmx_tmp USING (id)
WHERE ivfm_tmp.id NOT IN (
       SELECT inventory_data.id
       FROM inventory_data);

I think you could possibly also handle this with DEFAULT 0.00 on the
comm1, comm2, and comm3 columns.

As an aside, column names such as comm1, comm2, and comm3 are
sometimes a sign that a table should be refactored into two
associated with a foreign key.  Without more details about your
schema, it's hard to say if that's the case here, but it may be
something you want to look into.

Hope this helps.

Michael Glaesemann
grzm seespotcode net