原来表设计没有主键导致SQL重复执行时会产生多条一模一样的主键,现在需要对原来的表建立主键,需要对ID重复的数据进行删除,只保留一条虽然数据完全一致,但好在pgsql数据库提供了ctid的隐藏列,根据此列可以把只保留最小ctid的相同数据行。于是写了以下存储过程来处理。方便多了。再也不错这种问题了。
CREATE OR REPLACE FUNCTION delete_repeat(tablename varchar)
RETURNS bigint AS
$BODY$
DECLARE
rec RECORD;
sql varchar;
BEGIN
RAISE INFO '处理表名:%', tablename;
IF NOT exists(select * from pg_class where relname = tablename) then
RAISE INFO '处理表不存在:%', tablename;
RETURN -1;
END IF;
FOR rec in EXECUTE('select id from '||tablename||' group by id having count(id) > 1') LOOP
RAISE INFO 'id:%', rec.id;
sql := 'delete from ' || tablename || ' where id = ''' || rec.id|| ''' and
ctid <> (select min(ctid) from ' || tablename || ' where id = ''' || rec.id|| ''')';
execute sql;
RAISE INFO 'sql:%', sql;
END LOOP;
RETURN 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
select delete_repeat('t_menu');