Re: reinitialize a sequence?

On Mon, Dec 04, 2000 at 03:51:35PM -0800, Dan Lyke wrote:
> Bruno Boettcher writes:
> > is there a simple way to tell all sequences to take the max value +1 of
> > their respective tables? (a bit like the vacuum command?)
> 
> This is completely gross, but what I've done:
> 

Hmm, what I usually do is something like:

SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;


To do that in one fell swoop is not trivial, since there's no easy way to
extract the automatically generated sequence name from the system
tables.

Well, I couldn't resist the challenge, so here's a crufty example, but better
than the perl that was here (I think ;-)

The following psql will generate psql statements to reset all your sequences
to the maximum value. This is for 7.0.2, and depends critically on exactly
how the default value for the 'serial' type is constructed. 


select 'SELECT setval(\'"' ||        substr(adsrc,10,(length(adsrc) - 17)) ||     '"\', max("' || attname || '")) FROM
"'||     relname || '";'     from pg_class c,          pg_attribute a,          pg_attrdef d     where c.oid=d.adrelid
and          a.attrelid=c.oid and           d.adnum=a.attnum and           d.adsrc ~ ('nextval\\(\''||relname);
 

Use this by redirecting output to a file, then reading in that file, as so:

me@mycomputer:~$ psql mydb

mydb=# \t
Showing only tuples.
mydb=# \o sequence_reset.sql
mydb=# select 'SELECT setval(\'"' || substr(adsrc,10,(length(adsrc) - 17)) || '"\', max("' || attname || '")) FROM "'
||relname || '";' from pg_class c, pg_attribute a, pg_attrdef d where c.oid=d.adrelid and a.attrelid=c.oid and
d.adnum=a.attnumand d.adsrc ~ ('nextval\\(\''||relname);
 
mydb=# \o
mydb=# \i sequence_reset.sql

<output showing resets occuring goes here>

You might get some errors for empty tables, since '0' is out of bounds
for sequences.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005