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');
页:
[1]