Re: autoupdate sequences after copy

I just run a script to update them after importing data.  Something like this... (not a real script...)

while read tablename
do
    echo "select setval('${tablename}_${tablename}_seq', \
        (select max(${tablename}id) from $tablename))" | psql database
done
< tablenames.txt

Of course, this assumes you allowed the default sequence names to be created via SERIAL and that you created the
primarykeys as <tablename>id.  You might need a text file with table, key, and sequence names, but this is likely
easierthan issuing a bunch of psql commands by hand. 

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002


>>> CSN <cool_screen_name90001@yahoo.com> 10/09/03 12:10PM >>>
On Thursday 09 October 2003 08:10, CSN wrote:
> Is there a way to have p/k sequences get
automatically
> set to max(id)+1 after COPY's like the following?
>
> copy table1 (id,name) from stdin;
> 1 abc
> 2 def
> 3 fhi
> \.

Not really - if you don't use the sequence it keeps
its value. If you look at
pg_dump it issues an explicit setval() after a copy.

I'm not sure you can even work around it with a BEFORE
trigger to check and
update the sequence, the nextval() call will probably
be processed before the
trigger gets called (haven't checked). In any case,
performance would be a
bit poor.

Is there any reason why you're supplying your own id
values when you already
have a sequence?

--
  Richard Huxton
  Archonet Ltd

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

> Is there any reason why you're supplying your own id
values when you already have a sequence?


I'm importing a lot of data and tables (from mysql)
and want to keep the ID's the same.

CSN

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly