Column Aliases

Introduction to PostgreSQL column aliases

A column alias lets you assign a temporary name to a column or expression in the select list of a SELECT statement. The alias exists only for the duration of that query — it is not stored in the database or the table definition.

The basic syntax is:

SELECT column_name AS alias_name
FROM table_name;

The AS keyword is optional, so the following is equivalent:

SELECT column_name alias_name
FROM table_name;

You can also alias an expression rather than a bare column:

SELECT expression AS alias_name
FROM table_name;

Column alias examples

These examples use the customer table from the standard DVD rental sample database.

Rename a column heading:

SELECT first_name, last_name AS surname
FROM customer;

Result: the output heading changes from last_name to surname, while the data stays the same.

Alias a string concatenation expression:

Without an alias, PostgreSQL labels a concatenated expression with the unhelpful heading ?column?:

SELECT first_name || ' ' || last_name
FROM customer;

Adding an alias gives the output column a meaningful name:

SELECT first_name || ' ' || last_name AS full_name
FROM customer;

Sample output:

full_name
------------------
Jared Ely
Mary Smith
Patricia Johnson
...

Aliases with spaces:

If your alias contains spaces, wrap it in double quotes:

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

Using column aliases in ORDER BY

PostgreSQL evaluates clauses in this order: FROM → WHERE → SELECT → ORDER BY. Because ORDER BY is evaluated after SELECT, you can reference a column alias in ORDER BY:

SELECT
  first_name,
  LENGTH(first_name) AS name_length
FROM customer
ORDER BY name_length DESC;

This sorts customers from longest to shortest first name.

When you cannot use column aliases

Because WHERE is evaluated before SELECT, column aliases are not available in a WHERE clause. The following query will fail:

-- This will produce an error
SELECT first_name || ' ' || last_name AS full_name
FROM customer
WHERE full_name LIKE 'Mary%';

Instead, repeat the expression directly in the WHERE clause:

SELECT first_name || ' ' || last_name AS full_name
FROM customer
WHERE first_name || ' ' || last_name LIKE 'Mary%';

Key points to remember

• Use AS alias_name to assign a readable heading to any column or expression.
• The AS keyword is optional but recommended for clarity.
• Wrap aliases that contain spaces or special characters in double quotes: "my alias".
• Column aliases can be used in ORDER BY but not in WHERE or HAVING when referencing an alias defined in the same SELECT.
• Aliases are case-insensitive unless wrapped in double quotes — AS FullName and AS fullname are treated the same.

Reference: PostgreSQL documentation — SELECT.