Re : Re : Re : Resetting SEQUENCEs

[Laurent ROCHE <laurent_roche(at)yahoo(dot)com>]

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