Re: [GENERAL] autoupdate sequences after copy
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] autoupdate sequences after copy
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 \. CSN __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 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 primary keys as id. You might need a text file with table, key, and sequence names, but this is likely easier than issuing a bunch of psql commands by hand. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 >>> CSN <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] autoupdate sequences after copy
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])