PostgreSQL Select

In PostgreSQL, the SELECT command is the core command used to retrieve data from a database table, and the data is returned in the form of a result table, which is called result-sets.

The select command contains several clauses that we can use to write a query easily. The basic task while performing the select command is to query data from tables within the database.

The various clauses of SELECT command are as follows:

• Sort rows with the help of the ORDER BY clause.
• Group rows into groups using GROUP BY clause
• Filter the rows with the help of the WHERE clause.
• Filter the groups with the help of the HAVING clause.
• Select separate rows with the help of a DISTINCT operator.
• Perform set operations with the help of UNION, INTERSECT, and EXCEPT.
• Join with other tables with joins such as LEFT JOIN, INNER JOIN, CROSS JOIN, FULL OUTER JOIN conditions.

Here, we are going to discuss the SELECT and FROM clauses and other clauses we will discuss in the further sections of the PostgreSQL tutorial.

Syntax of PostgreSQL Select command

The SELECT command is used to recover data from a single table.

The syntax of the SELECT command is as follows:

SELECT select_list
FROM table_name;

The following are the parameters used in the above syntax:

Note: If we describe a list of columns, we can use a comma to separate between two columns. If we do not need to select data from all the columns in the table, we can use an asterisk (*) instead of describing all the column names because the select list can have the exact values or the expressions.

The SQL language is case insensitive, which means select or SELECT has the same result.

Other syntax of PostgreSQL select command

SELECT column1, column2,
……
columnN 
FROM table_name;  

Here we use the below parameter:

If we want to retrieve all the fields from the table, we have to use the following syntax:

SELECT * FROM table_name;  

Examples of Select command in PostgreSQL

Here, we will understand the use of Select command in PostgreSQL with the following examples.

We will use the Employee table, which we created earlier in the TheDeveloperBlog database's Company schema, for our better understanding.

• To query data from one column using the SELECT command

In this example, we will find the names of all Employee's from the employee table with SELECT command's help:

Select 
name 
from "Company".employee;

Output

Once we perform the above query, we will get the below result:

Note:

• To separate two SQL statements, we will use the semicolon (;).
• In this above query, at the end of the select command, we added a semicolon (;). At this point, the semicolon is not a part of the SQL declaration as it is used to indicate PostgreSQL the end of an SQL command.

• To query data from multiple columns using the SELECT command

If we want to see the data of multiple columns of a particular table, we can execute the below query.

For example, let us assume that we need to get the employee's name, age, and address. Therefore, we can define these column names in the SELECT command as we see in the below query:

select 
name, 
age, 
address 
from "Company".employee;

Output

After executing the above command, we will get the below outcome:

• To query data in all columns of a table using the Select Command

If we want to get all the columns data in a particular table, we can execute the below query.

Here, we select all the columns and rows from an ,employee table under the Company schema with the below query's help:

SELECT
   *
FROM
"Company".employee;

Output

After executing the above query, we will get the following result:

In the above example, we used the (*) asterisk symbol rather than writing all the column's names in the select command. Sometimes we have n-numbers of columns in the table, and writing all the column names became a tedious process.

But sometimes it is not a good process to use the asterisk (*) in the SELECT command.

If we are using the embedded SQL statements in the code because of the following reasons:

• We have one table with several columns, and it is not necessary to use the SELECT command with an asterisk (*) shorthand for recovering the data from all columns of the table.
• Besides that, if we retrieve the database's unimportant data, it will enhance the load between the database and application layers. And the output of our applications will be less accessible and deliberate. Thus, it is the best approach to describe the column names openly in the SELECT clause because every time, it is likely to get only needed data from a table.

• Using the SELECT command with expressions

In the below example, we will return the full name and address of all the employee with the help of select command:

SELECT 
name  AS full_name,
address
FROM 
 "Company".employee;

In the above query, we will use a column alias AS full_name to assign a column heading to the name expression.

Output

After executing the above query, we will get the below result:

• Using the SELECT command with expressions

Here, we will perform the select command with an expression where we skip the From clause into the select command, as the command does not refer to any table.

SELECT 4* 2 AS result;

Output

We will get the below output once we execute the above command: