Обсуждение: fast insert-if-key-not-already-there

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.


Something like this:

CREATE TABLE ip_addresses ("ip_addr_id"    serial    NOT NULL,"ip_addr"        inet    UNIQUE NOT NULL
PRIMARY KEY(ip_addr);
);

CREATE OR REPLACE FUNCTION update_Addresses(v_ip_addresses         inet[]
) RETURNS void AS $$
DECLAREv_ip_addr         INET;v_ip_addr_id     INTEGER    := 0 ;v_ip_addr_ids    INTEGER[];
BEGINFOR i IN 1..( array_upper( v_ip_addresses, 1 ) )::integer LOOP    v_ip_addr = v_ip_addresses[i];
    -- check if ip_addr exists and append if not    SELECT ip_addr_id FROM ip_addresses WHERE ip_addr=v_ip_addr INTO
v_ip_id;   IF v_ip_id IS NULL THEN        INSERT INTO ip_addresses ( ip_addr ) VALUES( v_ip_addr ) RETURNING
 
ip_addr_id INTO v_ip_id ;    END IF;        
    v_ip_addr_ids = array_append(v_ip_addr_ids, v_ip_addr_id);
END LOOP;
END;
$$ LANGUAGE 'plpgsql' STRICT;

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.

regards
patrick

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
wereupdated. If that returns zero, I'll send the INSERT 
 

Regards
Thomas

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