Menu
(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.
955
The first method to remove a PostgreSQL database is to use the following SQL statement: DROP DATABASE <database name>; The command removes the directory containing the database information and the catalog entries. Only the database owner can execute the DROP DATABASE command.
ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings. You must own the sequence to use ALTER SEQUENCE . To change a sequence's schema, you must also have CREATE privilege on the new schema.
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.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With