Re : Re : Re : Resetting SEQUENCEs

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(at)u
The Computing Froggy

----- Message d'origine ----
De : Alvaro Herrera <alvherre(at)commandprompt(dot)com>
À : Laurent ROCHE <laurent_roche(at)yahoo(dot)com>
Cc : Martijn van Oosterhout <kleptog(at)svana(dot)org>; pgsql-general(at)postgresql(dot)org
Envoyé le : Jeudi, 18 Octobre 2007, 23h54mn 16s
Objet : Re: Re : Re : [GENERAL] Resetting SEQUENCEs

Laurent ROCHE wrote:
> Hi,
>
> So nobody can help me to write the SELECT that will return the
SEQUENCE names, and their linked columns and their linked tables ?
> Are the system tables documented somewhere ?

Of course -- in the "internals" section. You need pg_class where
relkind = 's', pg_depend, and possibly pg_attribute.

--
Alvaro Herrera
http://www.PlanetPostgreSQL.org/
"La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva"

---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

_____________________________________________________________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail