Creating Postgres Query using Table and Column Aliases
Introduction
In this article we will go over a demo example of creating a PostgreSQL query using table and column aliases. First we’ll quickly go over some prerequisites but then we’ll dive straight into what aliases are and how to use them.
Prerequisite
Ensure to install and configure the following for your OS:
Basic PostgreSQL is required to be able to follow along with the tutorial.
What is Postgres Alias
An alias in Postgres is a temporary name assigned to a table or view. Aliases provides a more readable results when use format a returned result set.
Postgres Aliases have two (2) level of usage and that is the column level and table level.
Column Alias
In the following examples the column alias is being used. See the below syntax for column alias:
1 2 | SELECT <column_name> AS alias FROM TABLE; |
In the above syntax we assigned an alias against a column or a table field followed by the AS clause followed by the actual alias name that we want to use.
Table Alias
In this section, we now discuss the other level of Postgres alias and that is the table alias. Below is the basic syntax for providing an alias name to a table.
1 2 3 4 | SELECT list_fields FROM <table_name> AS alias_name; |
The above syntax is almost the same wit the previous section, we again use AS clause followed by the desired alias for the table.
Creating Sample Dataset
Now that we have an overview understanding of what is a Postgres alias, then we will create our sample dataset that we can use for demo purposes.
First, log in to the Postgres shell.
- We create a database using the below command.
1 | create database hospitaldb |
- Create tables for the database with the following structure
1 2 3 4 5 6 7 8 | CREATE TABLE IF NOT EXISTS patient ( id SERIAL PRIMARY KEY, patient_id CHARACTER VARYING(100), patient_name CHARACTER VARYING(100), patient_age INTEGER, patient_gender CHARACTER VARYING(100), patient_case CHARACTER VARYING(100) ); |
Then we insert our sample database.
1 2 3 4 5 6 7 | INSERT INTO patient (id,patient_id, patient_name, patient_age, patient_gender,patient_case) VALUES (1,'P2134','George Gon',45, 'male', 'asthma'), (2,'P2313','Robert McWallace',35, 'male', 'Chronic Migraine'), (3,'P2351','Rosalie Chavez',24, 'female', 'Mumps'), (4,'P2234','Dennise Chaplain',32, 'male', 'Acid reflux'), (5,'P2933','Ray Roberts',21, 'male', 'Soar throat'); |
Our table patient should look something like this.
1 2 3 4 5 6 7 8 | id | patient_id | patient_name | patient_age | patient_gender | patient_case ----+------------+------------------+-------------+----------------+------------------ 1 | P2134 | George Gon | 45 | male | asthma 2 | P2313 | Robert McWallace | 35 | male | Chronic Migraine 3 | P2351 | Rosalie Chavez | 24 | female | Mumps 4 | P2234 | Dennise Chaplain | 32 | male | Acid reflux 5 | P2933 | Ray Roberts | 21 | male | Soar throat (5 rows) |
Postgres Query using column alias example
In this section, we will be showing you how to use aliases against a table column.
We use the following syntax.
1 2 | SELECT patient_id, patient_name AS patient_listing FROM patient; |
The above syntax will assign an alias called ‘patient_listing’ to the selected column which is the ‘patient_name’.
Here is the output:
1 2 3 4 5 6 7 8 | patient_id | patient_listing ------------+------------------ P2134 | George Gon P2313 | Robert McWallace P2351 | Rosalie Chavez P2234 | Dennise Chaplain P2933 | Ray Roberts (5 rows) |
Let’s take a look at another example of a column alias:
1 2 | SELECT ptnt.patient_name AS "pName", ptnt.patient_age AS "pGender" FROM patient ptnt |
Gives the following output:
1 2 3 4 5 6 7 8 | pName | pGender ------------------+--------- George Gon | 45 Robert McWallace | 35 Rosalie Chavez | 24 Dennise Chaplain | 32 Ray Roberts | 21 (5 rows) |
Table Alias Example
Now that we are familiar on how to create query using column alias, we will now show some demonstration on table level aliases.
1 2 | SELECT patient_name , patient_id FROM patient ptnt; |
The above query gets two column from the table and we assign the ptnt as the name of the table.
This is the output:
1 2 3 4 5 6 7 8 | patient_name | patient_id ------------------+------------ George Gon | P2134 Robert McWallace | P2313 Rosalie Chavez | P2351 Dennise Chaplain | P2234 Ray Roberts | P2933 (5 rows) |
Let’s take a look at another example of a table alias:
1 2 | SELECT Patient_Listing FROM patient Patient_Listing; |
This is the output:
1 2 3 4 5 6 7 8 | patient_listing --------------------------------------------------------- (1,P2134,"George Gon",45,male,asthma) (2,P2313,"Robert McWallace",35,male,"Chronic Migraine") (3,P2351,"Rosalie Chavez",24,female,Mumps) (4,P2234,"Dennise Chaplain",32,male,"Acid reflux") (5,P2933,"Ray Roberts",21,male,"Soar throat") (5 rows) |
Conclusion
We hope you’ve found this tutorial on creating Postgres query using table and column aliases helpful. Remember this is just giving another name to the column or table, hence why they are called aliases. Thank you for joining us for this PostgreSQL tutorial.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started