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

Re: perform 1 check vs exception when unique_violation

From: "Robert Haas" <robertmhaas(at)gmail(dot)com>
To: "Anton Bogdanovitch" <poison(dot)box(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: perform 1 check vs exception when unique_violation
Date: 2008-12-30 14:50:55
Message-ID: 603c8f070812300650i226f032co8db1f25eb352e4b8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Dec 30, 2008 at 5:41 AM, Anton Bogdanovitch
<poison(dot)box(at)gmail(dot)com> wrote:
> I have to insert rows to table with 95% primary key unique_violation.

If you're inserting a lot of rows at once, I think you're probably
better off loading all of the data into a side table that does not
have a primary key, and then writing one statement to remove the
duplicates and do all the inserts at once.

INSERT INTO main (name, created)
SELECT
   s.name, CURRENT_TIMESTAMP
FROM
   (SELECT DISTINCT ON (name) FROM sidetable) s  -- nuke duplicate
names within sidetable
   LEFT JOIN main m ON s.name = m.name
   WHERE m.name IS NULL;   -- nuke names in sidetable that are already in main

I've usually found that any kind of loop in PL/pgsql is mind-numbingly
slow compared to doing the same thing as a single query.

...Robert

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-12-30 15:39:21
Subject: Re: Big index sizes
Previous:From: Robert HaasDate: 2008-12-30 14:43:28
Subject: Re: Big index sizes

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