对 postgres 中的唯一违规执行删除或更新

Question

在 Unique_violation 异常中，如何更新或删除引发异常的行

表代码并插入

create table test
(
id serial not null,
str character varying NOT NULL,
is_dup boolean DEFAULT false,
CONSTRAINT test_str_unq UNIQUE (str)
);

INSERT INTO test(str) VALUES ('apple'),('giant'),('company'),('ap*p*le');

函数

CREATE OR REPLACE FUNCTION rem_chars()
  RETURNS void AS
$BODY$

BEGIN
begin 
update test set str=replace(str,'*','');
EXCEPTION WHEN unique_violation THEN
--what to do here to delete the row which raised exception or
--to update the is_dup=true to that row 
end;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION rem_chars() OWNER TO postgres;

Answer 1

-- 这将向您显示所有潜在的按键冲突

SELECT a.id, a.str, b.id , b.str
FROM test a, test b
WHERE a.str = replace(b.str,'*','')
AND a.id < b.id;

-- 这将删除它们

DELETE FROM test WHERE id IN (
  SELECT b.id
  FROM test a, test b
  WHERE a.str = replace(b.str,'*','')
  AND a.id < b.id
);

Answer 2

我认为唯一的解决方案是分两步完成：

UPDATE test 
  SET str = replace(str,'*','')
  WHERE str NOT IN (SELECT replace(str,'*','') FROM test);

UPDATE test
  SET is_dup = true
  WHERE str IN (SELECT replace(str,'*','') FROM test);

至少我想不出更有效的方法。