postgresql 多个insert语句重用子表

hiz5n14c 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(165)

我有一种情况，我需要将数据插入到两个表中，其中插入的数据来自同一个长子查询。我知道CTE在类似的情况下会很有帮助，因为您可以定义一次子查询，并在后续语句中的多个地方重用它。问题是我没有一个语句要使用子查询，我有两个，我的理解是CTE只能在一个语句中使用。
所以我的问题是我怎样才能避免重复

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

下面的部分？

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);

postgresql

来源：https://stackoverflow.com/questions/77293826/reuse-sub-table-for-multiple-insert-statements

2条答案

按热度按时间

c0vxltue1#

请参见示例。
CTE只能在单个语句中使用。
返回条款很有用。
CTE中的第一个删除操作返回所有删除的行。
然后将它们插入第二个表。

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
;

我们可以在查询计划中看到，内部查询运行1次。

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)

下一个查询与更高的查询相同

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

赞(0）回复(0）举报 2023-10-18

xqkwcwgp2#

先把它写成一个临时表。
使用比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;

赞(0）回复(0）举报 2023-10-18

我来回答

postgresql 多个insert语句重用子表

2条答案

相关问题

热门标签

最新问答