How to Use Multiple WITH Statements in One PostgreSQL Query
-
Use Multiple
WITHStatements in One PostgreSQL Query -
Use a Comma to Separate Multiple
WITHStatements in PostgreSQL
The with statement is used to create temporary tables, and this means that the tables are not persisted in the database and only exist in memory until the query is complete.
The with statement was introduced to break complex queries into simple queries that are easier to process and debug.
This tutorial will teach how to use multiple with statements to execute a query using two temporary tables in PostgreSQL.
Use Multiple WITH Statements in One PostgreSQL Query
Login to your PostgreSQL database using the following command. The default user is postgres.
Change the username if you have more than one user in the database. Enter password on the next prompt if you have configured user authentication during login.
david@david-HP-ProBook-6470b:~$ psql -U postgres
After successfully logging in to the PostgreSQL server, create and connect to the database that we will use to store our data using the following command.
postgres=# create database multiple_with_db;
CREATE DATABASE
postgres=# \c multiple_with_db;
You are now connected to database "multiple_with_db" as user "postgres".
We first need to create two persistent tables from which we will create temporary tables. The first table will hold customers data.
Create the customer table as shown in the following data definition language. To create the table, you can copy and paste the query on your terminal and press Enter.
multiple_with_db=# create table customer(customer_id SERIAL UNIQUE NOT NULL,first_name varchar(50),last_name varchar(50),email varchar(60),PRIMARY KEY(customer_id));
CREATE TABLE
Create some customers in the customer table using the following data manipulation language. You can copy and paste the query on your terminal to insert the records into the table.
multiple_with_db=# insert into customer(first_name, last_name, email) values('john','doe','john@gmail.com');
INSERT 0 1
multiple_with_db=# insert into customer(first_name, last_name, email) values('mary','public','mary@gmail.com');
INSERT 0 1
multiple_with_db=# insert into customer(first_name, last_name, email) values('peter','parker','peter@gmail.com');
INSERT 0 1
multiple_with_db=# insert into customer(first_name, last_name, email) values('steve','harvey','steve@gmail.com');
INSERT 0 1
Use the following query to verify that your records were created successfully.
multiple_with_db=# select * from customer;
Output:
customer_id | first_name | last_name | email
-------------+------------+-----------+-----------------
1 | john | doe | john@gmail.com
2 | mary | public | mary@gmail.com
3 | peter | parker | peter@gmail.com
4 | steve | harvey | steve@gmail.com
(4 rows)
The second table contains customer’s order information of products they have bought. Create the customer_order table, as shown below.
multiple_with_db=# create table customer_order(order_id SERIAL UNIQUE NOT NULL, product_name varchar(50), product_price integer, product_quantity integer, total_price integer, created_at DATE, cust_id integer REFERENCES customer(customer_id));
CREATE TABLE
Insert some records into the customer_order table and ensure referential integrity constraints are referencing the customer, as shown below.
multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,3,3*500,'2022-03-07',1);
INSERT 0 1
multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,4,4*500,'2022-03-07',3);
INSERT 0 1
multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,7,7*500,'2022-03-07',4);
INSERT 0 1
multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,5,5*500,'2022-03-07',2);
INSERT 0 1
Use the following query to ensure that your data was persisted successfully in the database.
multiple_with_db=# select * from customer_order;
Output:
order_id | product_name | product_price | product_quantity | total_price | created_at | cust_id
----------+--------------+---------------+------------------+-------------+------------+---------
1 | laptop | 500 | 3 | 1500 | 2022-03-07 | 1
2 | laptop | 500 | 4 | 2000 | 2022-03-07 | 3
3 | laptop | 500 | 7 | 3500 | 2022-03-07 | 4
5 | laptop | 500 | 5 | 2500 | 2022-03-07 | 2
(4 rows)
Use a Comma to Separate Multiple WITH Statements in PostgreSQL
To use multiple with statements, the first with statement is followed by a comma (,) but not another with statement.
The following example shows how we can use multiple with statements separated by a comma to execute a query.
The first temporary table is created with all the data in the customer table, and the second temporary table is created with all the data in the customer_order table.
The query is executed on the temporary tables to return two columns, one containing the email of customers and the other column containing the total prices of products bought by each customer.
multiple_with_db=# WITH customer_info AS (select * from customer), order_info AS (select * from customer_order) SELECT (email,total_price) FROM customer_info t1 INNER JOIN order_info t2 ON t1.customer_id=t2.order_id;
Output:
row
------------------------
(john@gmail.com,1500)
(mary@gmail.com,2000)
(peter@gmail.com,3500)
(steve@gmail.com,2500)
(4 rows)
David is a back end developer with a major in computer science. He loves to solve problems using technology, learning new things, and making new friends. David is currently a technical writer who enjoys making hard concepts easier for other developers to understand and his work has been published on multiple sites.
LinkedIn GitHub