Search Postgresql Archives

Re : Re : Re : Resetting SEQUENCEs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


----- Message d'origine ----
De : Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx>
À : Laurent ROCHE <laurent_roche@xxxxxxxxx>
Cc : Martijn van Oosterhout <kleptog@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Envoyé le : Jeudi, 18 Octobre 2007, 23h54mn 16s
Objet : Re: Re : Re : 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux