Re: offset and limit in update and subselect

Hmmm... that's good to know. Basically, I'm trying to model fixed order
tables in another application through a proxy mechanism (see
http://rpgsql.sourceforge.net/) I guess I will have to force row
ordering on all proxied tables.

Tim

Tom Lane wrote:

> "Timothy H. Keitt" <Timothy(dot)Keitt(at)SUNYSB(dot)Edu> writes:
>
>> Basically, I need to update rows by offset from the beginning of the
>> table.
>
>
> I think you'd better rethink your data design. Tuple order in a table
> is not a defined concept according to SQL. Even if we allowed queries
> such as you've described, the results would not be well-defined, but
> would change at the slightest provocation. The implementation feels
> itself entitled to rearrange tuple order whenever the whim strikes it.
>
> As the documentation tries hard to make plain, LIMIT/OFFSET are only
> guaranteed to produce reproducible results if there's also an ORDER BY
> that constrains the tuples into a unique ordering.
>
> regards, tom lane

--
Timothy H. Keitt
Department of Ecology and Evolution
State University of New York at Stony Brook
Phone: 631-632-1101, FAX: 631-632-7626
http://life.bio.sunysb.edu/ee/keitt/