PostgreSQL SELECT Statement: Retrieving Data from a Table

Summary: in this tutorial, you will learn how to use the PostgreSQL SELECT statement to retrieve data from a table.

PostgreSQL SELECT statement

To retrieve data from a table, you use the PostgreSQL SELECT statement.

Here’s the basic syntax of the SELECT statement:

SELECT column1, column2, ...
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, list one or more columns of a table to retrieve data after the SELECT keyword. You use a comma to separate multiple columns.
Second, specify the table name in the FROM keyword.

When executing the SELECT FROM statement, PostgreSQL evaluates the FROM clause before the SELECT clause.

In the SELECT clause, you can retrieve data from one column:

SELECT column1
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Or you can retrieve data from multiple columns:

SELECT column1, column2
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want to retrieve data from all the columns of a table, you can list out all the columns with the assumption that the table_name has three columns:

SELECT column1, column2, column3
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Or you can use SELECT * to retrieve data from all the column of the table:

SELECT *
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Asterisk (*) or star is a shortcut for all columns. The SELECT * is often pronounced as SELECT star.

Selecting data from all table columns

The following example uses the SELECT statement to retrieve data from all the columns of the inventories table:

SELECT * FROM inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  brand  | quantity |  price
------------------+---------+----------+---------
 iPhone 14 Pro    | Apple   |       10 |  999.99
 Galaxy S23 Ultra | Samsung |       15 | 1199.99
 Pixel 7 Pro      | Google  |        8 |  899.99
 Xperia 1 IV      | Sony    |        7 | 1299.99Code language: plaintext (plaintext)

It returns four rows in the inventories table.

Selecting data from some columns

The following example uses the SELECT statement to retrieve data from the name and price columns:

SELECT
  name,
  price
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  price
------------------+---------
 iPhone 14 Pro    |  999.99
 Galaxy S23 Ultra | 1199.99
 Pixel 7 Pro      |  899.99
 Xperia 1 IV      | 1299.99Code language: plaintext (plaintext)

The SELECT statement returns data from the name and price columns only.

Using PostgreSQL SELECT statement to transform data

So far, you learned how to pull the data stored from the table using the SELECT statement. The SELECT statement can also retrieve and transform the data from a table.

For example, you can use the SELECT statement to calculate the inventory amount by multiplying the quantity with the price for each product in the inventories table:

SELECT
  name,
  quantity * price
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       | ?column?
------------------+----------
 iPhone 14 Pro    |  9999.90
 Galaxy S23 Ultra | 17999.85
 Pixel 7 Pro      |  7199.92
 Xperia 1 IV      |  9099.93Code language: plaintext (plaintext)

In this statement, we retrieve data from the name column of the inventories table. Additionally, we retrieve data from the quantity and price columns and multiply them at the same time.

Besides the multiplication operator (*) you can use other mathematical operators such as addition (+), subtraction (-), and division (/).

If you perform a calculation on columns in the SELECT statement, the column is often referred to as a calculated column.

Column alias

Note that the output of the calculated column (quantity * price) is ?column?. This is a temporary column name that PostgreSQL assign to it. However, its name is not meaningful.

To assign a meaningful column name to a calculated column (or a regular column), you can use a column alias.

A column alias is a temporary column name that you assign to the column in the SELECT statement.

Here’s the syntax for defining a column alias:

column_name AS column_aliasCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, you specify the AS keyword and the column alias after the column name.

Since the AS keyword is optional, you can omit it like this:

column_name column_aliasCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, you can assign a column alias to the calculated column as follows:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  amount
------------------+----------
 iPhone 14 Pro    |  9999.90
 Galaxy S23 Ultra | 17999.85
 Pixel 7 Pro      |  7199.92
 Xperia 1 IV      |  9099.93Code language: plaintext (plaintext)

The output shows that the ?column? becomes amount.

Alternatively, you can use a column alias without the AS keyword:

SELECT
  name,
  quantity * price amount
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

It returns the same output.

It’s important to note that you can assign a column alias to any column, not limited to the calculated column.

For example, the following statement assigns the column alias product_name to the name column:

SELECT
  name AS product_name,
  quantity * price AS amount
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

   product_name   |  amount
------------------+----------
 iPhone 14 Pro    |  9999.90
 Galaxy S23 Ultra | 17999.85
 Pixel 7 Pro      |  7199.92
 Xperia 1 IV      |  9099.93Code language: plaintext (plaintext)

Summary

Use the PostgreSQL SELECT statement to retrieve data from one or more column of a table.
Use the SELECT * to retrieve data from all columns of a table.
PostgreSQL evaluates the FROM clause before the SELECT clause.
Use a column alias to assign a temporary column to a column in the SELECT statement