PostgreSQL Table Alias

This article describes how to use table aliases in PostgreSQL.

In PostgreSQL, in addition to aliasing columns, you can also alias tables. Table aliases are generally used for the following purposes:

  • Table aliases can improve the readability of SQL statements.
  • Table aliases can bring convenience to writing SQL.
  • Table aliases can resolve conflicts that different tables have the same column names.

PostgreSQL table alias syntax

To specify an alias for a table in PostgreSQL, follow this syntax:

table_name [AS] table_alias;

Explanation,

  • The table_alias a alias for table_name.
  • If the table alias contains spaces, enclose it the ". However, in most cases, table aliases are used for simplicity, and table aliases with spaces are not recommended.
  • The AS keyword is optional, it can be omitted.

Table alias Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the Sakila sample database, all the films are stored in the film table, and the inventory information for the films is stored in the inventory table.

To find films without inventory records from the film table, use the following statement:

SELECT
    f.film_id,
    f.title
FROM film f
WHERE
    NOT EXISTS (
        SELECT 1
        FROM inventory i
        WHERE i.film_id = f.film_id
    );

 film_id |         title
---------+------------------------
      14 | ALICE FANTASIA
      33 | APOLLO TEEN
      36 | ARGONAUTS TOWN
      38 | ARK RIDGEMONT
      41 | ARSENIC INDEPENDENCE
      87 | BOONDOCK BALLROOM
...
     954 | WAKE JAWS
     955 | WALLS ARTIST
(42 rows)

Here, we’ve specified an alias f for the film table, and an alias i for the inventory table. Note the the WHERE clause in subquery in by the EXISTS expression:

WHERE i.film_id = f.film_id

where compares film_id from film table and film_id from inventory table for equality.

Instead of using aliases, you can also refer to columns directly using the table name, as follows:

WHERE inventory.film_id = film.film_id

Conclusion

In PostgreSQL, table aliases can simplify the writing of SQL statements and improve the readability of SQL statements.