PostgreSQL table data can be sorted based on some criteria. The data is usually sorted in ascending or descending order by making use of the ORDER BY clause and the ASC and DESC keywords(used to specify the sorting order). We can also custom-sort the data according to our needs and requirements. The following paragraphs will demonstrate about the methods that can be used to custom-sort data. These methods are:

● Method 1: Custom Sort Using CASE

● Method 2: Custom Sort Using array_position Function

● Method 3: Custom Sort JOIN

Let’s get started with the article to see how can we custom-sort our data using these methods.

How to Custom Sort in PostgreSQL ORDER BY Clause?

We can customize the sorting order as per our wish by using some methods enlisted above. Let’s see how these functions can be utilized to customize the sorting order.

Method 1: Custom Sort Using CASE

To custom sort the data in PostgreSQL, the most commonly used way is by doing it using CASE. the basic syntax for this method to perform custom sorting is:

SELECT * FROM tab_name
 ORDER BY CASE 
  WHEN col_value = "val1" THEN   priority_1
  WHEN col_value = "val2" THEN priority_2
  WHEN col_value = "val3" THEN priority_3
  ELSE priority_n 
  END;

This method specifies each value of the column in a specific order or priority. Consider the table named “projects” to implement the custom sorting methods on it. The table is given as:

Now we will be sorting the projects by their statuses in the following order:

Tested => Completed => Pending => TO-DO

The query can be written as:

SELECT * FROM projects
 ORDER BY CASE
  WHEN proj_status = 'Tested' then 1
  WHEN proj_status = 'Completed' then 2 
  WHEN proj_status = 'Pending' then 3
  WHEN proj_status = 'TO-DO' then 4
  END;

In the above syntax, we have simply specified the sorting order. This query will return the custom-sorted table. The output is given as:

This is how we can sort tables according to our wishes.

Let's move towards another alternative method to do the same thing.

Method 2: Custom Sort Using array_position Function

An alternate method to do custom sorting is by using the array_position() function. The array takes the two required parameters. The 1st one is the array and the 2nd is the element whose position is to be found in the array. In our case, we will provide the sorted array, as we want the table to be sorted, and give the column name from the table on which we want to apply sorting. The query can be written as:

SELECT * FROM projects
ORDER BY array_position(array['Tested','Completed','Pending','TO-DO'], proj_status);

The query will return us the custom sorted array same as we needed. The output is:

So this is how we can perform custom sorting.

Method 3: Custom Sort Using JOIN

We can then JOIN to order the data in a customized way. Consider the query for our use case using this method:

SELECT * FROM projects
 JOIN (VALUES ('Tested', 1), ('Completed',2), ('Pending', 3), ('TO-DO', 4)) 
 as o(value, sorting_number) ON proj_status = o.value
 ORDER BY o.sorting_number;

The above query fetches all the columns from the “projects” table and joins them with a temporary table. The temporary table contains the status values and sorting numbers. Finally, the ORDER BY clause sorts the result set according to the sorting_number.

The query results in the customized ordered data which looks like this:

The output shows that our query worked well.

So these are the approaches to customize and sort the data in PostgreSQL.

Conclusion

In PostgreSQL, the ordering is done by using the ASC and DESC keywords but we can also customize the sorting order using several ways; the first one is the most commonly used for this purpose i.e. by using the CASE keyword, the second is by using the array_position() function and the third one is by using JOIN. In this blog, we have discussed all three methods of custom-sorting the data according to our requirements with valid implementation.