Re: Copy Data Question
От | DataIntellect |
---|---|
Тема | Re: Copy Data Question |
Дата | |
Msg-id | 1151018456.v2.fusewebmail-164106@f обсуждение исходный текст |
Ответ на | Copy Data Question (<operationsengineer1@yahoo.com>) |
Список | pgsql-novice |
----- Original Message ----- Subject: Re: [NOVICE] Copy Data Question From: operationsengineer1@yahoo.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
В списке pgsql-novice по дате отправления: