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

On Mon, Aug 4, 2008 at 8:51 AM, Patrick Scharrenberg <pittipatti(at)web(dot)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 JOIN Ip_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