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_name

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.

Likecolumn aliases, theAS 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 thefilm table:

SELECT f.titleFROM film AS fORDER BY f.titleLIMIT 5;

Output:

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

In this example, we assign thefilm table an aliasf and use the table alias to fully qualify thetitle column.

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

SELECT f.titleFROM film fORDER BY f.titleLIMIT 5;

2) Using table aliases in join clauses

Typically, you use table aliases in a query that has ajoin 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_name

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

alias.column_name

For example, the following query uses anINNER JOIN clause to retrieve data from thecustomer andpayment tables:

SELECT  c.customer_id,  c.first_name,  p.amount,  p.payment_dateFROM  customer c  INNER JOIN payment p ON p.customer_id = c.customer_idORDER BY  p.payment_date DESC;

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 aboutINNER JOIN in the upcoming tutorial.

3) Using table aliases in self-join

When you join a table to itself (a.k.aself-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 thefilm table twice in the same query using the table aliases:

SELECT    f1.title,    f2.title,    f1.lengthFROM    film f1INNER JOIN film f2    ON f1.film_id <> f2.film_id AND       f1.length = f2.length;

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 aboutself-join in the upcoming tutorial.

Summary

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

Last updated onJanuary 17, 2024

On this page

• Introduction to the PostgreSQL table aliases
• PostgreSQL table alias examples
• Summary