Using WITH clause and INSERT statement in POSTGRESQL

Question

There was a question asked several years ago with a similar title (Using WITH clause with INSERT statement in POSTGRESQL), but I need to figure out a more complicated situation.

I've used the example from POSTGRESQL for using a "With" statement (https://www.postgresql.org/docs/current/queries-with.html). Let's say I made a pre-computed table and wanted to insert the output from the query into it (top_region, total_sales, product_units, and product_sales), how would I do that?

Precomputed table

INSERT INTO top (top_region, total_sales, product_units, product_sales)
select top_region, total_sales, product_units, product_sales
from #not sure here

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

My query

WITH sales_data AS (
                    SELECT s.customer_id, s.product_id, s.quantity, pr.list_price, pr.category_id, c.state_id
                    FROM order_products s
                    INNER JOIN Products pr ON (s.product_id = pr.product_id)
                    INNER JOIN Customer c ON (s.customer_id = c.customer_id)
                ),
                t_20cat AS (
                    SELECT category_id, ROW_NUMBER() OVER (ORDER BY SUM(list_price*quantity) DESC) AS "rank",  SUM(list_price*quantity) AS total_sales FROM sales_data
                    GROUP BY category_id  ORDER BY total_sales DESC LIMIT 20
                ),
                t_20cust AS (
                    SELECT customer_id, ROW_NUMBER() OVER (ORDER BY SUM(list_price*quantity) DESC) AS "rank", SUM(list_price*quantity) AS total_sales FROM sales_data
                    GROUP BY customer_id  ORDER BY total_sales DESC LIMIT 20
                ),
                t_20cc AS (
                    SELECT customer_id, t_20cust.rank as customer_rank, category_id, t_20cat.rank as category_rank FROM t_20cat, t_20cust
                )
                SELECT t_20cc.*, COALESCE(SUM(sales_data.quantity), 0) AS quantity_sold, COALESCE(SUM(sales_data.list_price*sales_data.quantity), 0) AS dollar_value
                FROM t_20cc
                    LEFT JOIN sales_data ON (
                        t_20cc.customer_id = sales_data.customer_id AND t_20cc.category_id = sales_data.category_id
                    )
                GROUP BY t_20cc.customer_id, t_20cc.customer_rank, t_20cc.category_id, t_20cc.category_rank

INSERT INTO top_20(customer_id, customer_rank, category_id, category_rank
select category_id, category_rank, category_id, category_rank
from t_20cc
)

score 1 · Answer 1 · answered Mar 17 '22 at 04:00

1

Move the other SELECT query into its own CTE (region_summary), then reference that in the INSERT statement's SELECT ... FROM clause:

WITH regional_sales AS (
    SELECT
        region,
        SUM(amount) AS total_sales
    FROM
        orders
    GROUP BY
        region
),
top_regions AS (
    SELECT
        region
    FROM
        regional_sales
    WHERE
        total_sales > ( SELECT SUM( total_sales ) / 10 FROM regional_sales )
),
region_summary AS (
    SELECT
        region,
        product,
        SUM(quantity) AS product_units,
        SUM(amount) AS product_sales
    FROM
        orders
    WHERE
        region IN ( SELECT region FROM top_regions )
    GROUP BY
        region,
        product
)
INSERT INTO top ( top_region, total_sales, product_units, product_sales )
SELECT
    region AS top_region,
    product AS total_sales,
    product_units,
    product_sales
FROM
    region_summary;

answered Mar 17 '22 at 04:00

Dai

141,631
28
261
374

Thanks, when I try that with my own data, I get an error: "syntax error at or near "INSERT." I've included my query in my question. – James Mar 17 '22 at 04:39
@James Your bottommost `SELECT` needs to be named CTE too. – Dai Mar 17 '22 at 04:51
Yeah, I think that's what I'm confused about...should it be "select customer_id, t_20cust.rank as customer_rank, category_id, t_20cat.rank as category_rank" – James Mar 17 '22 at 04:56
and include these in from t_20cc, t_20cat, t_20cust? – James Mar 17 '22 at 05:03
or doing something like this: ```total AS ( SELECT t_20cc.*, ... GROUP BY t_20cc.customer_id, t_20cc.customer_rank, t_20cc.category_id, t_20cc.category_rank ) INSERT INTO top_20( customer_id, customer_rank, category_id, category_rank) select customer_id, customer_rank, category_id, category_rank from total;``` – James Mar 17 '22 at 05:15

Using WITH clause and INSERT statement in POSTGRESQL

1 Answers1