Postgres With
In this tutorial, we will explore how to create the Common Table Expressions in PostgreSQL using the PostgreSQL WITH keyword.
PostgreSQL WITH Clause
The following defines the syntax of a PostgreSQL CTE using the WITH keyword:
CTE_query_definition
)
statement;
We start by specifying the name of the CTE, followed by an optional column list.
The next step is the CTE body. In this section, we define the query that returns a result. If we do not specify the column list after the CTE name, the select list of the query_definition becomes the column list of the CTE.
Finally, we use the CTE like a table or a view using any CRUD command.
PostgreSQL CTE Example
Consider the following table and sample data that we use to demonstrate the use of CTEs in PostgreSQL:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
salary NUMERIC(10, 2)
);
-- Insert sample data
INSERT INTO employees (name, department, salary)
VALUES ('J Doe', 'Finance', 5000.00),
('Jane Smith', 'Finance', 5500.00),
('Mark Johnson', 'Sales', 4500.00),
('Emily Brown', 'Sales', 4800.00),
('Michael Davis', 'IT', 6000.00),
('Sarah Wilson', 'IT', 6500.00);
Once the table is set up, we can learn how to use CTEs in PostgreSQL.
Example 1: Simple CTE
The following example demonstrates how we can perform a simple CTE in PostgreSQL using the employees’ table that we created earlier:
SELECT *
FROM employees
WHERE department = 'Finance'
)
SELECT *
FROM finance_employees;
Resulting Output:
You can check the query analysis as follows:
Example 2: CTE with Multiple Steps
The following shows how to perform a CTE with multiple steps:
SELECT department, SUM(salary) AS total_salary, COUNT(*) AS num_employees
FROM employees
GROUP BY department
),
department_averages AS (
SELECT department, total_salary / num_employees AS average_salary
FROM department_totals
)
SELECT *
FROM department_averages;
Output:
The example returns the average salary by department using the multi-step CTE.
Conclusion
This short tutorial explored how to build and work with CTEs in PostgreSQL. To learn more, you can check our tutorial on PostgreSQL recursive CTEs.
Source: linuxhint.com