JIYIK CN >

withStatements are used to create temporary tables, which means that these tables are not persisted in the database and exist only in memory until the query is completed.

The statement was introduced withto break down complex queries into simpler queries that are easier to process and debug.

This article will teach you how to use multiple withstatements to execute a query using two temporary tables in PostgreSQL.

WITHUsing multiple statements in one PostgreSQL query

Login to your PostgreSQL database using the following command. The default user is postgres.

If you have multiple users in the database, change the username. If you configured user authentication during login, enter the password in the next prompt.

david@david-HP-ProBook-6470b:~$ psql -U postgres

After successfully logging into the PostgreSQL server, use the following commands to create and connect to the database we will use to store data.

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 the temporary table. The first table will hold the customer data.

Create customerthe table as shown in the following data definition language. To create the table, you can copy and paste the query on the 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

Use the following data manipulation language customerto create some customers in the table. You can copy and paste the query on the terminal to insert 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 record was 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 order information for products purchased by customers. Create customer_orderthe 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 customer_orderthe table and ensure that the referential integrity constraint references 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 has been successfully saved 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)

WITHUse commas to separate multiple statements in PostgreSQL

To use multiple withstatements, follow the first withstatement with a comma ( ,) instead of another withstatement.

The following example shows how we can withexecute a query using multiple statements separated by commas.

The first temporary table is customercreated with all the data in the table, and the second temporary table is customer_ordercreated with all the data in the table.

Execute a query on the temporary table to return two columns, one containing the customer's email and the other containing the total price of the products purchased 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)