PostgreSQL SELECT statement

This article describes the basic syntax of the SELECT statement and how to use the SELECT statement to query data from a data table.

In PostgreSQL, the SELECT statement is used to retrieve data from one or more tables, and it is probably the most used statement.

PostgreSQL SELECT syntax

Let’s start with the basic form of a SELECT statement that retrieves data from a single table.

The syntax of the SELECT statement:

SELECT
    expr_list
FROM
    table_name
[other_clauses];

In this syntax:

  • The SELECT and the FROM are keywords.

  • The expr_list is a list of columns or expressions to select. Multiple columns or expressions need to be separated by commas.

  • The table_name is name of the table where to retrieve data.

  • The FROM table_name is optional. You can omit the FROM clause if you do retrieve data from a table.

  • The other_clauses are clauses supported by the SELECT statement. The SELECT statement support many clauses, including:

    • Use the ORDER BY clause to sort rows.
    • Use the WHERE clause to filter rows.
    • Use the LIMIT or FETCH clause to select a subset of rows from a table.
    • Use the GROUP BY clause to group rows.
    • Use the HAVING clause filter groups.
    • Join with other tables using something like INNER JOIN, LEFT JOIN, FULL OUTER JOIN, and CROSS JOIN etc.
    • Use UNION, INTERSECT and EXCEPT to perform set operations.

Note that SQL keywords are not case sensitive. But to make SQL code easier to read, it is a good coding practice to write SQL keywords in uppercase.

In this tutorial, we will focus on the basic usage of the SELECT statement, and the other related clauses will be learned in subsequent tutorials.

PostgreSQL SELECT Examples

Let’s look at some examples of using PostgreSQL SELECT statements.

We will demonstrate using the customer table in the PostgreSQL Sakila sample database.

Query a column of data using a PostgreSQL SELECT statement

The following SELECT statement finds all first names from a customer table:

SELECT first_name FROM customer;

Here is part of the output:

 first_name
------------
 MARY
 PATRICIA
 LINDA
 BARBARA
 ELIZABETH
 JENNIFER
 MARIA
 SUSAN
 MARGARET
 DOROTHY

Note that we added a semicolon (;) at the end of the SELECT statement. The semicolon is not part of the SQL statement. It is a signal of the end of an SQL statement. A semicolon is also used to separate two SQL statements.

Example of querying multiple columns of data using PostgreSQL SELECT statements

If you want to know the customer’s first name, last name, and email, you can specify these column names in the SELECT statement, as shown in the following query:

SELECT
   first_name,
   last_name,
   email
FROM
   customer;

Here is part of the output:

 first_name | last_name |                email
------------+-----------+-------------------------------------
 MARY       | SMITH     | [email protected]
 PATRICIA   | JOHNSON   | [email protected]
 LINDA      | WILLIAMS  | [email protected]
 BARBARA    | JONES     | [email protected]
 ELIZABETH  | BROWN     | [email protected]
 JENNIFER   | DAVIS     | [email protected]
 MARIA      | MILLER    | [email protected]
 SUSAN      | WILSON    | [email protected]
 MARGARET   | MOORE     | [email protected]
 DOROTHY    | TAYLOR    | [email protected]

Example of querying all columns of a table using a PostgreSQL SELECT statement

If you want to use the SELECT statement to find all the columns in the customer table, use the following statement:

SELECT * FROM customer;

Here is part of the output:

 customer_id | store_id | first_name | last_name |                email                | address_id | activebool | create_date |     last_update     | active
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+---------------------+--------
           1 |        1 | MARY       | SMITH     | [email protected]       |          5 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           2 |        1 | PATRICIA   | JOHNSON   | [email protected] |          6 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           3 |        1 | LINDA      | WILLIAMS  | [email protected]   |          7 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           4 |        2 | BARBARA    | JONES     | [email protected]    |          8 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           5 |        1 | ELIZABETH  | BROWN     | [email protected]  |          9 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           6 |        2 | JENNIFER   | DAVIS     | [email protected]   |         10 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           7 |        1 | MARIA      | MILLER    | [email protected]     |         11 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           8 |        2 | SUSAN      | WILSON    | [email protected]     |         12 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
           9 |        2 | MARGARET   | MOORE     | [email protected]   |         13 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1
          10 |        1 | DOROTHY    | TAYLOR    | [email protected]   |         14 | t          | 2006-02-14  | 2006-02-15 04:57:20 |      1

In this example, we used an asterisk (*) in the SELECT statement, which is shorthand for all columns. The asterisk (*) allows us to type less without customer listing all the column names in the table.

However, it is not a good practice to use an asterisk (*) in a SELECT statement for the following reasons:

  1. Database performance. Assuming you have a table with many columns and a large amount of data, the SELECT statement with the asterisk (*) shorthand will select data from all columns of the table, which may not be necessary for the application.

  2. Application performance. Retrieving unnecessary data from the database increases the traffic between the database server and the application server. As a result, your application may be slower and less scalable.

For these reasons, it is best to specify column names explicitly in the SELECT statement so that only the necessary data is fetched from the database.

For instant queries that examine database data, you can use the asterisk (*) shorthand.

Example using PostgreSQL SELECT statement with expressions

In addition to column names, you can also use expressions in SELECT statement. The following example uses the SELECT statement to return the full names and emails of all customers:

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

Here is part of the output:

     ?column?     |                email
------------------+-------------------------------------
 MARY SMITH       | [email protected]
 PATRICIA JOHNSON | [email protected]
 LINDA WILLIAMS   | [email protected]
 BARBARA JONES    | [email protected]
 ELIZABETH BROWN  | [email protected]
 JENNIFER DAVIS   | [email protected]
 MARIA MILLER     | [email protected]
 SUSAN WILSON     | [email protected]
 MARGARET MOORE   | [email protected]
 DOROTHY TAYLOR   | [email protected]

In this example, we use the string concatenation operator || to concatenate each customer’s first name and last name.

You’ll learn how to use column aliases to assign more meaningful names to expressions in the subsequent tutorial.

Examples of Evaluating Expressions Using PostgreSQL SELECT Statements

If you just want to simply evaluate an expression, you can omit the FROM clause in the SELECT statement. The following SELECT statement is used to compute the result of 5 * 3:

SELECT 5 * 3;

Here is the output:

 ?column?
----------
       15

Conclusion

In this tutorial, you learned how to query data from a single table using the basic form of the PostgreSQL SELECT statement. If you do not need to query data from any tables, you can omit the FROM clause in the SELECT statement.