PostgreSQL SELECT Statement {Syntax + Examples} – phoenixNAP

Introduction

PostgreSQL is an open source relational database management system (RDBMS). The database system easily handles various workloads and is compatible with 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 complete syntax and examples.

Prerequisites

• PostgreSQL installed and configured.

A database

• to work in (see how to create a database).

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

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

PostgreSQL SELECT syntax

The simplest form of SELECT statement syntax is: expressions SELECT

FROM tables WHERE conditions;

• Expressions are all the columns and fields

that you want in the result. The table

• syntax is the table or tables from which you want to extract the results

.

• The conditions represent the requirements that must be met to select the records.

An example of the complete syntax of the SELECT statement is:

SELECT [ ALL | DIFFERENT | DISTINCT ON (distinct_expressions) ] expressions FROM tables [WHERE conditions] [GROUP BY expressions] [HAVING condition] [ORDER BY expression [ ASC | ESCR | USING operator ] [ NULLS FIRST | NULLS LAST ]] [LIMIT [ number_rows | ALL] [DETOUR 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 next section.

PostgreSQL

SELECT parameters

The possible parameters in a SELECT statement are

:

• ALL: Optional parameter that returns all matching rows

. DISTINCT – A parameter that removes

• duplicates from the result set. DISTINCT
• ON: Optional parameter that removes duplicate data based on keyword distinct_expressions.
• Expressions: All the columns and fields that you want to include 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 to filter the records in the result set.
• GROUP BY expressions: 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. Restricts the groups in the returned rows to only those that meet the TRUE condition, filtering them.
• ORDER BY expression: Optional clause that identifies which column or columns should be used to sort the data in the result set.
• LIMIT: Optional clause that sets the maximum number of records to retrieve from the table, specified by the number_rows syntax. The first row of 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 instead of the fetch_rows syntax. The offset_value determines the first row of the result set.
• FOR UPDATE: An optional clause that writes the records needed to execute the query until the transaction is complete.
• FOR SHARE – An optional clause that allows records to be used for other transactions, but prevents their updating or deletion.

PostgreSQL SELECT Statement Examples

The following sections show several use cases for the

SELECT statement.

Example 1: Select

all fields

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

For example:

SELECT

* FROM actor;

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

Example 2: Filter

the results to match a condition

The SELECT statement allows you to filter the results by setting a condition. In the following example, we want to show only movie titles where the movie 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 one 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 combined two tables using INNER JOIN to get a result set that shows the first and last name columns of one table and the payment amount of another table. The two tables are joined 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 a 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 name of the actors and omits other columns. The output sorts the results by last name in ascending order.

Example 5: Concatenate

columns If you want the result set to combine multiple columns into one, you can use the || concatenation operator with the SELECT statement. For example:

SELECT first_name || ‘ ‘ || last_name OF the client;

In this example, we concatenate 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 omit the FROM clause. For example:

SELECT 15*3/5;

The result 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 DBMSs, take a look at our list of the best database management software to choose the one that best suits your needs.