Re : Re : Re : Resetting SEQUENCEs - Mailing list pgsql-general

Hi,

if anybody needs something like this here is the request to produce the code to resynch SEQUENCEs with the data in tha tables:
SELECT  'SELECT SETVAL(\'' ||S.relname|| '\', MAX(' ||C.attname|| ') ) FROM ' ||T.relname|| '  ;'
FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
ORDER BY S.relname

This goes through all the sequences in current shema and generates SELECT SETVAL(... orders to set the SEQUENCE to the last value in the table.
This only works it the SEQUENCE is OWNED (ALTER SEQUENCE ... OWNED BY ... since 8.2 or SERIAL) by a column: this way, the above SELECT will "find" the matching column and table for a SEQUENCE.

Note : this will not work for SEQUENCES not linked to tables, you could find a way by working out the table name and column name from the sequence name (for instance by using a similar naming convention to the SERIAL sequences).

Thanks for all the help I got.

Cheers,
L@u
The Computing Froggy