pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: reinitialize a sequence?
Date: Tue, 5 Dec 2000 11:27:37 -0600
Message-ID: <20001205112737.B13143@rice.edu> (raw)
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>

Following up to myself:

There is in fact a bug in 7.0.2 that's been fixed in 7.0.3 which causes
my select below not to work. The name of the sequence is now stored
with double quotes around it, to handle the mixed case names problem,
so the query needs to look like (untested: note added double quote to
last where clause):

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);


On Tue, Dec 05, 2000 at 10:10:12AM -0600, Ross J. Reedstrom wrote:
> 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;
> 
> 

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



view thread (5+ messages)

Message-ID: <20001205112737.B13143@rice.edu>
Permalink:  ../20001205112737.B13143@rice.edu/
Also on:    postgresql.org/message-id/20001205112737.B13143@rice.edu

 · 

reply

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: <20001205112737.B13143@rice.edu>
  References: <20001205003034.D31987@erm1.u-strasbg.fr> <14892.11783.659931.212551@wynand.flutterby.com> <20001205101012.A13143@rice.edu> <20001205112737.B13143@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