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

Re: fast insert-if-key-not-already-there

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: fast insert-if-key-not-already-there
Date: 2008-08-04 15:55:32
Message-ID: g778pk$2rq$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-sql
Patrick Scharrenberg, 04.08.2008 17:51:
> Hi!
> 
> I have to do much inserts into a database where the key most often is
> already there.
> My current approach is to query for the key (ip-address), and if the
> result is null I do the insert.
> For every IP-Address I need the ip_addr_id from the same table.
> 
> [...]
>
> Now I'm wondering if there is a better solution, since I'm doing ~20
> inserts at once and every time I'm doing single lookup's for the IDs.

If you know that most of the time the record is already there, I simply execute the UPDATE, then check how many rows were updated. If that returns zero, I'll send the INSERT 

Regards
Thomas





In response to

pgsql-sql by date

Next:From: Richard BroersmaDate: 2008-08-04 15:59:58
Subject: Re: fast insert-if-key-not-already-there
Previous:From: Patrick ScharrenbergDate: 2008-08-04 15:51:27
Subject: fast insert-if-key-not-already-there

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