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

On Mon, Aug 4, 2008 at 8:51 AM, Patrick Scharrenberg <pittipatti@web.de> wrote:


> 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.

>                        INSERT INTO ip_addresses ( ip_addr ) VALUES( v_ip_addr ) RETURNING
> ip_addr_id INTO v_ip_id ;


another option is to only insert if the addresses if they do not yet
exist.  You might have to rethink some of your other logic however:

INSERT INTO Ip_addresses ( ip_addr )      SELECT ip_addr         FROM ( VALUES ( v_ip_addr )) AS A( ip_addr )   LEFT
JOINIp_addresses AS B              ON A.ip_addr = B.ip_addr       WHERE B.ip_addr IS NULL;
 


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug