What is CTE in PostgreSQL?

CTE in PostgreSQL stands for common table expression. It is a way of temporarily holding the results of a PostgreSQL query. At times, we write extremely complex queries which are very difficult to interpret. In such cases, the usage of CTE makes our queries look simpler and more readable. With the help of this article, we intend to teach you the usage of CTE in PostgreSQL in Windows 10.

Example: Using CTE in PostgreSQL:

We will be using CTE in PostgreSQL in Windows 10 in the following example:

Step # 1: PostgreSQL Tables Creation:

First of all, we will create two PostgreSQL tables to use CTE later to extract the desired results from these tables. In this illustration, we wish to work with a relation between doctors and patients. Therefore, we will create a table named “doctor” and the other one named “patient”.

For creating the “doctor” table, we will run the following PostgreSQL query:

# CREATE TABLE doctor(Doc_ID SERIAL PRIMARY KEY, Doc_Name VARCHAR (255) NOT NULL);

This query will create the “doctor” table with two attributes, i.e. Doc_ID and Doc_Name. You can also see the whole table creation process from the image shown below:

Now, for creating the “patient” table, we will run the following PostgreSQL query:

# CREATE TABLE patient(Pat_ID SERIAL PRIMARY KEY, Pat_Name VARCHAR (255) NOT NULL, Pat_Temp INT NOT NULL, Doc_ID INT NOT NULL);

This query will create the “patient” table with four attributes, i.e. Pat_ID, Pat_Name, Pat_Temperature (this represents the body temperature of the patient), and Doc_ID (this is the same Doc_ID that we have declared in the “doctor” table. Here, it is being used as a foreign key to specify which doctors treated each patient). You can also see the whole table creation process from the image shown below:

Step # 2: Records Insertion in PostgreSQL Tables:

After creating these tables, we have to insert a sufficient amount of records into them to use these records for demonstrating the usage of CTE in PostgreSQL later on. For inserting records into the “doctor” table, we will run the following PostgreSQL query:

# INSERT INTO doctor VALUES(1, ‘Sarah’), (2, ‘Affan’), (3, ‘Irtiza’), (4, ‘Hina’), (5, ‘Naila’);

This query will simply insert the records of five different doctors into the “doctor” table as shown in the image below:

Now, for inserting records into the “patient” table, we will run the following PostgreSQL query:

# INSERT INTO patient VALUES(1, ‘Saba’, 99, 1), (2, ‘Sidra’, 100, 1), (3, ‘Hamza’, 100, 2), (4, ‘Aslam’, 98, 2), (5, ‘Fizza’, 101, 3), (6, ‘Iqra’, 102, 3), (7, ‘Sadia’, 100, 4), (8, ‘Sobia’, 99, 4), (9, ‘Salman’, 100, 5), (10, ‘Jawad’, 103, 5);

This query will insert the records of 10 different patients into the “patient” table as shown in the image below:

Note: You might be wondering why we kept the number of records of the “patient” table more than the “doctor” one. Well, a single doctor can attend to multiple patients at a time. However, this is just for demonstration. You can keep the number of records of these two tables equal if you wish so.

Step # 3: View the Newly Inserted Records in PostgreSQL Tables:

Before proceeding further, we will quickly view the records inserted into our two PostgreSQL tables. For the “doctor” table, we will run the following PostgreSQL query:

# SELECT * FROM doctor;

You can see all the records of the “doctor” table from the image shown below:

Now, for the “patient” table, we will run the following PostgreSQL query:

# SELECT * FROM patient;

You can see all the records of the “patient” table from the image shown below:

Step # 4: Use CTE to Display all the Records of a PostgreSQL Table:

This step will demonstrate relatively simple usage of CTE in PostgreSQL. We want to store all the records of one of our tables into a common table expression and then simply display it on the console. The query that we are going to execute for this purpose is cited below:

# WITH CTE_Patient AS (SELECT Pat_ID, Pat_Name, Pat_Temp, Doc_ID FROM patient) SELECT * FROM CTE_Patient;

Now, we will explain to you this whole query while discussing all of its components. The common table expression’s name is always preceded by the “WITH” keyword and proceeded by the “AS” keyword. It means that the name for our CTE in this particular case is “CTE_Patient”. After the “AS” keyword, we specify the whole query whose results we wish to be stored in our common table expression. In this example, we simply want to pick all the records comprising all the attributes of the “patient” table and then store them in our CTE. After that, we used the “SELECT” statement to display the contents of this CTE on our console. This query will take all the ten records from our “patient” table, store them temporarily in CTE_Patient, and then display the contents of CTE_Patient on the console as shown in the image below:

Step # 5: Use CTE with the “WHERE” Clause in PostgreSQL:

Now, we will move to a relatively complex usage of CTE in PostgreSQL, i.e. we will use CTE with the “WHERE” clause in PostgreSQL. In this modified example, we aim to check the temperature of all the patients and then display the names and IDs of only those patients who are having a fever. The query that will serve this purpose is as follows:

# WITH CTE_Patient AS (SELECT Pat_ID, Pat_Name, (CASE WHEN Pat_Temp <= 100 THEN ‘NORMAL’ WHEN Pat_Temp > 100 THEN ‘FEVER’ END) Temperature FROM patient) SELECT Pat_ID, Pat_Name, Temperature FROM CTE_Patient WHERE Temperature = ‘FEVER’ ORDER BY Pat_Name;

In this query, we have used the “CASE” statement on the Temperature variable. The main condition for this statement is that if the patient’s temperature is less than or equal to 100, it will be considered normal, whereas if it is more than 100, then the patient will have a fever. After that, we simply used the “SELECT” statement to display the Pat_ID, Pat_Name, and Temperature of all those patients from our common table expression who have a fever. Additionally, we have also ordered our results alphabetically according to the patient’s name, as shown in the image below:

In the same manner, if you wish to display the names and IDs of all those patients on the console whose body temperature is normal, then you need to modify the above-mentioned query slightly as follows:

All the patients from our “patient” table whose body temperature is normal are shown in the image below:

Conclusion:

This guide talked about the usage of CTE in PostgreSQL in Windows 10. To elaborate on this usage, we first created a simple example and then introduced some complexity in it so that the readers can better understand how CTE works with PostgreSQL tables. Once you thoroughly go through this comprehensive example, you will be able to learn the basic syntax of CTEs in PostgreSQL along with some other technical details, and afterwards, you will be able to use the CTEs effectively to make your queries look simpler and readable.