PostgreSQL Column Alias

Summary: In this tutorial, you will learn about PostgreSQL column aliases and how to use them to assign temporary names to columns in a query.

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;Code language: SQL (Structured Query Language) (sql)

In this syntax, the column_name is assigned an alias alias_name. The AS keyword is optional so you can omit it like this:

SELECT column_name alias_name
FROM table_name;Code language: SQL (Structured Query Language) (sql)

The following syntax illustrates how to set an alias for an expression in the SELECT clause:

SELECT expression AS alias_name
FROM table_name;Code language: SQL (Structured Query Language) (sql)

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 customer table:

SELECT 
   first_name, 
   last_name
FROM customer;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

This query assigned the surname as the alias of the last_name column:

Or you can make it shorter by removing the AS keyword as follows:

SELECT 
   first_name, 
   last_name surname
FROM customer;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 ?column? .

To fix this, you can assign the expression first_name || ' ' || last_name a column alias e.g., full_name:

SELECT
    first_name || ' ' || last_name AS full_name
FROM
    customer;Code language: SQL (Structured Query Language) (sql)

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"Code language: PHP (php)

For example:

SELECT
    first_name || ' ' || last_name "full name"
FROM
    customer;Code language: JavaScript (javascript)

Summary

• Assign a column or an expression a column alias using the syntax column_name AS alias_name or expression AS alias_name. The AS keyword is optional.
• Use double quotes (“) to surround column aliases that contain spaces.