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.

PostgreSQL SELECT Statement

Prerequisites

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.

Note: We recommend using our Bare Metal Cloud servers for storing your database. You can choose to store the database on a BMC server and keep other parts of the application in different cloud environments. Deploy a Bare Metal Cloud instance in only a few clicks.

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.

Note: Learn about the available data types in PostgreSQL.

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.

Note: Sometimes, queries take a long time to execute if there is a lot of data to process. See which tools you can use to optimize your queries and speed them up.

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;
Selecting all columns in a table using the PostgreSQL SELECT statement.

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;
Filter results of a SELECT statement using the WHERE clause.

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;
Use the SELECT statement to combine data from multiple tables.

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.

Note: MySQL and PostgreSQL are the most popular database management systems. MySQL also supports JOINS for combining data from multiple tables.

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;
Using the SELECT statement to select individual fields from a table.

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;
Concatenate Columns with select statement

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.

Was this article helpful?
YesNo
Bosko Marijan
Having worked as an educator and content writer, combined with his lifelong passion for all things high-tech, Bosko strives to simplify intricate concepts and make them user-friendly. That has led him to technical writing at PhoenixNAP, where he continues his mission of spreading knowledge.
Next you should read
How to List All Databases in PostgreSQL
June 9, 2021

Follow this simple tutorial to learn three different ways to list all databases in PostgreSQL.
Read more
How to Check Your PostgreSQL Version
April 21, 2020

Use this guide to check your PostgreSQL version with a few straightforward commands. You can retrieve the PostgresSQL version directly from your command line or use an effective SQL statement within the PostgreSQL shell.
Read more
How to Export a PostgreSQL Table to CSV
March 17, 2020

Learn how to export a PostgreSQL table to a .csv file. This feature is especially helpful when transferring the table to a different system or importing it to another database application.
Read more
How to Install SQL Workbench for PostgreSQL
March 13, 2020

Save time and effort by managing different database systems with a single tool. Find out how to set up SQL Workbench to connect to a PostgreSQL database with four (4) easy steps.
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.