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

getfuture

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');

renxiao2003

学习了。