(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.
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.
setval() function. When set to false nextval will return 1, otherwise it would have returned 2.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 $$;
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.
i from '''[a-z0-9_]*') from '[a-z0-9_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
;
ALTER SEQUENCE table_name_id_seq START WITH 1;