PostgreSQL Table Aliases

Summary: in this tutorial, you will learn about the PostgreSQL table aliases and their practical applications.

Introduction to the PostgreSQL table aliases

A table alias is a feature in SQL that allows you to assign a temporary name to a table during the execution of a query.

The following illustrates the syntax of defining a table alias:

table_name AS alias_nameCode language: SQL (Structured Query Language) (sql)

In this syntax:

• table_name: Specify the name of the table that you want to give an alias.
• alias_name: Provide the alias for the table.

Like column aliases, the AS keyword is optional, meaning that you can omit it like this:

table_name alias_name

PostgreSQL table alias examples

Let’s take some examples of using table aliases.

1) Basic PostgreSQL table alias example

The following example uses a table alias to retrieve five titles from the film table:

SELECT f.title
FROM film AS f
ORDER BY f.title
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

Output:

      title
------------------
 Academy Dinosaur
 Ace Goldfinger
 Adaptation Holes
 Affair Prejudice
 African Egg
(5 rows)

In this example, we assign the film table an alias f and use the table alias to fully qualify the title column.

Since the AS keyword is optional, you can remove it as follows:

SELECT f.title
FROM film f
ORDER BY f.title
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

2) Using table aliases in join clauses

Typically, you use table aliases in a query that has a join clause to retrieve data from multiple related tables that share the same column name.

If you use the same column name that comes from multiple tables in the same query without fully qualifying them, you will get an error.

To avoid this error, you can qualify the columns using the following syntax:

table_name.column_nameCode language: CSS (css)

If the table has an alias, you can qualify its column using the alias:

alias.column_nameCode language: CSS (css)

For example, the following query uses an INNER JOIN clause to retrieve data from the customer and payment tables:

SELECT 
  c.customer_id, 
  c.first_name, 
  p.amount, 
  p.payment_date 
FROM 
  customer c 
  INNER JOIN payment p ON p.customer_id = c.customer_id 
ORDER BY 
  p.payment_date DESC;Code language: SQL (Structured Query Language) (sql)

Output:

 customer_id | first_name  | amount |        payment_date
-------------+-------------+--------+----------------------------
          94 | Norma       |   4.99 | 2007-05-14 13:44:29.996577
         264 | Gwendolyn   |   2.99 | 2007-05-14 13:44:29.996577
         263 | Hilda       |   0.99 | 2007-05-14 13:44:29.996577
         252 | Mattie      |   4.99 | 2007-05-14 13:44:29.996577

Note that you’ll learn about INNER JOIN in the upcoming tutorial.

3) Using table aliases in self-join

When you join a table to itself (a.k.a self-join), you need to use table aliases. This is because referencing the same table multiple times within a query will result in an error.

The following example shows how to reference the film table twice in the same query using the table aliases:

SELECT
    f1.title,
    f2.title,
    f1.length
FROM
    film f1
INNER JOIN film f2 
    ON f1.film_id <> f2.film_id AND 
       f1.length = f2.length;Code language: SQL (Structured Query Language) (sql)

Output:

            title            |            title            | length
-----------------------------+-----------------------------+--------
 Chamber Italian             | Resurrection Silverado      |    117
 Chamber Italian             | Magic Mallrats              |    117
 Chamber Italian             | Graffiti Love               |    117
 Chamber Italian             | Affair Prejudice            |    117
 Grosse Wonderful            | Hurricane Affair            |     49
 Grosse Wonderful            | Hook Chariots               |     49
 Grosse Wonderful            | Heavenly Gun                |     49
 Grosse Wonderful            | Doors President             |     49
...

Note that you’ll learn about self-join in the upcoming tutorial.

Summary

• Use PostgreSQL table aliases to assign a temporary name to a table during the execution of a query.