Thread: Resetting SEQUENCEs

On Thursday 18 October 2007, Laurent ROCHE <laurent_roche@yahoo.com> wrote:
> Hi,
>
> I am quite surprised I could not find a way to automatically reset the
> value of a sequence for all my tables.
>
> Of course, I can write:
> SELECT setval('serial', max(id)) FROM distributorsBut if I reload data
> into all my tables, it's a real pain to have to write something like this
> for every single table with a sequence.
>
> I would expect PostgreSQL to provide some command like:
> resynchAllSequences my_schema;
>
> Can this be a feature in the future ?
>

Restoring backups normally will set sequences to the correct values; you're
doing something wrong if yours are not.

2007/10/18, Laurent ROCHE <laurent_roche@yahoo.com>:
>
> Hi,
>
> I am quite surprised I could not find a way to automatically reset the value
> of a sequence for all my tables.
>
> Of course, I can write:
> SELECT setval('serial', max(id)) FROM distributors
> But if I reload data into all my tables, it's a real pain to have to write
> something like this for every single table with a sequence.
>
> I would expect PostgreSQL to provide some command like:
> resynchAllSequences my_schema;


try something like

CREATE FUNCTION execute(in_sql TEXT) RETURNS void as $$
BEGIN
EXECUTE in_sql;
RETURN;
END;
$BODY$ language plpgsql;


select execute(
$$select setval( '$$
|| table_name ||
$$_id_seq', coalesce((select max(id) from $$
|| table_name ||
$$),1), false ) $$
) from information_schema.tables where you want;



--
Filip Rembiałkowski

On Thu, Oct 18, 2007 at 10:06:00AM -0700, Laurent ROCHE wrote:
> Hi,
>
> I am quite surprised I could not find a way to automatically reset the value of a sequence for all my tables.

I never bother resetting sequences. It's not like the numbers mean
anything...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.