September 20th, 2007 by depesz | Tags: | 3 comments »
Did it help? If yes - maybe you can help me?

all of you know copy command. it's fast for inserting new data. but can it be used it we want to insert or update rows?

sure, it will be more than one command, but it's perfectly doable. here's how.

let's assume we have a table with user data (id, username, password).

we import it once, let's say 10000 accounts. then after time we get new import – 12000 accounts. and a task – new records should be inserted, and if somebody from already-imported users has different password – password from import should be used.

another thing – we can't remove users before import – reason is simple, there are other tables that have foreign keys to users.

so, what to do?

let's say our import data contains only username and password.

we import it to temp table:

create temp table new_import (username text, password text);
copy new_import (username, password) from '/tmp/';

now we have the data in side table, so let's update the users in case password is different:

update users as u
set password = n.password
from new_import n
where n.username = u.username and n.password <> u.password;

and now, copy record from new_import users – but only about users which are not yet in users table:

insert into users (username, password)
select n.username, n.password
from new_import n left outer join users u on n.username = u.username
where u.username is null;

and now cleaning:

drop table new_import;

and that's all. of course proper indexing will be needed to make it fast. simple, fast and effective.

  1. 3 comments

  2. # figther_from_brazil!
    Sep 20, 2007

    Cool!!! Another cool Article!

  3. # figther_from_brazil!
    Sep 20, 2007

    Your Tips are so greate that i would like to translate in portuguese? may i?

  4. Sep 20, 2007

    sure, no problem at all.

Sorry, comments for this post are disabled.