On Wed, Oct 1, 2008 at 6:37 AM, Rafael Domiciano <[EMAIL PROTECTED] > wrote:
> Hi there, > The operation "on_duplicate_key_update" is in implementation on the new > version of Postgres :) > The only way (or, at least, the best way... I think) to do what you want is > using a temporary table... let's see: > > /* Creating the structure of the first table (table_01)... You can do it > the way you like */ > create table temp_01 as > (select * from table_01 limit 1); > CREATE TABLE temp_01( LIKE table_01 ); OR CREATE TABLE temp_01 as select * from table_01 where 1 = 2; These statements would avoid the following TRUNCATE command. (mail follows at the end) > TRUNCATE TABLE table_01; > > /* COPY */ > COPY temp_01 FROM '/tmp/table'; > > /* Insert the values */ > insert into table_01 a > where not exists > (select 1 from temp_01 b > where > a.cod_serial = b.cod_serial) > > /* Or you could do like this */ > delete from temp_01 a > where exists > (select 1 from table_01 b > where > a.cod_serial = b.cod_serial) > > I hope being helpful. > > Best Regards, > > Rafael Domiciano > Postgres DBA > > 2008/9/30 Glenn Gillen <[EMAIL PROTECTED]> > > Hey all, >> >> I've got a table with a unique constraint across a few fields which I >> need to regularly import a batch of data into. Is there a way to do it >> with COPY without getting conflicts on the unique contraint? I have no >> was of being certain that some of the data I'm trying to load isn't in >> the table already. >> >> Ideally I'd like it to operate like MySQL's on_duplicate_key_update >> option, but for now I'll suffice with just ignoring existing rows and >> proceeding with everything else. >> >> Thanks, >> >> For your current requirement, you can create a trigger on the table, which silently rejects the duplicate rows. To implement UPDATE ON DUPLICATE, you can add additional logic to the same trigger and update the row already present. Scour these archives, you'll find this topic discussed in detail earlier. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
