order table content - not the output

Question

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^^

score 1 · Answer 1 · answered Mar 12 '24 at 10:55

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

order table content - not the output

1 Answers1