如果对非唯一属性进行排序，是否完全保证 PostgreSQL 顺序? [英] Is PostgreSQL order fully guaranteed if sorting on a non-unique attribute?

问题描述

可能的重复:
为什么SQL 查询的结果没有按照我期望的顺序返回?

从阅读 7.5 Sorting Rows 和我的问题看过PostgreSQL，我的印象如下，但该部分不完全明确，所以如果有人可以验证，我将不胜感激:

From reading 7.5 Sorting Rows and from issues I've seen with PostgreSQL, my impression is the following, but that section is not fully explicit, so I would be grateful if someone could verify:

SELECT * FROM items;

没有保证的顺序.

SELECT * FROM items ORDER BY published_date ASC;

保证日期不同的两个项目按给定的顺序出现，但不保证日期相同的两个项目总是按相同的顺序出现.

guarantees that two items with different dates come in a given order, but does not guarantee that two items with the same date always come in the same order.

SELECT * FROM items ORDER BY published_date ASC, id ASC;

总是以相同的顺序返回项目，因为它是完全确定的.

always returns items in the same order, since it is fully deterministic.

我有这个权利吗?

我不太清楚对一个属性(例如 published_date)进行排序是否保证具有相同值的记录的顺序，如第二个示例所示.

I'm not quite clear about whether sorting on one attribute (such as published_date) guarantees the order for records with the same value, as in the second example.

推荐答案

Order 不保证，除非您使用 ORDER BY 子句明确指定.

Order is not guaranteed unless you explicitly specify it with the ORDER BY clause.

您可能在多次执行时以相同的顺序获取数据，以防没有数据库活动，因为 PostgreSQL 只会按照它在数据库页面中找到的顺序返回行.做一个小测试:

You might be getting data in the same order upon several executions in case there is no database activity, as PostgreSQL will just return rows in the order it finds them in the database pages. Do a small test:

• 插入多行保持所需的顺序；
• 查询表:您将得到有序的行；
• 更新集合中的第一条记录；
• 再次查询表；
• 观察结果.

简而言之:您可能甚至以所需的顺序获取行，但这只是巧合.

In short: You might be even getting rows in the desired order, but this is just a coincidence.

这篇关于如果对非唯一属性进行排序，是否完全保证 PostgreSQL 顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！