Thread: Record order change after update

Hello list,

After update a column on a table, that row goes to the top when I do a
select from that table without any order, is that the expected behavior
in postgresql? is there a way to prevent it?

Thanks in advance.

Josué Maldonado

On Tue, Mar 02, 2004 at 04:12:05PM -0600, Josué Maldonado wrote:

> After update a column on a table, that row goes to the top when I do a
> select from that table without any order, is that the expected behavior
> in postgresql?

Yes.

> is there a way to prevent it?

No.  Use ORDER BY.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Los dioses no protegen a los insensatos.  Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)

My understanding of RDBMs:

you will get all information as indicated by the query in an unsorted order.
Sometimes you get the same order; sometimes you do not.

To ensure expected ordering you must use the ORDER keyword.






Quoting Josué Maldonado <josue@lamundial.hn>:

> Hello list,
>
> After update a column on a table, that row goes to the top when I do a
> select from that table without any order, is that the expected behavior
> in postgresql? is there a way to prevent it?
>
> Thanks in advance.
>
> Josué Maldonado
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Clinging to sanity, josue@lamundial.hn (Josué Maldonado) mumbled into her beard:
> After update a column on a table, that row goes to the top when I do a
> select from that table without any order, is that the expected
> behavior in postgresql? is there a way to prevent it?

Prevent what?

SQL returns unordered sets of data unless you specifically impose an
order using an ORDER BY clause.

Did PostgreSQL ignore your ORDER BY clause?  If so, that would be a
problem worth reporting.

But if your query did not impose an ORDER BY clause, it is entirely
proper for the database engine to return data in whatever order it
finds most convenient.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://cbbrowne.com/info/sap.html
"When people understand what Microsoft is up to, they're outraged."
-- Tim O'Reilly, President, O'Reilly & Associates

From the relational point of view, tuples in a relation are sets, and sets have no ordering.
That's why you should never trust how tuples are ordered (on any relational database, not just postgreSQL), and if you care about ordering you should always use the ORDER BY clause.

On Tue, 2004-03-02 at 19:12, Josué Maldonado wrote:

Hello list,

After update a column on a table, that row goes to the top when I do a 
select from that table without any order, is that the expected behavior 
in postgresql? is there a way to prevent it?

Thanks in advance.

Josué Maldonado



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:

>    >From the relational point of view, tuples in a relation are sets, and
>    sets have no ordering.
>    That's why you should never trust how tuples are ordered (on any
>    relational database, not just postgreSQL), and if you care about
>    ordering you should always use the ORDER BY clause.
>    On Tue, 2004-03-02 at 19:12, Josué Maldonado wrote:
>
> Hello list,
>
> After update a column on a table, that row goes to the top when I do a
> select from that table without any order, is that the expected behavior
> in postgresql? is there a way to prevent it?

The paragraph you quoted above answers your question.  Use ORDER BY if
you want a definite order out of a SELECT query.

-Doug