postgresql Postgres 默认按 id 排序 - worldship
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6585574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres default sort by id - worldship
提问by Ominus
I need to setup worldship to pull from one of our postgres databases. I need to have it so that the packages are sorted by id. I have no way (that i am aware of) of having worldship send the order by clause so I need to have the default for the records returned to be returned by id.
我需要设置 worldship 以从我们的 postgres 数据库之一中提取。我需要它，以便包按 id 排序。我没有办法（我知道）让 worldship 发送 order by 子句，所以我需要默认返回的记录由 id 返回。
On a second note I have no idea how postgres default sorts it looks like it by the last time the record was changed so if i write a two records id 1,2 then change record 2 when I run the query it returns them with record 2 being first.
在第二个注意事项中，我不知道 postgres 默认如何排序它在上次更改记录时的样子，所以如果我写了两个记录 id 1,2 然后在我运行查询时更改记录 2 它会返回它们与记录 2成为第一。
回答by Denis de Bernardy
Rows are returned in an unspecified order, per sql specs, unless you add an order by clause. In Postgres, that means you'll get rows in, basically, the order that live rows read on the disk.
除非您添加 order by 子句，否则根据 sql 规范，以未指定的顺序返回行。在 Postgres 中，这意味着您将获得行，基本上是在磁盘上读取活动行的顺序。
If you want a consistent order without needing to add an order by clause, create a view as suggested in Hyman's comment.
如果您想要一致的顺序而不需要添加 order by 子句，请按照 Hyman 的评论中的建议创建一个视图。
回答by a_horse_with_no_name
There is no such thing as a "default sort". Rows in a table are not sorted.
没有“默认排序”这样的东西。表中的行未排序。
You could fake this with a view (as suggested by Hyman Maney) there is no way you can influence the order of the rows that are returned.
您可以使用视图来伪造这一点（如 Hyman Maney 所建议的那样），您无法影响返回的行的顺序。
But if you do that, be aware that adding an additional ORDER BY to a SELECT based on that view will sort the data twice.
但是，如果您这样做，请注意将额外的 ORDER BY 添加到基于该视图的 SELECT 将对数据进行两次排序。
Another option mightbe to run the CLUSTER command on that table to physically order the rows on the disk according to the column you want. But this sill does not guaranteethat the rows are returned in that order. Not even with a plain SELECT * FROM your_table(but chances are reasonably high for that).
You will need to re-run this statement on a regular basis because the order created by the CLUSTER command is not automatically maintained.
另一种选择可能是对该表运行 CLUSTER 命令，以根据所需的列对磁盘上的行进行物理排序。但此基台并不能保证按该顺序返回行。甚至没有平原SELECT * FROM your_table（但机会相当高）。您将需要定期重新运行此语句，因为不会自动维护 CLUSTER 命令创建的订单。
回答by Webucator
For what it's worth, which probably isn't much, PostgreSQL's "default" ordering is based on the time the records were last updated. The most recently updated records will appear last.
对于它的价值，可能并不多，PostgreSQL 的“默认”排序基于记录的上次更新时间。最近更新的记录将出现在最后。
回答by Daniel Harcek
You could eventually use a sorted index, which should guarantee you order of retrieved rows in case the query plan hits the index, or if you force it, but this approach will be more than circuitous :). ORDER BYclause is the way to go as mentioned already.
您最终可以使用排序索引，它应该可以保证您检索的行的顺序，以防查询计划命中索引，或者如果您强制执行它，但这种方法将不仅仅是迂回:)。ORDER BY条款是已经提到的方法。
