EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL CTE
 

PostgreSQL CTE

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 12, 2023

PostgreSQL CTE

 

 

Definition of PostgreSQL CTE

PostgreSQL CTE is an abbreviation for common table expressions used to simplify complex queries. The common table expression result is temporary, which we can include in the SELECT, INSERT, UPDATE, and DELETE, etc. SQL statements. The Common Table Expressions result is temporary, which means that the common table expression exists while executing the SQL statement only. We can use the common table expressions to increase the readability of the complex JOINs and SQL statements; we can also organize the complex JOINs and SQL statements in a human-readable and organized manner.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Consider the following syntax of the PostgreSQL CTE:

WITH CTE_name (column_name_list) AS (
CTE_SQL_statement_definition
)
statement;

Explanation:

  •  CTE_name: We define the name to be given to the common table expression. The names of the columns follow the common table expression. The column name list is optional.
  • CTE_SQL_statement_definition: We define the SQL statement, which returns the result set inside the body of the WITH clause. The CTE_SQL_statement_definition becomes the names of the column list of the common table expression if we have not defined the column names after the names of the common table expression.
  • statement: This can be used as a view or table in the statement, which can be a SELECT, INSERT, UPDATE, or deletes SQL statements.

How CTE Works in PostgreSQL?

  • To define a WITH clause, follow the syntax section by specifying the name of the common table expression and then list the column names.
  • The WITH clause contains a body; if we have not defined the column name, it takes all columns from the SQL statement defined in the body of the WITH clause.
  • The Common Table Expressions result is temporary, which means that the common table expression exists while executing the SQL statement only.

Examples:

We will create two tables of the name ‘student’ and ‘teacher’ by using the PostgreSQL CREATE TABLE statement as follows to understand the examples:

create table student
(
stud_id serial PRIMARY KEY,
stud_fname VARCHAR(80) NOT NULL,
stud_lname VARCHAR(80) NOT NULL,
stud_marks INT NOT NULL,
teach_id INT NOT NULL
);
create table teacher
(
teach_id serial PRIMARY KEY,
teach_fname VARCHAR(80) NOT NULL,
teach_lname VARCHAR(80) NOT NULL
);

We will insert some data into the ‘teacher’ table by using the PostgreSQL INSERT INTO statement as follows:

INSERT INTO teacher(teach_fname, teach_lname)
VALUES
('William','Joe'),
('Oliver','John'),
('Jack','Richard'),
('Harry','Joseph'),
('George','Thomas'),
('Brown','Charles');

 Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.

select * from teacher;

PostgreSQL CTE 1

Now, we will insert some data into the student’ table by using the PostgreSQL INSERT INTO statement as follows:

INSERT INTO student(stud_fname, stud_lname, stud_marks, teach_id)
VALUES
('Smith','Johnson',67,1),
('Williams','Jones',42,1),
('Harper','James',54,2),
('Jack','Liam',58,2),
('Harry','Mason',62,3),
('Jacob','Oscar',69,3),
('Michael','Charlie',75,4),
('William','Joe',44,4),
('Oliver','John',53,5);

Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.

select * from student;

PostgreSQL CTE 2

1. Consider the following basic example, which will be used to retrieve all the records from the student table by using common table expression:

With CTE_student AS
(
select
stud_id,
stud_fname,
stud_lname,
stud_marks,
teach_id
FROM
student
)
select * from CTE_student;

Illustrate the result of the above SQL statement by using the following snapshot.

PostgreSQL CTE 3

2. Consider another example; here, we will first define a cte_student as a common table expression by using the WITH clause as defined below:

WITH cte_student AS (
SELECT
stud_id,
stud_fname,
stud_lname,
(CASE
WHEN stud_marks <= 40
THEN 'Fail'
WHEN stud_marks > 40 AND stud_marks <= 55
THEN 'Third'
WHEN stud_marks > 55 AND stud_marks <= 60
THEN 'Second'
WHEN stud_marks > 60 AND stud_marks <= 65
THEN 'First'
WHEN stud_marks  > 65
THEN 'Distinction'
END) Grade
FROM
student
)
SELECT
stud_id,
stud_fname,
stud_lname,
Grade
FROM
cte_student
WHERE
Grade = 'Distinction'
ORDER BY
stud_fname;

Illustrate the result of the above SQL statement by using the following snapshot.

Output 4

3. Consider one more example; we can use the JOIN with the column table expression as follows:

WITH cte_student AS (
SELECT
stud_id,
stud_fname,
stud_lname,
teach_id,
(CASE
WHEN stud_marks <= 40
THEN 'Fail'
WHEN stud_marks > 40 AND stud_marks <= 55
THEN 'Third'
WHEN stud_marks > 55 AND stud_marks <= 60
THEN 'Second'
WHEN stud_marks > 60 AND stud_marks <= 65
THEN 'First'
WHEN stud_marks  > 65<
THEN 'Distinction'
END) Grade
FROM
student
)
SELECT t.teach_id,
teach_fname,
teach_lname,
Grade
FROM teacher t
INNER JOIN cte_student USING (teach_id);

Illustrate the result of the above SQL statement by using the following snapshot.

Output 5

Conclusion

From the above article, we hope you understand how to use the PostgreSQL common table expression and how the PostgreSQL common table expression works. Also, we have added several examples of PostgreSQL common table expression to understand them in detail.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL CTE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL IF Statement
  2. PostgreSQL Temporary Table
  3. For Loop PostgreSQL
  4. PostgreSQL List Tables

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW