Common
Table Expression(CTEs) is a handy feature in PostgreSQL, it is a temporary
result set that you can reference within another SELECT, INSERT, UPDATE, or
DELETE statement. It provides a convenient way to manage the completed queries.
CTEs are defined within the PostgreSQL statement using the WITH clause. It
resolves many difficulties we face in the PostgreSQL query statement such as
removes the complexity of sub-query, which executed multiple times, equally
helpful in place of temporary tables.
In
this article, we will learn the uses of Common Table Expression based on the
scenario. The CTEs start with WITH clause and followed by the expression name.
Further, we use the expression name in a select query to obtain the records of Common
Table Expression.
Syntax of WITH clause in PostgreSQL
WITH
CTE_Name [ ( column_name [,...n] ) ]
AS
(
CTE_query_definition )
|
For example, suppose we have the Employee table that contains employee details with managerid.
CREATE TABLE public.employee
(
id serial NOT NULL,
fname text COLLATE pg_catalog."default",
lname text COLLATE pg_catalog."default",
username text COLLATE pg_catalog."default",
password text COLLATE pg_catalog."default",
contact text COLLATE pg_catalog."default",
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL,
managerid integer,
CONSTRAINT employee_primarykey PRIMARY
KEY (id)
);
|
Inserts
some records into the employee table, run the following query.
INSERT INTO public.Employee(
FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
VALUES ('Name1','last1', 'name1@gmail.com', 'name@0123', '9088877787', '20000','2018/07/07',0);
INSERT INTO public.Employee(
FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
VALUES ('Name2','last2', 'name2@gmail.com', 'name@0123', '9288877787', '30000','2018/07/07',3);
INSERT INTO public.Employee(
FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
VALUES ('Name3','last3', 'name3@gmail.com', 'name@0123', '9388877787', '40000','2017/09/08',1);
INSERT INTO public.Employee(
FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
VALUES ('Name4','last4', 'name4@gmail.com', 'name@0123', '9344877787', '70000','2018/06/09',2);
|
Common Table Expression without recursive,
Suppose I want to get all those records that have Salary greater than 20000.
Simple CTE Example:
WITH CTE(EmpID, FirstName, LastName, MgrID, Salary,YearOfJoining)
AS
(
SELECT Id,FName,LName,ManagerID,Salary,date_part('year', doj)
FROM
public.Employee
WHERE Salary>20000
)
SELECT EmpID, FirstName, LastName, MgrID, Salary,YearOfJoining FROM CTE;
|
Result
Recursive CTE Example
From
the Employee table, I want to get the records of the employee and manager, also
want to show the level of the hierarchy.
WITH RECURSIVE CTE(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT Id,FName,LName,ManagerID,1 FROM public.Employee WHERE ManagerID=0
UNION ALL
SELECT e.Id, e.FName,e.LName, e.ManagerID, r.EmpLevel + 1
FROM public.Employee e
INNER JOIN CTE r
ON e.ManagerID = r.EmpID
)
SELECT EmpID, FirstName, LastName, MgrID, EmpLevel FROM CTE;
|
Result
Another example of recursive Common Table Expression
WITH RECURSIVE cte (i)
AS
(
SELECT 10
UNION ALL
SELECT i+10 FROM cte WHERE i+10<= 100
)
SELECT i FROM cte;
|
Result:
INSERT records using WITH clause
We
can modify the records using CTE Statements (Data-Modifying Statements in
WITH), PostgreSQL WITH clause allows us to perform several different operations
in the same query.
Suppose
you want to delete some records from the Employee table and want to log the
deleted data in Employee_Log table then use the following query.
WITH deleted_rows
AS
(
DELETE FROM employee
WHERE
username = 'name4@gmail.com'
RETURNING
*
)
INSERT INTO employee_log
SELECT * FROM deleted_rows;
|
Update records using with clause
Suppose
you want to increase the salary of 10% of your employee, for example, see the
following query.
WITH upd_emp
AS
(
UPDATE
employee SET Salary = Salary*1.1
RETURNING
*
)
SELECT * FROM upd_emp;
|
