简单的INSERT
INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM (
VALUES
(text 'testing', text 'blue') -- explicit type declaration; see below
, ('another row' , 'red' )
, ('new row1' , 'purple') -- purple does not exist in foo, yet
, ('new row2' , 'purple')
) val (description, type)
LEFT JOIN foo f USING (type);
LEFT [OUTER] JOIN
而不是 [INNER] JOIN
意味着在 val
中的所有行都会被保留,即使在 foo
中找不到匹配项。相反,NULL
会被输入到 foo_id
(如果该列定义为 NOT NULL
,则会引发异常)。
子查询中的 VALUES
表达式与 @ypercube's CTE 执行相同的操作。Common Table Expressions 提供了额外的功能,并且在处理大型查询时更易于阅读,但它们也可能成为优化障碍(直到 Postgres 12)。当不需要上述任何功能时,子查询通常会稍微快一些。
您可能需要进行显式类型转换。由于 VALUES
表达式没有直接连接到表(例如 INSERT ... VALUES ...
),因此无法推断出类型,并且除非显式指定类型,否则将使用默认数据类型。这在某些情况下可能无法正常工作。只需在第一行中执行此操作,其余行会自动适应。
同时插入
缺失的外键行
为了在foo
中动态创建缺失的条目,使用单个SQL语句,CTEs起到了关键作用:
WITH sel AS (
SELECT val.description, val.type, f.id AS foo_id
FROM (
VALUES
(text 'testing', text 'blue')
, ('another row', 'red' )
, ('new row1' , 'purple')
, ('new row2' , 'purple')
) val (description, type)
LEFT JOIN foo f USING (type)
)
, ins AS (
INSERT INTO foo (type)
SELECT DISTINCT type FROM sel WHERE foo_id IS NULL
RETURNING id AS foo_id, type
)
INSERT INTO bar (description, foo_id)
SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id)
FROM sel
LEFT JOIN ins USING (type);
Postgres 9.6的旧sqlfiddle - 在9.1中也是一样的。还请参见下面的新示例!
请注意要插入的两行附加行。它们都是紫色,在foo
中不存在。这是为了说明第一个INSERT
语句中需要使用DISTINCT
的两行。
逐步解释
第一个CTE(Common Table Expression)`sel`提供了多行输入数据。子查询`val`使用`VALUES`表达式可以被替换为作为源的表或子查询。立即通过`LEFT JOIN`连接到`foo`,以附加预先存在的`type`行的`foo_id`。这样,所有其他行都会得到`foo_id IS NULL`。
第二个CTE `ins`将新的不同类型(`foo_id IS NULL`)插入到`foo`中,并返回新生成的`foo_id` - 与`type`一起用于连接回插入的行。
最后的外部`INSERT`现在可以为每一行插入一个`foo_id`:要么该类型已经存在,要么它在步骤2中被插入。
严格来说,这两个插入操作是“并行”进行的,但由于这是一个“单一”的语句,所以默认的FOREIGN KEY约束不会报错。引用完整性默认在语句结束时强制执行。
如果同时运行多个这样的查询,可能会出现极小的竞争条件。请参考以下链接:
- [Postgres中的原子UPDATE .. SELECT](link1)
- [函数中的SELECT或INSERT是否容易出现竞争条件?](link2)
- [如何在可串行化隔离级别下实现插入-如果不存在则找到事务?](link3)
只有在高并发负载下才会真正发生,而且机会非常小,与其他答案中宣传的缓存解决方案相比,几乎可以忽略不计。
重复使用的函数
创建一个SQL函数,该函数以复合类型的数组作为参数,并在
VALUES
表达式的位置使用
unnest(param)
。
或者,如果这样的数组语法看起来太混乱,请使用逗号分隔的字符串作为参数
_param
。例如形式如下:
'description1,type1;description2,type2;description3,type3'
然后使用这个来替换上述语句中的
VALUES
表达式:
SELECT split_part(x, ',', 1) AS description
split_part(x, ',', 2) AS type
FROM unnest(string_to_array(_param, ';')) x;
在Postgres 9.5或更高版本中使用UPSERT的函数
创建一个用于参数传递的自定义行类型。虽然我们可以不使用它,但这样更简单:
CREATE TYPE foobar AS (description text, type text);
功能:
CREATE OR REPLACE FUNCTION f_insert_foobar(VARIADIC _val foobar[])
RETURNS void
LANGUAGE sql AS
$func$
WITH val AS (SELECT * FROM unnest(_val))
, typ AS (
SELECT v.type, f.id
FROM (SELECT DISTINCT type FROM val) v
LEFT JOIN foo f USING (type)
)
, ins AS (
INSERT INTO foo AS f (type)
SELECT type
FROM typ
WHERE id IS NULL
ON CONFLICT (type) DO UPDATE
SET type = EXCLUDED.type
RETURNING f.type, f.id
)
INSERT INTO bar AS b (description, foo_id)
SELECT v.description, COALESCE(t.id, i.id)
FROM val v
LEFT JOIN typ t USING (type)
LEFT JOIN ins i USING (type)
ON CONFLICT (description) DO UPDATE
SET foo_id = EXCLUDED.foo_id
WHERE b.foo_id <> EXCLUDED.foo_id;
$func$;
致电:
SELECT f_insert_foobar(
'(testing,blue)'
, '(another row,red)'
, '(new row1,purple)'
, '(new row2,green)'
, '("with,comma",green)'
);
db<>fiddle here
快速且稳定,适用于具有并发事务的环境。
除了上述查询之外,此函数还...
... 在 foo
上应用 SELECT
或 INSERT
:任何在外键表中不存在的 type
都会被插入,假设大多数类型已经存在。
... 在 bar
上应用 INSERT
或 UPDATE
(真正的 "UPSERT"):如果 description
已经存在,则更新其 type
,但仅当它实际发生变化时才更新。参见:
... 将值作为众所周知的行类型传递给具有 VARIADIC
函数参数。请注意,默认最大函数参数为100!参见:
还有许多其他传递多行的方法...
相关: