Received: from thor.tht.net (thor.tht.net [209.47.145.4]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eB5GNAG65800 for ; Tue, 5 Dec 2000 11:23:10 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by thor.tht.net (8.9.3/8.9.3) with ESMTP id MAA58268 for ; Tue, 5 Dec 2000 12:23:38 GMT (envelope-from reedstrm@rice.edu) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Tue, 5 Dec 2000 10:10:12 -0600 (CST) Date: Tue, 5 Dec 2000 10:10:12 -0600 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: reinitialize a sequence? Message-ID: <20001205101012.A13143@rice.edu> Mail-Followup-To: pgsql-sql@postgresql.org References: <20001205003034.D31987@erm1.u-strasbg.fr> <14892.11783.659931.212551@wynand.flutterby.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <14892.11783.659931.212551@wynand.flutterby.com>; from danlyke@flutterby.com on Mon, Dec 04, 2000 at 03:51:35PM -0800 X-Archive-Number: 200012/32 X-Sequence-Number: 363 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.attnum and d.adsrc ~ ('nextval\\(\''||relname); mydb=# \o mydb=# \i sequence_reset.sql You might get some errors for empty tables, since '0' is out of bounds for sequences. Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005