Oracle2PG sequence(序列）问题汇总

迁移PostgreSQL的Sequence（序列）问题

https://masuit.net/2042?t=0HN6FQRQT1K6P

如何快速获取同步序列的SQL

有些项目中数据量比较少，在迁移过程；表数据迁移过去；但是序列需要重置下；接下来讲到，引用自：https://www.cnblogs.com/lottu/p/14330474.html

SELECT concat('SELECT setval(''"',c.relname,'"'', MAX("',SPLIT_PART(c.relname, '_', 2),'")) FROM "',SPLIT_PART(c.relname, '_', 1),'";') FROM pg_class c WHERE c.relkind = 'S';

迁移postgres时序列错误脚本修复

引用自： https://www.cnblogs.com/Raspberry-zx/p/12702310.html

迁移postgres数据库的时候,有时候会出现序列与数据库值不匹配的现象.参考了https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync之后,

改写了一下脚本,执行后可正常使用了.

do --check seq not in sync
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    Select
      DISTINCT(constraint_column_usage.table_name) as tablename,
      constraint_column_usage.column_name as idname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as seqname,
            columns.table_schema as schamename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null
   ) loop
    execute format('select last_value from %I.%s',_r.schamename,_r.seqname) into _i;
    execute format('select max(%I) from %I.%I',_r.idname,_r.schamename,_r.tablename) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.tablename||'_Id_seq',' from:',_i,' to:',_m+1);
      execute format('alter sequence %I.%s restart with %s',_r.schamename,_r.seqname,_m+1);
    end if;
  end loop;

end;
$$
;