PostgreSQL SELECT Statement: Syntax, Parameters, Examples

Introduction

PostgreSQL is an open-source relational database management system (RDBMS). The system is made to handle diverse workloads and supports most operating systems. Its extensibility and SQL compliance make PostgreSQL a widely popular RDBMS.

The SELECT statement is the most commonly used data manipulation language (DML) command in PostgreSQL.

In this tutorial, you will learn how to use the PostgreSQL SELECT statement with its full syntax and examples.

Prerequisites

• PostgreSQL installed and configured.
• A database to work on (see how to create a database in PostgreSQL).

What Is PostgreSQL SELECT Statement?

The PostgreSQL SELECT statement retrieves data from a single or several tables in a database and returns the data in a result table called a result-set. Use the SELECT statement to return one or more rows matching the specified criteria from the database tables.

The SELECT statement is the most complex statement in SQL, with many optional keywords and clauses. The sections below explain the SELECT syntax in detail.

PostgreSQL SELECT Syntax

The simplest form of the SELECT statement syntax is:

SELECT [expressions] 
FROM [tables]
WHERE [conditions];

The three main parts of the statement are:

• expressions. All the columns and fields you want in the result. Specifying an asterisk (*) selects all columns.
• tables. The table(s) from which you want to extract the results. The FROM clause must contain at least one table.
• conditions. The requirements that must be met to select the records.

An example of the full SELECT statement syntax is:

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 ]];

We will explain all the parameters in the section below.

PostgreSQL SELECT Parameters

The possible parameters in a SELECT statement are:

• ALL. Returns all matching rows.
• DISTINCT. Removes duplicates from the result-set.
• DISTINCT ON. Eliminates duplicate data based on the distinct_expressions keyword.
• WHERE conditions. Contains the conditions that must be met to filter the records in the result-set.
• GROUP BY expressions. Collects data from multiple records, grouping the results by one or more columns.
• HAVING condition. Used in combination with GROUP BY. Restricts the groups of the returned rows to only the ones that meet the condition (TRUE), thus filtering them.
• ORDER BY expression. Identifies which column(s) to use to sort the data in the result-set.
• LIMIT. Sets the maximum number of records to retrieve from the table, specified by the number_rows syntax. The first row in the result-set is determined by offset_value.
• FETCH. Sets the maximum number of records in the result-set. Specify the number of records in place of the fetch_rows parameters. The offset_value determines the first row in the result-set.
• FOR UPDATE. Write-locks the records needed to run the query until the transaction completes.
• FOR SHARE. Allows the records to be used by other transactions but prevents their update or deletion.

All these parameters are optional.

PostgreSQL SELECT Statement Examples

The sections below show several use cases for the SELECT statement.

Select All Fields

The easiest way to return all fields from and see all the contents of a table is to use a PostgreSQL SELECT statement.

For example:

SELECT * FROM actor;

In the example above, the output shows all the fields contained in the actor table.

Filter Results to Match a Condition

The SELECT statement lets you filter the results using a condition. For example:

SELECT title
FROM film
WHERE language_id=1;

The example shows how to filter the selected column based on the language_id field value.

Select Fields from Multiple Tables

PostgreSQL allows you to process data from multiple tables in a database. To get results from multiple tables in a single query, use JOINS.

For example:

SELECT customer.first_name, customer.last_name, payment.amount
FROM customer 
INNER JOIN payment 
ON customer.customer_id=payment.customer_id
ORDER BY amount DESC;

In the example above, we combine two tables using INNER JOIN. We get a result-set that displays the first and last name columns from one table, and the payment amount from another table. The two tables are joined together by the customer_id column, which is the same in both tables.

The results are in descending order, specified by the ORDER BY amount DESC clause.

Select Individual Fields from One Table

The PostgreSQL SELECT statement allows you to return individual fields from a table. For example:

SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC;

The example above provides only the first and last names of the actors and leaves out other columns. The output orders the results by the last name in ascending order.

Concatenate Columns

To select a result-set and combine several columns into one, use the concatenation operator (||) and define a separator with the SELECT statement. For example:

SELECT 
first_name || ' ' || last_name
FROM 
customer;

In this example, we concatenated the first and last name columns to get the full name of each customer. A space is used as a separator between the two columns.

Calculations

Use the SELECT statement to perform calculations. In that case, omit the FROM clause. For example:

SELECT 15*3/5;

The output is the result of the mathematical expression specified in the SELECT statement.

Conclusion

This guide showed how to use the SELECT statement in PostgreSQL to process your data.

Next, learn how to install SQL Workbench for PostgreSQL.