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


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

Answers 2
Added at 2016-12-12 15:12

(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.

nr: #1 dodano: 2016-12-12 21:12

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.

nr: #2 dodano: 2016-12-12 21:12

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

DO $$
 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 $$;
Source Show
◀ Wstecz