PostgreSQL SELECT Statement: Syntax & Example Queries | A 101 Guide

Writing queries in PostgreSQL, you would have come across the SELECT statement every time, right? Have you ever wondered about the detailed usage of the SELECT statement?

Worry not! You have come to the right place.

This article will help you comprehensively understand the overall syntax of the PostgreSQL SELECT statement along with all the parameters. You will also come across the different kinds of use cases and query examples of the PostgreSQL SELECT statement.

What is the PostgreSQL SELECT Statement?

The PostgreSQL SELECT statement is used to query and retrieve data from one or more tables in a database in PostgreSQL. And the output data is displayed as output in a result table called a result-set. You can even use the PostgreSQL SELECT statement to retrieve and display data from multiple tables that match the specified criteria.

PostgreSQL SELECT Statement Overview

You can consider the SELECT statement to be the most complex statement in PostgreSQL that can be used with a variety of optional keywords and clauses. Move forward and you will have detailed knowledge about the PostgreSQL SELECT statement.

1) Syntax

The most basic syntax of the PostgreSQL SELECT statement is as follows:

SELECT expressions 
FROM tables 
WHERE conditions;

Where,

• expressions represent the name of all the columns you want to retrieve data from and display in the result-set.
• tables represent the names of all the tables from which you want to retrieve data.
• conditions represent the criteria that must be met for selecting the records.

However, a more detailed syntax of the PostgreSQL SELECT statement would be:

SELECT [ ALL | DISTINCT | DISTINCT ON (distinct_expressions) ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS FIRST | NULLS LAST ]]
[LIMIT [ number_rows | ALL]
[OFFSET offset_value [ ROW | ROWS ]]
[FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY]
[FOR { UPDATE | SHARE } OF table [ NOWAIT ]];

2) Parameters

All the possible parameters that can be used with the PostgreSQL SELECT statement are as follows:

• ALL: This is an optional parameter that will help return all the matching rows based on the condition mentioned.

• DISTINCT: This is an optional parameter that is used to remove duplicate values from the result-set. You can also have an elaborate idea about the DISTINCT clause in PostgreSQL here.

• DISTINCT ON: This is an optional parameter that is used to return output by removing duplicates based on the distinct_expressions.

• expressions: This is a mandatory parameter to be used along with the PostgreSQL SELECT statement. It represents all the columns or the calculations that you want you to retrieve data from. Even you can use the * sign if you want to retrieve data from all the columns of one or more tables.

• tables: This is a mandatory parameter to be used along with the PostgreSQL SELECT statement i.e, there must be at least one table after the FROM clause. It represents the name of one or more tables that you want you to retrieve data.

• WHERE conditions: This is an optional parameter. Along with the WHERE clause, you can mention the conditions on the basis of which you want to retrieve records.

• GROUP BY expressions: This is an optional parameter. Along with the GROUP BY clause, you can mention the column name(s) or expressions on the basis of which you can group the results.

• HAVING condition: This is an optional parameter. It is used along with the GROUP BY clause to limit the rows to be returned on the basis of the condition that holds to be true.

• ORDER BY expression: This is an optional parameter. Along with the ORDER BY clause, you need to mention the name of the column(s) on the basis of which you need to sort the records in your result-set.

• LIMIT: This is an optional parameter. This clause helps to control the maximum number of records to retrieve and display in the result-set. The number of rows specified by number_rows is the maximum number of records that are returned in the result-set. offset_value helps to decide the first row returned by the LIMIT clause.

• FETCH: This is an optional parameter that controls the maximum number of records to be retrieved. The number of records specified by fetch_rows will be returned in the result set. offset_value is the value that helps to determine the first row returned by the FETCH clause.

• FOR UPDATE: This is an optional parameter. Query-affected records are write-locked until the transaction is finished.

• FOR SHARE: This is an optional parameter. Records impacted by the query can be utilized by other transactions, but they cannot be updated or deleted.

Note: You can also refer to the Official Documentation of PostgreSQL SELECT statement.

PostgreSQL SELECT Statement: Example Queries

Before using the SELECT statement while writing queries to retrieve records from the tables in PostgreSQL, you first need to have knowledge about creating tables in PostgreSQL. You can follow the detailed guide for creating PostgreSQL Tables.

1) Query One Field from One Table

You can use the PostgreSQL SELECT statement to query a single column from one table.

SELECT emp_id from Employee;

In the following image, you can see the output while retrieving all the values of a single field from one table using the PostgreSQL SELECT statement.

2) Query Individual Fields from One Table

You can use the PostgreSQL SELECT statement to query a number of columns from one table.

SELECT emp_id, first_name, last_name, address 
from Employee;

This example showcases the usage of the PostgreSQL SELECT statement to display employee id, their first name, and their last name from the Employee table.

In the following image, you can encounter the output of the query using the PostgreSQL SELECT statement to retrieve data from multiple fields in a table.

3) Query All Fields from One Table

You can use the PostgreSQL SELECT statement to query all the columns of a table.

SELECT * 
from Employee;

This example showcases the usage of the PostgreSQL SELECT statement to display the values of all the columns from the Employee table.

In the following example, you can come across the output of the query for retrieving all fields of a table using the PostgreSQL SELECT statement.

4) Query fields from Multiple Tables

You can use the PostgreSQL SELECT statement to query columns from multiple tables. And you can use the JOIN statement for this purpose.

SELECT employee.emp_name, department.dept_name
FROM employee
INNER JOIN department
ON employee.dept_id = department.dept_id
ORDER BY emp_name;

This example showcases the usage of the PostgreSQL SELECT statement to display the emp_name (names of employees) and dept_name (respective department names) in their respective departments. These 2 tables are joined using the INNER JOIN statement and then the values are evaluated on the basis of the dept_id column.

In the following image, you can see the output of the query for retrieving columns on the basis of a condition after joining 2 tables.

5) With Expressions

• You can use the PostgreSQL SELECT statement with expressions without extracting data from any table.

SELECT 10*3;

This example showcases the usage of the PostgreSQL SELECT statement to display the output of any expression. In this case, the output of the above code would be 30 i.e, the result of 10*3.

• You can use the PostgreSQL SELECT statement with expressions while extracting data from any table.

SELECT f_name || ‘ ‘ || l_name
FROM Employee;

This example showcases the usage of the PostgreSQL SELECT statement to display the complete name of all the employees. In this case, the f_name and l_name are two separate columns and they individually contain information about the first names and last names of all the employees. So, here using the concatenation operators you are able to attach both the characters.

Conclusion

• This article illustrated about PostgreSQL SELECT statement as being part of all the queries in PostgreSQL.
• You had an in-depth understanding of the detailed syntax of the PostgreSQL statement and all the parameters in it.
• You also had a comprehensive understanding of how you can use the PostgreSQL SELECT statement to accommodate all your use cases.

Now, you can move forward and write your queries in PostgreSQL using the SELECT statement.