0

I have this table in Postgres:

 id | name  |   city    
----+-------+-----------
  2 | Anna  | Paris
  3 | James | 
  1 | Bob   | Berlin

And I want to reorder the table itself, not the output of an SELECT statement, to this:

 id | name  |   city    
----+-------+-----------
  1 | Bob   | Berlin
  2 | Anna  | Paris
  3 | James |

It is difficult to google this, because I only get shown the order by command for the select statement^^

Bog
  • 101
  • 3
  • What do you mean by reorder ? By the way , the data stored in the tables aren't sorted. – Ergest Basha Mar 12 '24 at 10:52
  • by reorder i mean to change the table itself. I just want to execute for example SELECT * FROM table and don't append an ORDER BY^^ – Bog Mar 12 '24 at 11:04
  • The table is unordered heap, and there is no rows ordering in it. The server is free in rows ordering until definite ORDER BY is specified. Moreover, in two consecutive calls of the same query the output may differ in rows ordering easily - and this is a norma. You should not worry about the rows ordering when it is not critical for you, otherwise you must specify definite ORDER BY. – Akina Mar 12 '24 at 12:23
  • Hmm I am confused... Like I get it that the table is an unordered heap, but I ran ;SELECT * FROM students; 100 times and I always get the same order. Looks like the default order is the last modified date. I don't know, maybe I am just confused... – Bog Mar 12 '24 at 12:33
  • @Bog from Sorting Rows (ORDER BY) After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen – Ergest Basha Mar 12 '24 at 12:52
  • @ErgestBasha ahh okay interesting. Thank you very much :) – Bog Mar 12 '24 at 13:04

1 Answers1

1

That's because ORDER BY is the only way to ensure an order.

If you want to change the order of rows that you see when you run SELECT * FROM tab or TABLE tab, that's a fool's errand, because there is no fixed order of the table rows:

  • any UPDATE can change the order

  • two concurrent queries can see a different row order, because sequential scans need not always start at the beginning of a table

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
  • Wait so all SQL tables are unordered? Is there no way to change the order in the table content itself?^^ – Bog Mar 12 '24 at 11:03
  • Exactly. That's how it is. A relational database is no spreadsheet. – Laurenz Albe Mar 12 '24 at 11:17
  • But has to be a default order... I mean running SELECT * FROM table always returns the same order. It seems like the default order is the last modified date. Am I wrong? – Bog Mar 12 '24 at 12:30
  • 1
    The table file is not necessarily read from start to finish in the same order every time https://postgresqlco.nf/doc/en/param/synchronize_seqscans/ and if you do updates, rows may move around – bobflux Mar 12 '24 at 12:59
  • Right. You can sometimes get a different order. And the order will almost always change after an UPDATE. – Laurenz Albe Mar 12 '24 at 13:18
  • Alright. Thank you very much :) – Bog Mar 12 '24 at 15:29