On 09/11/2011 08:59 AM, Igor Chudov wrote:
I do not plan to do a lot of random writing. My current design is that my perl
scripts write to a temporary table every week, and then I do INSERT..ON
DUPLICATE KEY UPDATE.
By the way, does that INSERT UPDATE functionality or something like this exist
in Postgres?
i
You have two options:
1) write a function like:
create function doinsert(_id integer, _value text) returns void as $$
begin
update thetable set value = _value where id = _id;
if not found then
insert into thetable(id, value) values (_id, _value);
end if
end;
$$ language plpgsql;
2) use two sql statements:
-- update the existing
update realTable set value = (select value from tmp where tmp.id = realTable.id)
where exists (select value from tmp where tmp.id = realTable.id);
-- insert the missing
insert into realTable(id, value)
select id, value from tmp where not exists(select 1 from realTable where tmp.id
= realTable.id);
-Andy
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
