I need to update sequences in more then 26 tables on various system
Right now I am doing SELECT setval('auth_group_id_seq', max(id)) FROM auth_group; for every table.
But is their a way where I can iterate over every table available in \dt public.* and then update the sequences.
I thought of doing something like this but got error instead select table_name from tables where tables IN (\dt public.*);
You can get every table in the public schema with this query:
select tablename from pg_tables where schemaname='public';
To get all sequences per the public schema you can do this:
select cs.relname, nc.nspname
from pg_class cs
join pg_namespace nc on cs.relnamespace = nc.oid
where cs.relkind='S' and nc.nspname='public';
You should not need to link a sequence to a table in order to alter the sequence. Also consider the sequences are their own object and do not "belong" to a table in postgres. Sequences must be a unique name per schema, so as long as you alter the sequences you want per the correct schema this should do the trick.
Altering a sequence syntax:
ALTER SEQUENCE serial RESTART WITH 105;
A fast way to do these updates would be to dynamically generate the alter statements. However, this requires the value you are changing maxvalue to be the same on all sequences.
select 'alter sequence ' || nc.nspname || '.' || cs.relname || ' maxvalue value_to_be_set;'
from pg_class cs
join pg_namespace nc on cs.relnamespace = nc.oid
where cs.relkind='S' and nc.nspname='public';
Would output something like this:
alter sequence public.sequence_1 maxvalue 5;
alter sequence public.sequence_2 maxvalue 5;
alter sequence public.sequence_3 maxvalue 5;
id is the max value which I need to update. I work on ORM, I am not good with raw SQL. So I need to use id instead of value_to_be_set and this command will update all sequences ??I recomend you to iterate over sequence:
SELECT * FROM pg_class WHERE relkind='S'
