PostgreSQL Column Alias

This article describes aliasing a column in PostgreSQL to improve readability of the output.

PostgreSQL allows you to specify aliases for returned columns in the SELECT statement to improve readability.

PostgreSQL column alias syntax

To assign an alias to a specified column in a SELECT statement, use the following syntax:

SELECT expr AS alias_name
[FROM table_name];

Here:

  • The expr is an expression or column name.
  • The alias_name is an alias for the expr. The alias will be used in the result set returned by the SELECT statement.
  • If the column alias contains spaces, enclose it with ".
  • The AS is a keyword, it is optional. You can omit it.

You can alias multiple columns like this:

SELECT
   expr1 AS alias_name1,
   expr2 AS alias_name2,
   ...
[FROM table_name];

PostgreSQL column alias example

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

Simple example

To retrieve an actor’s first and last name from the actor table, use the following statement:

SELECT
   first_name,
   last_name
FROM actor
LIMIT 10;

 first_name |  last_name
------------+--------------
 PENELOPE   | GUINESS
 NICK       | WAHLBERG
 ED         | CHASE
 JENNIFER   | DAVIS
 JOHNNY     | LOLLOBRIGIDA
 BETTE      | NICHOLSON
 GRACE      | MOSTEL
 MATTHEW    | JOHANSSON
 JOE        | SWANK
 CHRISTIAN  | GABLE

To specify an alias surname for last_name, use the following statement:

SELECT
   first_name,
   last_name AS surname
FROM
   actor
LIMIT 10;

 first_name |   surname
------------+--------------
 PENELOPE   | GUINESS
 NICK       | WAHLBERG
 ED         | CHASE
 JENNIFER   | DAVIS
 JOHNNY     | LOLLOBRIGIDA
 BETTE      | NICHOLSON
 GRACE      | MOSTEL
 MATTHEW    | JOHANSSON
 JOE        | SWANK
 CHRISTIAN  | GABLE

Here, the name of the last_name column in the result set has been replaced with surname.

Assign an alias to an expression examples

To retrieve the full names of actors from the actor table, use the following statement:

SELECT
   first_name || ' ' || last_name
FROM
   actor
LIMIT 10;

      ?column?
---------------------
 PENELOPE GUINESS
 NICK WAHLBERG
 ED CHASE
 JENNIFER DAVIS
 JOHNNY LOLLOBRIGIDA
 BETTE NICHOLSON
 GRACE MOSTEL
 MATTHEW JOHANSSON
 JOE SWANK
 CHRISTIAN GABLE

Here, we used the ||operator to concatenate two strings. The expression first_name || ' ' || last_name concatenates first_name, spaces and last_name.

You can see that the column name of the expression is ?column? and it is meaningless. To make the output column name of the expression more readable, we need to specify a column alias for the expression, for example full_name:

To work around this, you can assign the alias full_name for first_name || ' ' || last_name as following:

SELECT
    first_name || ' ' || last_name full_name
FROM
    actor
LIMIT 10;

      full_name
---------------------
 PENELOPE GUINESS
 NICK WAHLBERG
 ED CHASE
 JENNIFER DAVIS
 JOHNNY LOLLOBRIGIDA
 BETTE NICHOLSON
 GRACE MOSTEL
 MATTHEW JOHANSSON
 JOE SWANK
 CHRISTIAN GABLE

Column alias with spaces

In the above example, if you want to use Full Name as a column alias, it contains spaces, use double quotes, ie: "Full Name".

SELECT
    first_name || ' ' || last_name "Full Name"
FROM
    actor
LIMIT 10;

     Full Name
---------------------
 PENELOPE GUINESS
 NICK WAHLBERG
 ED CHASE
 JENNIFER DAVIS
 JOHNNY LOLLOBRIGIDA
 BETTE NICHOLSON
 GRACE MOSTEL
 MATTHEW JOHANSSON
 JOE SWANK
 CHRISTIAN GABLE

Conclusion

  • Use the syntax expr AS alias_name or expression AS alias_name to assign a column alias to a column or expression.
  • The AS keyword is optional.
  • Use double quotes marks (") to enclose column aliases containing spaces.