MySQL中on duplicate key update 在PostgesSQL中的实现方式

 

MySQL中on duplicate key update username = VALUES(username),maxdate = VALUES(maxdate),

在PostgresSQL中如何实现呢?

 

MySQL中的语句:

insert into db select distinct username ,mindate,maxdate from test_table on duplicate key update username = VALUES(username),maxdate = VALUES(maxdate);

 

PostgresSQL中实现:

步骤一:创建表

CREATE TABLE db (username TEXT, userid TEXT ,mindate TEXT ,maxdate TEXT);

 

步骤二:创建唯一索引

CREATE UNIQUE INDEX username_index ON db (username);

 

步骤三:创建更新函数

CREATE FUNCTION merge_db(uname TEXT, uid TEXT ,mind TEXT , maxd TEXT) RETURNS VOID AS

$$

BEGIN

    LOOP

        -- first try to update the username

        UPDATE db SET maxdate = maxd WHERE username = uname;

        IF found THEN

            RETURN;

        END IF;

        -- not there, so try to insert the username

        -- if someone else inserts the same useridconcurrently,

        -- we could get a unique-useridfailure

        BEGIN

            INSERT INTO db(username,userid,mindate,maxdate) VALUES (uname, uid, mind,maxd);

            RETURN;

        EXCEPTION WHEN unique_violation THEN

            -- do nothing, and loop to try the UPDATE again

        END;

    END LOOP;

END;

$$

LANGUAGE plpgsql;

 

步骤四:测试案例

select merge_db('user_one','279470','20101011','20101011');

select merge_db('user_two,'279470','20101011','20101012');

select merge_db('user_one','279470','20101011','20101012');