It supports both JSON that is non-rational and rational SQL, querying. It is applicable on almost all operating systems, including Windows, macOS, Linux, and OpenBSD as well. Many companies are currently using Postgresql in their products and services like Apple, Cisco, Instagram, etc.
Postgresql is an extensible database and user-friendly because it allows customization, and users are capable of defining their own data types, functional languages, etc. Custom plugins are also developed if you don’t think that the default setting will meet your requirement. Postgresql has many features, but those that discriminate it from others are Nested transactions and Asynchronous replication.
Now moving towards the working of Postgresql. Our current mode of discussion is using a query of order in Postgresql. All commands used in Postgresql are almost the same as SQL. However, we have used two approaches to demonstrate the working of order by clause in Postgresql.
- Applying PostgreSQL order by clause via SQL shell (psql)
- Applying PostgreSQL order by clause using pgAdmin
Applying PostgreSQL order by clause via SQL shell (psql)
After the Postgresql is successfully installed on your system, you can now open sql shell (psql). As you have defined the local hostname and database name, provide a password to proceed further. ‘Postgres’ is the default name of the database. We have also used the same in this tutorial.
Take an example of a table. In Postgresql, table creation is very simple and defined. The table is created through a ‘create’ command, and we use an ‘insert’ key to add values to the table. This method is discussed later in the guide. Coming back to the table named ‘car’ having the attributes of the car as column names. Use the select command to fetch all records of the table.
Now we will apply the query in which we want to display only two columns from the table.
And the result must be in ascending order concerning the color. So all the colors in the column are ordered alphabetically.
Consider another table named worker that contains the information about every working person. Use the select command to show the data of the table.
In this case, we want to fetch the record in descending order concerning the column fname. Whereas, two column’s record is fetched accordingly in the table.
You can see that the result of the fname column is arranged.
Consider the same table worker. In this example, we created a column at run time as we said that postgresql is customizable and flexible. In this created column, we will show the result. The length of the last name of the worker is calculated by using a built-in function ‘LENGTH’; the syntax of the function is;
This function will display the total words in the name. We will use the query to fetch two columns, lname, and the ‘len’ column. The data in the ‘len’ column is then arranged in descending order.
The resultant columns are shown with the length in descending order.
We will talk about the null values in the table. Here we will create a new table ‘sample’ by using the create query. Here the column name is defined along with the data type that is an integer value.
The table will be created now. We will add the values in the respective column. Here we have entered all the values in a single line. You may add records separately. One row is entered as null. This null will be shown as a space in the table.
Use the select statement to see the values you inserted. If we want to display the column data according to that column particularly, you will notice that each value is arranged ascendingly with the null value at the end by default.
In other words, if we want to arrange data in ascending order, we don’t need to mention it. Now we want the null value to appear in the first row. For that purpose, we will use:
There is a different way of arranging data of the table. Consider the table worker. We want to display the ‘yor’ of the car in descending order. For this, we have used angular bracket “>”.
The result will be in ascending order if you add the opposite angular bracket after the keyword ‘USING’. The output shows the ‘yor’ in descending order.
Applying PostgreSQL order by clause using pgAdmin
Now the second approach is through the pgAdmin of Postgresql.
Launch postgresql workbench first. Now the user is asked to write the saved password that the user has already given at the configuration time.
After pressing ‘OK’ that dialogue box will be removed. An interface will be displayed that is the home dashboard of Postgresql. To apply queries, navigate to the left side of the page. Expand the server; further, expand postgresql and then databases. Right-click the name ‘postgres’ of the database. A drop-down will appear. From here, select ‘query tool’. After selecting that, you will see that interface is changed.
Execute a simple select query. For execution, go to the menu bar and select the execute/refresh button.
Now you can see the result at the bottom of the page; that is the resultant portion. The relevant image is attached below.
Here take an example of a table as described earlier ‘worker’. Display the names of the city of a worker in ascending order. Use the query below
Execute the query; the result will be as follow.
You can understand the concept in two ways, as described in the article. Postgresql provides the easiest ways of applying queries of order by clause. Only you need to have configured postgresql in your system.