【MySQL\Oracle\PostgreSQL】迁移到openGauss数据出现的问题解决方案

问题1：序列值不自动刷新问题
下面SQL只针对单库操作以及每个序列只绑定一张表的情况

-- 自动生成的序列，设置序列值
with sequences as (select *from (select table_schema,table_name,column_name,pg_get_serial_sequence(format('%I.%I', table_schema,table_name), column_name) as auto_seq, --<<自动(string_to_array(column_default, E'\''))[2]             as col_seq   --<<手动from information_schema.columnswhere table_schema not in ('pg_catalog', 'information_schema')and column_default ILIKE 'nextval(%'and table_schema = 'usc') t
--                    where auto_seq is not null),maxvals as (select table_schema,table_name,column_name,auto_seq,col_seq,(xpath('/row/max/text()',query_to_xml(format('select max(%I) as max from %I.%I', column_name, table_schema,table_name),true, true, '')))[1]::text::bigint as max_val,(xpath('/row/cur/text()',query_to_xml(format('select last_value as cur from %I.%I',table_schema, col_seq),true, true, '')))[1]::text::bigint as cur_valfrom sequences)
select table_schema,table_name,column_name,auto_seq,col_seq,cur_val,coalesce(max_val, 0) as max_val,setval(col_seq, coalesce(max_val, 1)) --<<设置序列值
from maxvals;

问题2：
字符类型为空字符串迁移后会变成null排查，需要考虑大表问题，防止慢SQL

-- 数据库迁移出现null/空字符串 排除
with t as (select table_schema,table_name,column_name,(xpath('/row/cnt/text()',query_to_xml(format(E'select count(*) as cnt from %I.%I where %I = \'\'', table_schema,table_name, column_name),true, true, '')))[1]::text::bigint as null_cnt
from information_schema.columns
where table_schema not in ('pg_catalog', 'information_schema')and table_schema = 'usc'and is_nullable = 'NO'and udt_name in ('varchar', 'text', 'bpchar'))
select * from t where null_cnt > 0;

问题3：
时间精度问题，导致时间范围查询失败。批量调整时间字段精度

DO
$$DECLAREr RECORD;BEGINFOR r INselect table_name, column_name, udt_namefrom information_schema.columnswhere table_schema not in ('pg_catalog', 'information_schema')and table_schema = 'usc'and udt_name in ('timestamp','timestamptz')LOOPEXECUTE 'ALTER TABLE ' || r.table_name || ' ALTER COLUMN ' || r.column_name || ' TYPE ' || r.udt_name ||'(0)';END LOOP;END
$$;