pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sequence update function?
2+ messages / 2 participants
[nested] [flat]

* Sequence update function?
@ 2007-06-12 01:25 Uwe C. Schroeder <uwe@oss4u.com>
  2007-06-12 06:31 ` Re: Sequence update function? Thomas Markus <t.markus@proventis.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Uwe C. Schroeder @ 2007-06-12 01:25 UTC (permalink / raw)
  To: pgsql-admin


Hi,

just thought I ask before I start doing it myself: Has anyone ever written a 
stored procedure (or any other means) of syncing the sequence to the max # 
key in the corresponding tables?

I'm running a simple script to update table contents between dev and 
production system, however the script is flawed in the way that it copied the 
primary key along (it's a simple pg_dump|pg-restore shellscript). It works 
fine because all rows that are already there just fail for PK violation. 
In the end I'm ending up with sequences out of sync. So i though a simple 
little helper that loops over the sequences (information schema), grabs the 
according table, does a max(key) on the table and sets the sequence to that 
will do nicely.
Anyone written something like that?

Thanks

	UC

--
Open Source Solutions 4U, LLC	1618 Kelly St
Phone:  +1 707 568 3056		Santa Rosa, CA 95401
Cell:   +1 650 302 2405		United States
Fax:    +1 707 568 6416



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Sequence update function?
  2007-06-12 01:25 Sequence update function? Uwe C. Schroeder <uwe@oss4u.com>
@ 2007-06-12 06:31 ` Thomas Markus <t.markus@proventis.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Thomas Markus @ 2007-06-12 06:31 UTC (permalink / raw)
  To: pgsql-admin

do a

select setval('sequence',max(col)+1) from tablename

for each table

tm

Uwe C. Schroeder schrieb:
> Hi,
>
> just thought I ask before I start doing it myself: Has anyone ever written a 
> stored procedure (or any other means) of syncing the sequence to the max # 
> key in the corresponding tables?
>   

Attachments:

t.markus.vcf (text/x-vcard, 253B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-06-12 06:31 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-12 01:25 Sequence update function? Uwe C. Schroeder <uwe@oss4u.com>
2007-06-12 06:31 ` Thomas Markus <t.markus@proventis.net>

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