PostgreSQL表行在更新时会显示在底部

Question

I am currently migrating from MySQL to PostgreSQL in a Laravel application, and I noticed that when updating, the row goes to the end of the table (bottom).

In the application I know I can use ORDER BY to sort, but I am referring to the internal behavior of the database while performing the UPDATE action.

In Mysql, it remains in the same position it occupied before the update. Is there any way to apply this function? Would it be a InnoDB feature? Using Navicat Premium 12.1 DBMS.

I think this is just an aesthetic factor, but even so I would like to learn how to carry out this "permanent ordination".

The database is in UTF-8 encoding and pt_BR.UTF8 collation and ctype. Following is the table:

CREATE TABLE `properties` (
   `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `title` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `name` varchar(255),
   `rental_price` decimal(10, 2),
   `sale_price` decimal(10, 2)
);

Thank you all!

Answer 1

Part 1: Generally use ORDER BY

If you do not use the ORDER BY statement, both MySQL and PostgreSQL (and for that matter most relational DBMS systems) do not make any promises about the order of records.

You should refactor your application to use the ORDER BY statement. If you want your data set to be ordered by newest first, you could use something like:

SELECT * FROM yourtable ORDER BY id DESC;
SELECT * FROM youtable ORDER BY creation_date DESC; -- if your table has such a column

Similarly, you can have oldest objects first by using one of the following:

SELECT * FROM yourtable ORDER BY id ASC;
SELECT * FROM youtable ORDER BY creation_date ASC; -- if your table has such a column

Part 2: Looking into the mechanics

You added to your question a more detailed inquiry:

[...] I know I can use ORDER BY to sort, but I am referring to the internal behavior of the database while performing the UPDATE action.

There is multiple things that influence the sequence of database records displayed on your screen, when performing a query. In a real life application, it is not (practially) possible to predict this sequence.

I assume this is simply an effect of PostgreSQL creating a new record for the updated record as can be found here in the Updating a Row section. I suggest to not rely on this behvaiour in any of your applications.