Question 265291 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Reuse sub table for multiple insert statements

I have a situation where I need to insert data into two tables, where the inserted data is derived from the same long sub query. I know that CTEs can be helpful in similar situations since you can define a subquery once, and reuse it in multiple places in the subsequent statement. The problem is I don’t have a single statement I want to use the subquery for, I have two, and my understanding is the CTEs only be used in a single statement.

So my question is how can I avoid repeating the

SELECT col1 
FROM table3 
WHERE col2 > 5 
  AND col2 < 10 
ORDER BY col2 DESC

part of the below?

INSERT INTO table1 (col1, othercol)
    SELECT col1, 'foo' 
    FROM
        (SELECT col1 FROM table3 WHERE col2 > 5 AND col2 < 10 ORDER BY col2 DESC);
INSERT INTO table2 (col1, othercol)
    SELECT col1, 'bar' 
    FROM
        (SELECT col1 FROM table3 WHERE col2 > 5 AND col2 < 10 ORDER BY col2 DESC);
Tags:

2

Answers


  1. 0

    Write it as a temp table first.

    Use a better name than tmp 🙂

    DROP TABLE IF EXISTS tmp;

CREATE TEMP TABLE tmp
AS
SELECT col1 
FROM table3 
WHERE col2 > 5 AND col2 < 10 
ORDER BY col2 DESC;

INSERT INTO table1 (col1, othercol)
SELECT col1, 'foo' 
FROM tmp;

INSERT INTO table2 (col1, othercol)
SELECT col1, 'bar' 
FROM tmp;
    Login or Signup to reply.
  2. 0

    See example.
    CTEs can only be used in a single statement.
    There RETURNING clause is helpful.
    First INSERT operation in CTE returns all INSERTED rows.
    Then insert them into second table.

    with q1 as (
  INSERT INTO table2 (col1, othercol)
    SELECT col1, 'bar' 
    FROM
        (SELECT col1 FROM table3 WHERE col2 > 5 AND col2 < 10 ORDER BY col2 DESC) SubQuery1
  returning *
)
INSERT INTO table1 (col1, othercol)
    SELECT col1, 'foo' 
    FROM q1
;

    We can see on query plan, internal query runs 1 time.

    QUERY PLAN
Insert on table1  (cost=22.99..23.07 rows=0 width=0)
  CTE q1
    ->  Insert on table2  (cost=22.94..22.99 rows=4 width=62)
          ->  Subquery Scan on subquery1  (cost=22.94..22.99 rows=4 width=62)
                ->  Sort  (cost=22.94..22.95 rows=4 width=8)
                      Sort Key: table3.col2 DESC
                      ->  Seq Scan on table3  (cost=0.00..22.90 rows=4 width=8)
                            Filter: ((col2 > 5) AND (col2 < 10))
  ->  CTE Scan on q1  (cost=0.00..0.08 rows=4 width=62)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search