PostgreSQL Clauses

PostgreSQL Clauses

Different types of clauses supported by PostgreSQL are:

  • PostgreSQL Where
  • PostgreSQL Order By
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Distinct
  • PostgreSQL Limit
  • PostgreSQL Fetch

PostgreSQL Where Clauses

Where condition is used to filter the output that is obtained by the ‘Select’, ’Update’, ’Delete’ commands. The output of the where condition is only obtained when the condition is fulfilled.

The syntax for the where clause is:

 SELECT column1, column2, .....columnN
 FROM table_name
 WHERE [search_condition]     

Let us understand it with the help of the example.

PostgreSQL Clauses

A table named employee is created having three columns named name, age, and salary. And we have entered four rows in the table. And these four rows are clearly visible in the output of the select query without any clauses.

Now let us apply where clause on the output obtained from this query, i.e., the select query.

PostgreSQL Clauses

As shown in the image displayed above, the number of records displayed in this query are less as compared to the previous query because of the reason that where clause filters those records where the age of the employee is greater than 22.

We can also add another constraint to this query with the help of the AND operator.

PostgreSQL Clauses

In the previous state, we had two records having age greater than 22 and here in this case we have also included another constraint that the salary should be less than 60 so we are left with only one record.

So, the record that is displayed at the end of the query is passed through two filters one is having age greater than 22 and the other is the salary having less than 60.

PostgreSQL Order by clause

The Order by clause is used to display the data in a sorted form that we can sort the output in descending or ascending sequence. By default, the sequence of the display is ascending but that can be changed by passing the option in descending sequence.

The order by clause has the syntax:

 SELECT column_or_columns
 FROM name_of_table
 [WHERE condition]   
 [ORDER BY columnOne, columnTwo, ..columnN] [ASC | DESC];    

Let us consider an example for a better understanding of the clause.

PostgreSQL Clauses

This is the table on which we are going to apply the order by clause. Initially, we have records displayed in the sequence of their addition. But once we apply the order by clause on it, it’ll be sorted according to the query.

PostgreSQL Clauses

So, after we applied the order by clause on the age column of the employee table, we can see the records are displayed in the ascending sequence of the ages.

The records can be displayed in descending sequence by using the query.

SELECT * FROM myschema.employee ORDER BY age DESC;
PostgreSQL Clauses

Now after using the ‘desc’ keyword, the records are displayed in descending order.

So, this order by clause can be applied to any column irrespective of the data type that column has.

PostgreSQL Group By

The Group By clause of PostgreSQL is used to display the repetitive values once only. We can calculate the count and the sum of those repetitive values in the table. The Group By clause is used to remove the redundancy in the result.

The Group By clause can easily be used with count(), sum() function, etc.

The syntax of the Group by clause is

  SELECT column-list   
 FROM table_name
 WHERE [conditions ]
 GROUP BY column1, column2....columnN  ; 
PostgreSQL Clauses

In the above-shown example, we have two records having the same value for the age column that is both the record number 2 and 5 have the same age value, i.e., 22.

Now we will apply order by clause on these records and observe the output.

PostgreSQL Clauses

Before applying the order by clause, we have a total of 5 records among which 2 have redundant data for the age column, but after applying order by clause on the age column, we get only 4 records as output that means the redundant data is not displayed in the output of the query.

We can also get the count of each data in a particular column in the table by using the count() with the order by clause. The syntax for this query will be :

SELECT age,count(age) FROM myschema.employee GROUP BY age;

The output after successfully executing this query is:

PostgreSQL Clauses