Skip site navigation (1) Skip section navigation (2)

[TLM] Re: How to insert on duplicate key?

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: "fdu(dot)xiaojf(at)gmail(dot)com" <fdu(dot)xiaojf(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: [TLM] Re: How to insert on duplicate key?
Date: 2007-12-25 07:15:15
Message-ID: 20071225071840.5BA5741428A@qatlm3.calidad2.pandasoftware.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 25 Dec 2007, fdu(dot)xiaojf(at)gmail(dot)com wrote:

> insert a record into a table, and when the record already 
> exists(according to the primary key), update it.

There is an example that does exactly that, 37-1, in the documentation at 
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html 
It actually does the update first and only if that fails does the insert, 
which avoids the whole duplicate key issue altogether.

> I have tried the query and update/insert way, and it was very slow when 
> more than 1 million records have been inserted. (I have more than 20 
> million records to insert.)

This may be better because it isn't doing the query first.  You may 
discover that you need to aggressively run one of the VACUUM processes 
(I'd guess regular and ANALYZE but not FULL) in order to keep performance 
steady as the number of records grows.  Anytime you update a row, that 
becomes a dead row that's still taking up space, and if you do a lot of 
those they get in the way of finding the rows that are still live.  Take a 
look at 
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html 
to get an idea of the process.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

In response to

Responses

pgsql-general by date

Next:From: 杨雪枫Date: 2007-12-25 12:43:48
Subject: [TLM] It's serious,Help!
Previous:From: fdu.xiaojf@gmail.comDate: 2007-12-25 03:25:55
Subject: Re: How to insert on duplicate key?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group