Re: Copy Data Question

----- Original Message -----
Subject: Re: [NOVICE] Copy Data Question
From: operationsengineer1(at)yahoo(dot)com
Date: Thu, June 22, 2006 15:54

> > how can i add *only* nonexisting entries to my db
> > tables (iow, if it is one of the 100+ there now, i
> > don't want to mess with it)?
>
> I will take a stab at the easy question. Out of test
> solutions, this is the one that I know.
>
> 1. create a temp table using destination table
> schema using the LIKE clause.
>
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
>
> 2. import external data into the temp table.
>
> 3. select into destinationtable from temptable where
> temptable.primarykey not exists
> ( select destinationtable.primarykey
> from destination table
> );

Richard, thanks. in my case, the primary key isn't
relevant, however, the product_number is (it is
unique). iiuc, in my case i should use product_number
instead of primarykey.

it makes sense - thanks for the enlightenment.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

A few additional thoughts:

Create a temp table that is the same struct as the tartet table. Insert
the 100+ rows into this new table. Next run an insert into <target_tab>
from <temp_table> where <row values> not in (select <row_value> from
<target_table>)

If you need to check multiple columns for uniqueness in the target tab
then compare like this:

insert into <target_tab>
from <temp_table>
where col_a || col_b || col_c || col_d || col_e
not in (select col_a || col_b || col_c || col_d || col_e from
<target_table>)

=======================================
also, how do i update multiple tables with related info?

Try creating a series of sql stmts that insert the data in the correct
order for the constraints and wrap it in a transaction so you can rollback
if something goes wrong.

If you created the constraints for the related tables as deferrable (not
the default) then you could run:

begin work;
set constraints all deferred;
<insert / update statements in any order>
commit;

/Kevin