Creating Postgres Query using Table and Column Aliases

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.