PostgreSQL SELECT Statement {Syntax + Examples}

Introduction

PostgreSQL is an open-source relational database management system (RDBMS). The database system easily handles 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 to use the PostgreSQL SELECT statement with its full syntax and examples.

Prerequisites

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

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 expressions are all the columns and fields you want in the result.
• The tables syntax is the table or tables from which you want to extract the results.
• The conditions represent the requirements that must be met in order 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 - An optional parameter that returns all matching rows.
• DISTINCT - A parameter that removes duplicates from the result-set.
• DISTINCT ON - An optional parameter that eliminates duplicate data based on the distinct_expressions keyword.
• expressions - All the columns and fields you want included in the result. Specifying an asterisk (*) selects all columns.
• tables - Specify the tables from which you want to retrieve records. The FROM clause must contain at least one table.
• WHERE conditions - The clause is optional and contains the conditions that must be met in order to filter the records in the result-set.
• GROUP BY expressions - An optional clause that collects data from multiple records, grouping the results by one or more columns.
• HAVING condition - An optional clause used in combination with GROUP BY. It restricts the groups of the returned rows to only the ones that meet the condition TRUE, thus filtering them.
• ORDER BY expression - An optional clause that identifies which column or columns to use to sort the data in the result-set.
• LIMIT - An optional clause that 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 - An optional clause that sets the maximum number of records in the result-set. Specify the number of records in place of the fetch_rows syntax. The offset_value determines the first row in the result-set.
• FOR UPDATE - An optional clause that write-locks the records needed for running the query until the transaction completes.
• FOR SHARE - An optional clause that allows the records to be used by other transactions but prevents their update or deletion.

PostgreSQL SELECT Statement Examples

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

Example 1: 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.

Example 2: Filter Results to Match a Condition

The SELECT statement lets you filter the results by setting a condition. In the following example, we want to display only the film titles where the film language is English (language_id=1):

SELECT title
FROM film
WHERE language_id=1;

Example 3: 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 to 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.

Example 4: 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.

Example 5: Concatenate Columns

If you want your result-set to combine several columns into one, you can use the concatenation operator || 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.

Example 6: Calculations

You can also use the SELECT statement to perform some calculations, but then you 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

Now you should know how to use the SELECT statement in PostgreSQL to process your data. If you are interested in other DBMS, take a look at our list of top database management software to choose the one that best suits your needs.