Pi

January 15, 2010

Upsert, Merge Postgres

Filed under: Uncategorized — rqmedes @ 2:47 am

Postgres doesn’t have  UPSERT or ON DUPLICATE KEY functionality  so if you want to perform an update  if not exists insert in one statement you need to write a merge function.

The manual provides a  good example.

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

However this can become time consuming to write on large/many  tables.

Here is a function that will generate a merge function skeleton for your tables

First create a text aggregate.

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

And here is the function.

CREATE OR REPLACE FUNCTION admin.table_merge(_name text)
RETURNS text AS
$BODY$
Declare sql text;
Declare fsql text;
Declare isql text;
Declare usql text;
Declare psql text;
     BEGIN
	sql = 'Create or replace function admin.merge_'  || _name || '('; 

	SELECT INTO fsql,usql,isql,psql
	        textcat_all(('_'|| column_name || ' ' || data_type) || ', '),
	        textcat_all((column_name || '= _' || column_name) || ', '),
	        textcat_all((column_name) || ', '),
	        textcat_all(('_' || column_name) || ', ')
	FROM information_schema.columns
	WHERE  table_name = _name;

	sql = sql || substring(fsql, 0, length(fsql)-1) || ') RETURNS VOID AS
	$MERGE$
	BEGIN
	   LOOP
	      UPDATE ' || _name || ' SET ' || substring(usql, 0, length(usql)-1) ||  ' Where id = _id; '
	  || ' IF FOUND THEN
	          RETURN;
	      END IF; '
	  || 'BEGIN
	      INSERT INTO ' || _name || '(' || substring(isql, 0, length(isql)-1) || ') Values
	      (' || substring(psql, 0, length(psql)-1) || ');
	      RETURN;
	      EXCEPTION WHEN unique_violation THEN
	      END;
	   END LOOP;
	END;
	$MERGE$
	LANGUAGE ''plpgsql''
        COST 100;';
	return sql;
     END;
$BODY$
  LANGUAGE 'plpgsql'
  COST 100;
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: