Thread: Use of nextval, currval
I'm attempting to convert a code which uses Oracle to Postgres. I am concerned about a couple of points in the SQL, which I should be able to do in Postgres. Say I have defined a sequence DataId; The Oracle uses the sequence as follows: insert into TABLE (id1,.......,id2) values (DataId.NextVal,......,DataId.Currval); I believe that I can replace this in Postgres with: insert into TABLE (id1,.......,id2) values (nextval('DataId'),.....,currval('DataId'); What I think is expected is that the values of id1 and id2 will be equal. I have been told that Oracle will guarantee this; will that be assumption be true for Postgres, also? (My concern is that, with multiple/many users of the same database, there may be instances where DataId increments before the currval happens, such that it's possible that id1 != id2, above.) Is there a simple way to write the SQL such that I can only use nextval once, keeping its value for both id1 and id2? (I have no idea why the values are duplicated.....) For that matter, is that a realistic assumption for Oracle's treatment of sequences? Thanks, Matt
Matt, > I'm attempting to convert a code which uses Oracle to Postgres. > > I am concerned about a couple of points in the SQL, which I should be > able to do > in Postgres. > > Say I have defined a sequence DataId; > > The Oracle uses the sequence as follows: > > insert into TABLE (id1,.......,id2) > values (DataId.NextVal,......,DataId.Currval); > > I believe that I can replace this in Postgres with: > > insert into TABLE (id1,.......,id2) > values (nextval('DataId'),.....,currval('DataId'); > > > What I think is expected is that the values of id1 and id2 will be > equal. This is correct. Please post if you for some reason find them not equal. -Josh Berkus
"Josh Berkus" <josh@agliodbs.com> writes: >> I believe that I can replace this in Postgres with: >> >> insert into TABLE (id1,.......,id2) >> values (nextval('DataId'),.....,currval('DataId'); >> >> What I think is expected is that the values of id1 and id2 will be >> equal. > This is correct. Well ... it's relying on the assumption that the elements of the VALUES list will be evaluated left-to-right. Which is true at the moment, and I don't really expect it to change, but it still could be called an unsafe dependency on undocumented implementation details. I doubt you will find anything in the SQL spec that requires implementations to behave that way. regards, tom lane