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 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]
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
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
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
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')
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.