how to sort a column in postgresql and set it to sorted values-CodePudding

I am new to PostgreSQL and would like to know how to set a column in a table to its sorted version. For example: (column: points) 5,7,3,9,8,4. set to 3,4,5,7,8,9.

My incorrect version:

UPDATE outputTable SET points_count =  (SELECT points_count FROM outputTable ORDER BY points_count ASC)

CodePudding user response：

Try with this :

UPDATE outputTable 
SET    points_count =  s.points_count
FROM   (SELECT points_count FROM outputTable ORDER BY points_count ASC) s
WHERE  outputTable.ctid = s.ctid;

As you are planning to update same table with reference to same table, you will need row level equality criteria like ctid to update each row.

CodePudding user response：

It seems like you want to sort the rows in a table.

Now this is normally a pointless exercise, since tables have no fixed order. In fact, every UPDATE will change the order of rows in a PostgreSQL table.

The only way you can get a certain order in the result rows of a query is by using the ORDER BY clause, which will sort the rows regardless of their physical order in the table (which is not dependable, as mentioned above).

There is one use case for physically reordering a table: an index range scan using an index on points_count will be much more efficient if the table is physically sorted like the index. The reason is that far fewer table blocks will be accessed.

Therefore, there is a way to rewrite the table in a certain order as long as you have an index on the column:

CREATE INDEX ON outputtable (points_count);

CLUSTER outputtable USING points_count;

But – as I said above – unless you plan a range scan on that index, the exercise is pointless.