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

Question

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

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

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

Answers to

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

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