Postgresql:更新时行号更改 [英] Postgresql: row number changes on update

问题描述

我是 Postgresql 的新手，我使用的是 9.3 版.我有一张桌子，里面有几行.我的问题是，当我更新一行时，行号会更改，并将其移动到表中的最后一个位置.我的问题是:这是默认行为吗，因为我认为当一行被更新时，它不应该从它的位置移动?操作好像是先删除再插入行.

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.

这是示例 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;

现在开始:

SELECT * FROM cities;

将 id 为 1 的更新行移动到最后一个位置.

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

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

谢谢

推荐答案

行号改变了

关系表中没有行号"这样的东西.

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

我认为当一行被更新时，它不应该从它的位置移动

由于一行没有位置"，因此也不存在将其移出"这样的东西.

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

把它们想象成篮子里的球.

Think of them like balls in a basket.

如果没有 ORDER BY，DBMS 可以自由地以它认为合适的任何顺序返回行.

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

如果您需要以某种方式对行进行排序，唯一的(实际上:唯一的)方法是使用 ORDER BY 语句.

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.

这篇关于Postgresql:更新时行号更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！