Postgresql: row number changes on update

3
votes

I am new to Postgresql and I am using version 9.3. I have a table in which i have a couple of rows. My question is, when I update a row, the row number is changed and it is moved to the the last location in the table. My question is: is this the default behavior, because I think when a row is updated, it should not be moved from its place? The operation seems to be like deleting and then inserting the row again.

Here is the sample SQL:

CREATE TABLE cities
(
    city_id serial, 
    city_name character varying(50), 
    PRIMARY KEY (city_id)
);

INSERT INTO cities (city_name) VALUES ('ABC');
INSERT INTO cities (city_name) VALUES ('DEF');
INSERT INTO cities (city_name) VALUES ('GHI');
INSERT INTO cities (city_name) VALUES ('JKL');

UPDATE cities
    SET city_name = 'XYZ'
    WHERE city_id = 1;

Now, on:

SELECT * FROM cities;

moves the updated row with id 1 at the last location.

2, DEF
3, GHI
4, JKL
1, XYZ

Thanks

1
sqlpostgresql
"The operation seems to be like deleting and then inserting the row again." True, unless a HOT update is done. Other users might still see the original "order" because of their transaction settings. It all depends! A database server is not a filesystem. - Frank Heikens

1 Answers

8
votes

the row number is changed

There is no such thing as a "row number" in a relational table.

I think when a row is updated, it should not be moved from its place

As a row doesn't have a "place", there is no such thing as "moving" it out of that either.

Rows in a relational table are not sorted.

Think of them like balls in a basket.

Without an ORDER BY the DBMS is free to return the rows in any order it thinks is OK.

If you need to have the rows ordered in a certain way the only (really: the only) way to get that is to use an ORDER BY statement.