postgresql，插入几组 3 个项目，每组有 2 个相同的项目

Question

我需要插入几组 3 项。每套有 2 件元素相同。我想把这个子句放在 $1 的位置: EXTRACT(EPOCH FROM NOW() AT TIME ZONE 'UTC')

WITH data(serial_id, banned_ts, reason) AS (
    VALUES (x, $1, $2),(y, $1, $2),(z, $1, $2)
    )
    INSERT INTO _serial_ids_banned (serial_id, banned_ts, reason)
    SELECT d.serial_id, d.banned_ts, d.reason
    FROM data d
    WHERE NOT EXISTS
        (SELECT 1 FROM _serial_ids_banned b
        WHERE b.serial_id = d.serial_id);

如果我这样做，那么该子句将被评估 3 次(或更多，具体取决于集合的数量)。是否可以创建这样的查询，只评估一次子句并在每个集合中使用它？

Answer 1

或显式连接:

t=# prepare so1(text,text) as
with p(banned_ts, reason) as (values ($1,$2))
, sid(serial_id) as (values('x'),('y'),('z'))
, data as (select * from sid join p on true)
select * from data;
PREPARE
t=# execute so1('a','b');
 serial_id | banned_ts | reason
-----------+-----------+--------
 x         | a         | b
 y         | a         | b
 z         | a         | b
(3 rows)