From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: reinitialize a sequence?
Date: Tue, 5 Dec 2000 10:10:12 -0600
Message-ID: <20001205101012.A13143@rice.edu> (raw)
In-Reply-To: <14892.11783.659931.212551@wynand.flutterby.com>
References: <20001205003034.D31987@erm1.u-strasbg.fr>
<14892.11783.659931.212551@wynand.flutterby.com>
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
<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu
Subject: Re: reinitialize a sequence?
In-Reply-To: <20001205101012.A13143@rice.edu>
References: <20001205003034.D31987@erm1.u-strasbg.fr> <14892.11783.659931.212551@wynand.flutterby.com> <20001205101012.A13143@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox