Working with ALIAS in Postgres

Intro

Aliases help you improve the readability of your queries. Sometimes, the column names are technically and you wish to change the names to be more human readable. Also, when joining table, you often want to rename columns to make the query easier to read. In this article, we will learn how to use Column and Table Aliases in PosgreSQL.

The Syntax

The basic syntax of using a Column Alias is as follows:

select column AS [alias]

Here alias is the new name of the column.

In a similar way, we can rename tables

select column from table AS [table_alias]

We can also omit the AS keyword and have the following:

select column [column_alias] 
	from table [table_alias]

Getting Setup

For our setup, we will use docker compose to create a Postgres database and to connect phpmyadmin. Start by copying the following into a docker compose file called docker-compose.yml

version: '3'
 
services:
  db:
    image: 'postgres:latest'
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: default_database
    volumes:
      - psqldata:/var/lib/postgresql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  psqldata:

We can run this file, we can use docker-compose up. One this is done, open up phpmyadmin by going to http://localhost:8081.

You can then login by leaving the host empty and using the following credentials.

POSTGRES_USER: username
POSTGRES_PASSWORD: password

Creating a DB

In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.

We will be using the sample db provided here: https://dev.Postgres.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.

Next, let's create an actor table.

CREATE TABLE actor (
  actor_id smallint,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (actor_id)
) ;

And finally, let's enter a few rows.

INSERT INTO actor VALUES 
(1,'PENELOPE','GUINESS','2006-02-15 04:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 04:34:33'),
(3,'ED','CHASE','2006-02-15 04:34:33'),
(4,'JENNIFER','DAVIS','2006-02-15 04:34:33'),
(5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'),
(6,'BETTE','NICHOLSON','2006-02-15 04:34:33'),
(7,'GRACE','MOSTEL','2006-02-15 04:34:33'),
(8,'MATTHEW','JOHANSSON','2006-02-15 04:34:33')

Alias Example

A common use case for using alias is to convert the case of the column names. Let’s see an example of renaming the first and last name columns.

SELECT 
    first_name AS FirstName,
    last_name AS LastName
FROM
    actor;

Sometimes, we want to create a new column. For example, we can use the CONCAT_WS function to create a new full name column. Here, we can rename the column to make the result more readable.

SELECT 
    CONCAT_WS(', ', last_name, first_name) AS `Full Name`
FROM
    actor;

Not here the use of back ticks. This is because we wanted to add a Space in the middle of Full and Name.