Thread

1. Re: autoupdate sequences after copy

   CSN <cool_screen_name90001@yahoo.com> — 2003-10-09T19:10:44Z

   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
   
   

2. Re: autoupdate sequences after copy

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2003-10-09T20:49:52Z

   On Thu, Oct 09, 2003 at 12:10:44PM -0700, CSN wrote:
   > 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.
   
   The problem is when you do
   pg_dump -s -t table database > schema
   pg_dump -a -t table database > data
   edit data
   psql -f schema otherdatabase
   psql -f data otherdatabase
   
   you then need to quickly
     select setval('col_id_seq',(select max(id) from col));
   for each sequence.. and not forget..
   
   Cheers,
   
   Patrick