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:
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:
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
Here we use the below parameter:
If we want to retrieve all the fields from the table, we have to use the following syntax:
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 javatpoint database's Company schema, for our better understanding.
In this example, we will find the names of all Employee's from the employee table with SELECT command's help:
Once we perform the above query, we will get the below result:
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:
After executing the above command, we will get the below outcome:
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:
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:
In the below example, we will return the full name and address of all the employee with the help of select command:
In the above query, we will use a column alias AS full_name to assign a column heading to the name expression.
After executing the above query, we will get the below result:
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.
We will get the below output once we execute the above command: