Pages

Friday, June 15, 2012

Reset all sequences' current values + PostgreSQL

Following function allows to reset starting values of all sequences in particular PostgreSQL schema. In data migration process, this function can be used to reset all sequence values after importing data to destination database.
CREATE OR REPLACE FUNCTION seq_update()
  RETURNS void AS
$BODY$
Declare
tab1 varchar;
col1 varchar;
seqname1 varchar;
maxcolval integer;
ssql varchar;
BEGIN
FOR tab1, col1, seqname1 in Select distinct constraint_column_usage.table_name, 
constraint_column_usage.column_name,
replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','')
 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 
AND constraint_column_usage.table_name not in ('user', 'usermodulespages')
--AND constraint_column_usage.table_name = 'role'
order by 1 
LOOP
ssql := 'select max(' || col1 || ') from ' || tab1 ;
RAISE NOTICE 'SQL : %', ssql;
execute ssql into maxcolval;
RAISE NOTICE 'max value : %', maxcolval;
EXECUTE 'alter sequence ' || seqname1 ||' restart  with ' || maxcolval;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
Following command can be used to run the function
select * from seq_update()

3 comments:

  1. Thanks for the nice function. It really helped me during a data only restore from production to development.

    Here is a revised version to handle a few other cases in the initial select as well as null sequence values that are brought about by an empty table.

    Declare
    tab1 varchar;
    col1 varchar;
    seqname1 varchar;
    maxcolval integer;
    ssql varchar;
    BEGIN
    FOR tab1, col1, seqname1 in Select distinct constraint_column_usage.table_name,
    constraint_column_usage.column_name,
    replace(replace(replace(replace(replace(replace (replace (columns.column_default, '''::text)', ''),
    '''::regclass)', ''), 'nextval((''', ''), 'nextval(''', ''), '"::regclass)', ''), '"public"."', ''), '"', '')
    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
    AND constraint_column_usage.table_name not in ('user', 'usermodulespages')
    --AND constraint_column_usage.table_name = 'role'
    order by 1
    LOOP
    ssql := 'select max(' || col1 || ') from ' || tab1 ;
    RAISE NOTICE 'SQL : %', ssql;
    execute ssql into maxcolval;
    RAISE NOTICE 'max value : %', maxcolval;
    IF maxcolval IS NOT NULL THEN
    EXECUTE 'alter sequence ' || seqname1 ||' restart with ' || maxcolval;
    ELSE
    RAISE NOTICE 'Resetting sequence % with 1', seqname1;
    EXECUTE 'alter sequence ' || seqname1 || ' restart with 1';
    END IF;
    END LOOP;
    END;

    ReplyDelete
  2. Hey,

    Thanks a lot for the function, it works great,

    I made 2 modifications on mine :
    * Adding 1 to the MAX() value (I got errors on the first entry like "ERROR: duplicate key value violates unique constraint "codes_distribues_pkey"")
    * Using COALESCE to avoid errors in case of empty tables (I believe it's just another way than the one Charles Boening proposed)

    Here is the updated function :

    CREATE OR REPLACE FUNCTION seq_update()
    RETURNS void AS
    $BODY$
    Declare
    tab1 varchar;
    col1 varchar;
    seqname1 varchar;
    maxcolval integer;
    ssql varchar;
    BEGIN
    FOR tab1, col1, seqname1 in Select distinct constraint_column_usage.table_name,
    constraint_column_usage.column_name,
    replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','')
    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
    AND constraint_column_usage.table_name not in ('user', 'usermodulespages')
    --AND constraint_column_usage.table_name = 'role'
    AND constraint_column_usage.table_name NOT IN ('ip_geo_country', 'ip_geo_local')
    order by 1
    LOOP
    ssql := 'select COALESCE(max(' || col1 || ') + 1, 1) as max from ' || tab1 ;
    RAISE NOTICE 'SQL : %', ssql;
    execute ssql into maxcolval;
    RAISE NOTICE 'max value : %', maxcolval;
    EXECUTE 'alter sequence ' || seqname1 ||' restart with ' || maxcolval;
    END LOOP;
    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE;

    ReplyDelete
  3. Thanks for the the intellectual contribution. Really Great

    ReplyDelete