CODEWITHSUNDEEP
databasepostgresql-9.4
Alan Wayne
Alan Wayne

Reputation: 5394

How to reset all sequences to 1 before database migration in PostgreSQL?

(PostgreSQL 9.4)

I am in the process of migrating an older database to a new schema. After using pg_restore to acquire the new schema (without data) from my development machine, I find that some sequences do not start at 1. (I had changed multiple sequences during development to work with higher values).

Before I start the database migration, is there a programmatic way of resetting all the sequences (some of which are not primary keys) back to 1?

Thanks for any help or suggestions.

Upvotes: 9

Views: 12852

Answers (4)

Daniel Monroy
Daniel Monroy

Reputation: 1

Sometimes the sequences do not follow a pattern.
I share the following code I hope it helps

CREATE OR REPLACE FUNCTION
 restore_sequences(schema_name in varchar)
 RETURNS void AS $$
 DECLARE
    statements CURSOR FOR
        select s.sequence_schema, s.sequence_name from 
        information_schema."sequences" s
        where s.sequence_schema = schema_name
        order by s.sequence_schema asc;
BEGIN
    FOR stmt IN statements loop
        execute 'SELECT SETVAL(' || ((E'\''||(select current_database())||'.'||stmt.sequence_schema||'.'||stmt.sequence_name)||(E'\'')) || ', 1, true);';
        execute 'ALTER SEQUENCE ' || ((select current_database())||'.'||stmt.sequence_schema||'.'||stmt.sequence_name) || ' START 1';
    END LOOP;
END;
$$ LANGUAGE plpgsql
;

Upvotes: 0

Brayme Guaman
Brayme Guaman

Reputation: 175

You can do it with this sql code:

DO $$
DECLARE
i TEXT;
BEGIN
 FOR i IN (SELECT column_default FROM information_schema.columns WHERE column_default SIMILAR TO 'nextval%') 
  LOOP
         EXECUTE 'ALTER SEQUENCE'||' ' || substring(substring(i from '''[a-z_]*')from '[a-z_]+') || ' '||' RESTART 1;';    
  END LOOP;
END $$;

I read information of columns and I use regex to separate squence's name. After I make query and use EXECUTE for each sequence. This code is for all sequences of your DB.

Upvotes: 2

M. Wiśnicki
M. Wiśnicki

Reputation: 6203

You can change seq value using setval in loop, here you get all tables whith columns Id in DATA_BASE_NAME

DO $$
DECLARE
i TEXT;
BEGIN
 FOR i IN (SELECT tb.table_name FROM information_schema.tables AS tb INNER JOIN information_schema.columns AS cols ON 
        tb.table_name = cols.table_name WHERE tb.table_catalog='DATA_BASE_NAME' 
         AND tb.table_schema='public' AND cols.column_name='Id') LOOP
         EXECUTE 'SELECT setval('||'"' || i || '_Id_seq"'||',1);';

  END LOOP;
END $$;

Upvotes: 6

Alan Wayne
Alan Wayne

Reputation: 5394

This works simple enough for my needs, SETVAL manual. In PgAdmin where I want to restrict the sequences to all those in the public schema:

SELECT  SETVAL(c.oid, 1)
from pg_class c JOIN pg_namespace n 
on n.oid = c.relnamespace 
where c.relkind = 'S' and n.nspname = 'public'

I post this as a help to anyone coming here.

Upvotes: 17

Related Questions