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 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.
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: This is defined as a clause that was used to execute the Subquery and large Subquery in PostgreSQL.
- Name of CTE: This is defined name of the common table expression which was we have used with clause.
- AS: This is defined as we have used alias name of a common table expression in with clause.
- List of the column: It is defined as a column list that we have used in with clause.
- Select query statement: We have used a select query statement with common table expression and with 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 clause in PostgreSQL. We can use multiple table to retrieve data from multiple tables by using with a clause in PostgreSQL.
- Where condition: It is defined as the condition by using the same we have retrieved data from the table. Where condition is useful when we have to retrieve specified data from the table.
- Order by: It is defined as order by clause is used with where clause to retrieve data with ascending or descending order.
- Definition of common table expression: This is defined as a definition query of CTE which was we have used in with clause.
How WITH Clause works in PostgreSQL?
We can specify the column list by using the common table expression in PostgreSQL. We can also join the table by using a clause in PostgreSQL. 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 below example shows that with clause statement require select privileges on table or superuser privileges to execute with 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:
Explanation: In the above first example we have used user as db_test, this user doesn’t have privileges of select on stud1 and stud2 table or superuser so, it will issue 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 stud1 and stud2 table. Postgres users have superuser privileges in PostgreSQL.
Examples to Implement PostgreSQL WITH Clause
Below is the 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:
- Below is the table and data description of the stud2 table.
Code:
\d+ stud2;
select * from stud2;
Output:
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:
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:
3. With clause in join operations
Below example shows that with clause in join operations are as follows. We have used stud1 and stud2 table 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:
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:
Recommended Articles
This is a guide to PostgreSQL WITH Clause. Here we discuss an introduction to PostgreSQL WITH Clause, how does it work with query examples for better understanding. You can also go through our other related articles to learn more -.
