DO $$
DECLARE
i integer;
page_size integer = 1000;
total_count integer;
total_page integer;
total_count_bak bigint;
BEGIN
SELECT COUNT(*) INTO total_count FROM bos_data_flag_info;
total_page = total_count / page_size;
IF total_count % 10 > 0 THEN
total_page = total_page + 1;
END IF;
RAISE NOTICE '共有 % 页,每页 % 条,共有 % 条,', total_page, page_size ,total_count;
FOR pageNum IN 1..total_page LOOP
RAISE NOTICE '执行第: % 页', pageNum;
INSERT INTO bos_data_flag_info_bak SELECT * FROM bos_data_flag_info i
-- WHERE NOT EXISTS ( -- 有问题,会导致漏数据
-- SELECT 1 FROM bos_data_flag_info_bak b WHERE b.id = i.id )
ORDER BY ID ASC LIMIT page_size OFFSET (pageNum - 1) * (page_size) ;
-- COMMIT非常关键,不然整个for循环就是1个大事务
-- COMMIT非常关键,不然整个for循环就是1个大事务
-- COMMIT非常关键,不然整个for循环就是1个大事务
COMMIT;
END LOOP;
-- 开始校验总数
SELECT COUNT(*) INTO total_count_bak FROM bos_data_flag_info_bak;
IF total_count_bak = total_count THEN
RAISE NOTICE '数据条数一致,新老表均有 % 条', total_count_bak ;
ELSE
RAISE NOTICE '数据条数不一致';
END IF;
END;
$$ LANGUAGE plpgsql;
06-14