From: Andy Colson <andy@squeakycode.net>
To: Igor Chudov <ichudov@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres for a "data warehouse", 5-10 TB
Date: Sun, 11 Sep 2011 09:23:20 -0500
Message-ID: <4E6CC458.4010206@squeakycode.net> (raw)
In-Reply-To: <CAMhtkAah2c4XfSec=OtgL1V51wpD=jygbZnBVBRYCV02MscebQ@mail.gmail.com>
References: <CAMhtkAbK7saRO_Tayb1nS3YLWN=Wns=Ci47qs-3YbX0T2+hkpA@mail.gmail.com>
<CAOR=d=0zmw-EGRJyYmwCTxR5g6osbFLS5i-3kzjG05j7S=1JaA@mail.gmail.com>
<CAMhtkAah2c4XfSec=OtgL1V51wpD=jygbZnBVBRYCV02MscebQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, ichudov@gmail.com
Subject: Re: Postgres for a "data warehouse", 5-10 TB
In-Reply-To: <4E6CC458.4010206@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox