August 3, 2023
Summary: in this tutorial, you will learn about PostgreSQL column aliases and how to use column aliases to assign temporary names to columns in queries.
Introduction to the PostgreSQL column aliases
A column alias allows you to assign a column or an expression in the select list of a
SELECT statement a temporary name. The column alias exists temporarily during the execution of the query.
The following illustrates the syntax of using a column alias:
SELECT column_name AS alias_name FROM table_name;
In this syntax, the
column_name is assigned an alias
AS keyword is optional so you can omit it like this:
SELECT column_name alias_name FROM table_name;
The following syntax illustrates how to set an alias for an expression in the
SELECT expression AS alias_name FROM table_name;
The main purpose of column aliases is to make the headings of the output of a query more meaningful.
PostgreSQL column alias examples
We’ll use the
customer table from the sample database to show you how to work with column aliases.
1) Assigning a column alias to a column example
The following query returns the first names and last names of all customers from the
SELECT first_name, last_name FROM customer;
If you want to rename the
last_name heading, you can assign it a new name using a column alias like this:
SELECT first_name, last_name AS surname FROM customer;
This query assigned the
surname as the alias of the
Or you can make it shorter by removing the
AS keyword as follows:
SELECT first_name, last_name surname FROM customer;
2) Assigning a column alias to an expression example
The following query returns the full names of all customers. It constructs the full name by concatenating the first name, space, and the last name:
SELECT first_name || ' ' || last_name FROM customer;
Note that in PostgreSQL, you use the
|| as the concatenating operator that concatenates one or more strings into a single string.
As you can see clearly from the output, the heading of the column is not meaningful
To fix this, you can assign the expression
first_name || ' ' || last_name a column alias e.g.,
SELECT first_name || ' ' || last_name AS full_name FROM customer;
3) Column aliases that contain spaces
If a column alias contains one or more spaces, you need to surround it with double quotes like this:
column_name AS "column alias"
SELECT first_name || ' ' || last_name "full name" FROM customer;
- Assign a column or an expression a column alias using the syntax
column_name AS alias_nameor
expression AS alias_name.
ASkeyword is optional.
- Use double quotes (“) to surround a column alias that contains spaces.