Introduction to the PostgreSQL column aliases

A column alias allows you to assign a column or an expression in the select list of aSELECT 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_nameFROM table_name;

In this syntax, thecolumn_name is assigned an aliasalias_name. TheAS keyword is optional so you can omit it like this:

SELECT column_name alias_nameFROM table_name;

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

SELECT expressionAS alias_nameFROM 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 thecustomer table from thesample 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 thecustomer table:

SELECT   first_name,   last_nameFROM customer;

If you want to rename thelast_name heading, you can assign it a new name using a column alias like this:

SELECT   first_name,   last_nameAS surnameFROM customer;

This query assigned thesurname as the alias of thelast_name column:

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

SELECT   first_name,   last_name surnameFROM 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_nameFROM   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?column? .

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

SELECT    first_name|| ' ' || last_nameAS full_nameFROM    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"

For example:

SELECT    first_name || ' ' || last_name "full name"FROM    customer;

Summary

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

Last updated onJanuary 16, 2024

On this page

• Introduction to the PostgreSQL column aliases
• Summary