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/