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 WITH Clause
 

PostgreSQL WITH Clause

Priya Pedamkar
Article byPriya Pedamkar

Updated May 16, 2023

PostgreSQL WITH Clause

 

 

Introduction to PostgreSQL WITH Clause

PostgreSQL with clause is used to write the auxiliary statement to use large query, with clause is helpful when we have executing complicated large queries into the simple forms. While using with clause, the output of this clause is easily readable, with clause is also called as common table expressions or CTE, it is also define as a temporary table which only exist only that specific query. We can execute multiple Subquery by using multiple queries in PostgreSQL, it is helpful by using multiple temporary tables.

Watch our Demo Courses and Videos

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

Syntax and Parameters

Below is the syntax of with clause in PostgreSQL.

  • With name_of_CTE (list_of_column) AS (Select query statement)
Select column_list
From name_of_table
Where condition (select column_list from name_of_table)
Order by name_of_column;
  • With name_of_CTE (list_of_column) AS (
Definition of CTE query)
Statement;

Below is the parameter description syntax of with clause in PostgreSQL.

  • With clause: In PostgreSQL, we define this clause as one that executes the Subquery and large Subquery.
  • Name of CTE: This is the defined name of the common table expression we used with clause.
  • AS: We define this as the usage of an alias name for a common table expression in the with clause.
  • List of the column: It is defined as a column list that we have used in with clause.
  • Select query statement: We used a select query statement with a common table expression and the clause in PostgreSQL. We are using multiple select statements in with clause.
  • Name of a table: It is defined as the name of the table from which we have retrieving data by using with a clause in PostgreSQL. We can use multiple tables to retrieve data from multiple tables using a clause in PostgreSQL.
  • Where condition: It is defined as the condition by using the same we have retrieved data from the table. Where the condition is useful when we have to retrieve specified data from the table.
  • Order by: When used with the where clause, the order by clause defines the retrieval of data with ascending or descending order.
  • Definition of common table expression: This is defined as a definition query of CTE, which we used in with clause.

How WITH Clause works in PostgreSQL?

We can specify the column list by using the common table expression in PostgreSQL. In PostgreSQL, we may also join the table by utilising a clause. We have also use the recursive with a clause in the query. To execute the with clause statement, we need to have select privileges on a table, or we need to have superuser privileges to execute the with clause statement.

The example below shows that a clause statement requires select privileges on table or superuser privileges to execute with a clause statement.

Code:

psql -U db_test -d testing
WITH cte_stud1 AS (SELECT id, name, (CASE WHEN id > 5 THEN 'id1' WHEN id > 7 THEN 'id2' ELSE 'id3' END) id FROM stud1)SELECT id, name, student_id FROM stud2 WHERE name = 'PQR' ORDER BY id;
psql -U postgres -d testing
WITH cte_stud1 AS (SELECT id, name, (CASE WHEN id > 5 THEN 'id1' WHEN id > 7 THEN 'id2' ELSE 'id3' END) id FROM stud1)SELECT id, name, student_id FROM stud2 WHERE name = 'PQR' ORDER BY id;

Output:

Output 1

Explanation: In the above first example, we have used the user as db_test, this user doesn’t have privileges of select on stud1 and stud2 table or superuser, so it will issue an error while executing the with clause statement. In the second example, we have selected the table rows using the username as Postgres, after using this user, we have selected the column from the stud1 and stud2 table. Postgres users have superuser privileges in PostgreSQL.

Examples to Implement PostgreSQL WITH Clause

Below is an example of with clause in PostgreSQL.

We are using stud1 and stud2 table to describe an example of with clause in PostgreSQL are as follows.

  • Below is the table and data description of the stud1 table.

Code:

\d+ stud1;
select * from stud1;

Output:

PostgreSQL WITH Clause - 2

  • Below is the table and data description of the stud2 table.

Code:

\d+ stud2;
select * from stud2;

Output:

PostgreSQL WITH Clause - 3

1. Simple with clause.

Below example shows that with clause are as follows. We have use CTE name as CTE_test.

Code:

With CTE_test AS (Select ID, NAME, stud_id, ADDRESS FROM stud1) Select * From CTE_test;

Output:

Simple WITH Clause

2. Recursive With clause

Below example shows that recursive with clause are as follows. We have use CTE name as test(id).

Code:

WITH RECURSIVE test(id) AS (VALUES (1) UNION SELECT id FROM stud2) SELECT sum(id) FROM test;

Output:

Recursive

3. With the clause in join operations

Below example shows that with a clause in join, operations are as follows. We have used stud1 and stud2 tables to join operation using with clause.

Code:

WITH cte_stud1 AS (SELECT id, name FROM   stud1 GROUP  BY id) SELECT s.id, s.name FROM stud2 s INNER JOIN cte_stud1 USING (id);

Output:

join operations

4. With clause using condition

Below example shows with clause using condition.

Code:

WITH cte_stud AS (SELECT id, name, (CASE WHEN id > 5 THEN 'id1' ELSE 'id2' END) id FROM stud1) SELECT id, name FROM stud2;

Output:

With clause using condition

Recommended Articles

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

  1. PostgreSQL replace
  2. HAVING PostgreSQL
  3. PostgreSQL ORDER BY
  4. Indexes in PostgreSQL

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