Thread: Inserting and incrementing with MAX aggregate
I was wondering if this statement was still supported in 7.0.x versions of pgSQL. I upgraded from a 6.x version where say: insert into events (eid,name) values (max(eid)+1,'server down'); works but not in the 7.x variants. I know that I could use the serial type for that column but in the interest not having to rewrite the code (or dump, drop and recreate the tables/data), I wanted to know if there was a more stand way to incrementing a field automatically that would be fairly portable. Any help would be appreciated. Keith Perry VCSN Inc. keith@vcsn.com http://vcsn.com
Could you not: insert into events (eid,name) values ((SELECT max(eid) FROM EVENTS)+1,'server down'); It looks like it would work. It's just a subquery... Ian Keith Perry wrote: > I was wondering if this statement was still supported in 7.0.x versions > of pgSQL. I upgraded from a 6.x version where say: > > insert into events (eid,name) values (max(eid)+1,'server down'); > > works but not in the 7.x variants. > > I know that I could use the serial type for that column but in the > interest not having to rewrite the code (or dump, drop and recreate the > tables/data), I wanted to know if there was a more stand way to > incrementing a field automatically that would be fairly portable. Any > help would be appreciated. > > Keith Perry > VCSN Inc. > keith@vcsn.com > http://vcsn.com
Ahhh, thank you that worked. I don't know why but for some reason I didn't think I could do a subquery in an insert *laff*- 'learn something new everyday :) Keith- Ian Harding wrote: > Could you not: > > insert into events (eid,name) values ((SELECT max(eid) FROM > EVENTS)+1,'server down'); > > It looks like it would work. It's just a subquery... > > Ian > > Keith Perry wrote: > > > I was wondering if this statement was still supported in 7.0.x versions > > of pgSQL. I upgraded from a 6.x version where say: > > > > insert into events (eid,name) values (max(eid)+1,'server down'); > > > > works but not in the 7.x variants. > > > > I know that I could use the serial type for that column but in the > > interest not having to rewrite the code (or dump, drop and recreate the > > tables/data), I wanted to know if there was a more stand way to > > incrementing a field automatically that would be fairly portable. Any > > help would be appreciated. > > > > Keith Perry > > VCSN Inc. > > keith@vcsn.com > > http://vcsn.com