31

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.

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.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
Ominus
  • 5,501
  • 8
  • 40
  • 44
  • 3
    Could you create a view of the table in question whereby things are ordered by id? –  Jul 05 '11 at 16:18

4 Answers4

53

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.

If you want a consistent order without needing to add an order by clause, create a view as suggested in Jack's comment.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
13

For what it's worth, which probably isn't much, from my testing, it appears that PostgreSQL's "default" ordering is based on the time the records were last updated. The most recently updated records will appear last. Note that I couldn't find any documentation to support this. It's just what I've found from my own testing.

Webucator
  • 2,397
  • 24
  • 39
  • 1
    Would you have a documentation reference to this? Thanks! – Zero Distraction Jan 06 '22 at 04:48
  • 1
    I couldn't find any documentation on this. PostgreSQL doesn't appear to have an _official_ default ordering, but that _in practice_, if you don't specify an order, the last updated records go to the bottom. – Webucator Jan 06 '22 at 13:08
  • 1
    This answer has no any evidence or reference to any doc, most probably its from you some specific experience, but that is not enough to have this conclusion. – Tigran Babajanyan Nov 14 '22 at 07:39
  • 2
    @TigranBabajanyan that's true. I've clarified my answer. – Webucator Nov 20 '22 at 14:02
  • Yes I've observed the same, although this behaviour probably shouldn't be relied upon as others have pointed out before. – AlexAngc Jan 19 '23 at 15:18
12

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 Jack Maney) there is no way you can influence the order of the rows that are returned.

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.

Another option might be 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 guarantee that 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.

okm
  • 23,575
  • 5
  • 83
  • 90
-1

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 BY clause is the way to go as mentioned already.

Daniel Harcek
  • 321
  • 3
  • 7
  • 3
    This is wrong. Even if the retrieval is done through an index, the overall order of the result might not be ordered by the index values - and it it is most definitely not _guaranteed_ (if at all, chances are high that the index order will be kept). –  Jun 16 '15 at 19:30
  • Its wrong. Indexes comes into picture when data is already retrieved, so it wont help. Rather it will confuse user that on which index desired data is present. – Deepak Gangore Aug 27 '19 at 10:39