Contact
+421 911 065 745
Common table expression (CTE) is among the extended elements of every database system, including postgres. It represents a temporary SQL query that can be used multiple times within a longer and complex selection, the sql is more readable and the script is faster and more optimized. In postgres, you can also delete and update based on the CTE result. The CTE structure looks like this:
WITH RECURSIVE cte_name (column1, column2, ...)
AS(
-- anchor member
SELECT select_list FROM table1 WHERE condition
UNION [ALL]
-- recursive term
SELECT select_list FROM cte_name WHERE recursive_condition
)
SELECT * FROM cte_name;
It follows from the above that each CTE must start with the keyword WITH followed by the name of the CTE (if we want a repeating CTE, we must put the word "RECURSIVE"), followed by AS and select is used in the parenthesis, then the parenthesis is closed if we want to add more CTE, a comma and the name of the CTE are added after the closed parenthesis, and again the script in parentheses:
WITH RECURSIVE cte_name (column1, column2, ...)
AS(
-- anchor member
SELECT select_list FROM table1 WHERE condition
),
cte_name2 (column1, column2, ...)
AS(
-- anchor member
SELECT select_list FROM table1 WHERE condition
)
SELECT * FROM cte_name
union all
SELECT * FROM cte_name2
;
If we want to record the number of deleted lines or changed e.g. to some logging table called log_table, we can use CTE:
--delete
WITH MOVED_ROWS AS
(DELETE
FROM TABLE_NAME RETURNING *)
INSERT INTO LOG_TABLE
SELECT MR.*,
CURRENT_TIMESTAMP
FROM MOVED_ROWS MR;
- update
WITH CTE AS
(UPDATE TABLE_NAME
SET COLUMN_NAME_NUMERIC = 100
RETURNING *)
INSERT INTO LOG_TABLE
SELECT *
FROM CTE ;
Another possibility is the use of CTE in Recursive query, when the repeating script in CTE can go through several levels and thus record all the records in the resulting query. find out who is whose manager and what the sequence is.
create table employees
(
employee_id numeric(6) not null
constraint emp_emp_id_pk
primary key,
first_name varchar(20),
last_name varchar(25) not null,
email varchar(25) not null,
phone_numeric varchar(20),
hire_date date not null,
job_id varchar(10) not null,
salaries numeric(8, 2),
commission_pct numeric(2, 2),
manager_id numeric(6)
constraint emp_manager_fk
reference employees,
department_id numeric(4)
);
insert into public.employees (employee_id, first_name, last_name, email, phone_numeric, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'Steven', 'King', 'SKING', '515.123.4567', '2003-06-17', 'AD_PRES', 24000.00, null, null, 90),
(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '2005-09-21', 'AD_VP', 17000.00, null, 100, 90),
(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '2001-01-13', 'AD_VP', 15000.00, null, 100, 90),
(114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '2002-12-07', 'PU_MAN', 11000.00, null, 100, 30),
(120, Matthew, Weiss, MWEISS, 650.123.1234, 2004-07-18, ST_MAN, 8000.00, null, 100, 50),
(121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '2005-04-10', 'ST_MAN', 8200.00, null, 100, 50),
(122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '2003-05-01', 'ST_MAN', 7900.00, null, 100, 50),
(123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '2005-10-10', 'ST_MAN', 6500.00, null, 100, 50),
(124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '2007-11-16', 'ST_MAN', 5800.00, null, 100, 50),
(145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', '2004-10-01', 'SA_MAN', 14000.00, 0.40, 100, 80),
(146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', '2005-01-05', 'SA_MAN', 13500.00, 0.30, 100, 80),
(147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', '2005-03-10', 'SA_MAN', 12000.00, 0.30, 100, 8 0),
(148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', '2007-10-15', 'SA_MAN', 11000.00, 0.30, 100, 80 ),
(149;
(201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '2004-02-17', 'MK_MAN', 13000.00, null, 100, 20);
WITH RECURSIVE EMPLOYEE_REC AS
(SELECT first_name,last_name,employee_id,'manager' as to
FROM employees with where employee_id=100
UNION SELECT e.first_name,e.last_name,e.manager_id,'subordinates' a.s. no
FROM employees e
INNER JOIN EMPLOYEE_REC S ON S.employee_id = E.manager_id)
SELECT *
FROM EMPLOYEES_REC;
Using examples, we showed the use of CTE in the database tool Postgres. The basic idea is not to repeat the query unnecessarily multiple times in the script and thus reduce I/O operations and to have a temporary script to which the following scripts follow. At the same time, it is a solution that does not allocate any memory compared to temp tables and has more possibilities of use. It is possible to create a logging table or make a recursive query, which is an advantage of CTE in postgres compared to other database tools (Oracle, MS SERVER) which perform the given operations through complex functions and procedures. I recommend using CTE every day for everyone and thus write more optimized scripts.
+421 911 065 745