在PostgreSQL中模拟UPSERT的可写CTE看起来相当优雅。(直到我们在Postgres中得到实际的upsert/merge。参见:https://stackoverflow.com/a/8702291/558819)
使用PostgreSQL 9.3,有一个问题:如何插入默认值?使用NULL
当然没有帮助,因为NULL
被显式插入为NULL
,这与MySQL的示例不同。举个例子:
WITH new_values (id, playlist, item, group_name, duration, sort, legacy) AS (
VALUES (651, 21, 30012, 'a', 30, 1, FALSE)
, (NULL::int, 21, 1, 'b', 34, 2, NULL::boolean)
, (668, 21, 30012, 'c', 30, 3, FALSE)
, (7428, 21, 23068, 'd', 0, 4, FALSE)
), upsert AS (
UPDATE playlist_items m
SET (playlist, item, group_name, duration, sort, legacy)
= (nv.playlist, nv.item, nv.group_name, nv.duration, nv.sort, nv.legacy)
FROM new_values nv
WHERE nv.id = m.id
RETURNING m.id
)
INSERT INTO playlist_items (playlist, item, group_name, duration, sort, legacy)
SELECT playlist, item, group_name, duration, sort, legacy
FROM new_values nv
WHERE NOT EXISTS (SELECT 1
FROM upsert m
WHERE nv.id = m.id)
RETURNING id
例如,我希望legacy
列的第二行VALUES
采用其默认值。
我尝试了一些方法,比如在VALUES列表中显式地使用DEFAULT
,但这不起作用,因为CTE不知道它插入了什么。我还在insert语句中尝试了coalesce(col, DEFAULT)
,似乎也不起作用。所以,我想做的事有可能吗?
1条答案
按热度按时间g2ieeal71#
Postgres 9.5或更高版本
INSERT ... ON CONFLICT ... DO NOTHING | UPDATE
**正确实现。这大大简化了操作:我们可以直接将
VALUES
子句附加到INSERT
,这允许使用DEFAULT
关键字。在(id)
上发生唯一违规的情况下,Postgres会进行更新。我们可以在UPDATE
中使用排除的行。手册:ON CONFLICT DO UPDATE
中的SET
和WHERE
子句可以使用表名(或别名)访问现有行,并可以使用特殊的excluded
表访问建议插入的行。并且:
请注意,所有每行
BEFORE INSERT
触发器的影响都反映在排除的值中,因为这些影响可能导致该行被排除在插入之外。剩余边角案例
UPDATE
有多种选项:你可以…UPDATE
中添加WHERE
子句,以仅写入选定的行。COALESCE(l.legacy, EXCLUDED.legacy)
NOT NULL
时更新:COALESCE(EXCLUDED.legacy, l.legacy)
但是没有办法区分
DEFAULT
值和INSERT
中实际提供的值。只有生成的EXCLUDED
行可见。如果您需要区分,请回到前面的解决方案,在那里您可以使用这两种解决方案。Postgres 9.4及以上版本
这是一个棘手的问题。您遇到了以下限制(根据文档):
在
INSERT
的顶层出现的VALUES
列表中,表达式可以替换为DEFAULT
,以指示应插入目标列的默认值。* * 当VALUES
出现在其他上下文时,DEFAULT
不能使用。**大胆强调我的。如果没有要插入的表,则不会定义默认值。因此,对于您的问题没有直接的解决方案,但有许多可能的替代路线,这取决于确切的要求。
从系统目录中获取默认值?
你可以像@Patrick评论的那样从系统目录
pg_attrdef
或information_schema.columns
中获取这些。完整的说明在这里:但是,您仍然只有一个包含表达式的文本表示的行列表来烹饪默认值。您必须动态地构建和执行语句以获取要使用的值。乏味又混乱。相反,我们可以让 * 内置的Postgres功能为我们做这件事 *:
简单快捷方式
插入一个虚拟行并将其返回以使用生成的默认值:
问题/解决方案范围
这只能保证适用于
STABLE
orIMMUTABLE
default expressions。大多数VOLATILE
函数都能正常工作,但不能保证。current_timestamp
系列函数被认为是稳定的,因为它们的值在事务中不会改变。特别是,这对**
serial
**列(或从序列绘制的任何其他默认值)有副作用。但这应该不是问题,因为您通常不会直接写入serial
列。这些根本不应该在INSERT
语句中列出。serial
列的剩余缺陷:序列仍然通过单个调用前进以获得默认行,从而在编号中产生间隙。同样,这应该不是问题,因为serial
列中的间隙 * 通常是预期的 *。还有两个问题可以解决:
NOT NULL
**,则必须插入伪值并在结果中替换为NULL
。ON DELETE
。有一个更好的方法:避免伪行
克隆一个临时表,包括列默认值,并插入到 * that * 中:
同样的结果,更少的副作用。由于默认表达式是逐字复制的,因此克隆将从相同的序列(如果有的话)进行绘制。但是完全避免了来自不需要的行或触发器的其他副作用。
感谢Igor的创意:
删除
NOT NULL
约束您必须为
NOT NULL
列提供虚拟值,因为(per documentation):非空约束始终复制到新表中。
要么适应
INSERT
语句中的约束,要么(最好)消除约束:有一个***快速和肮脏的方法***与超级用户权限:
它只是一个没有数据也没有其他用途的临时表,在事务结束时被删除。所以捷径很诱人。不过,基本规则是:切勿直接篡改系统目录。
所以,让我们来看看一个干净的方法:在
DO
语句中自动执行动态SQL。您只需要保证拥有的 * 常规特权 *,因为相同的角色创建了临时表。干净得多,仍然非常快。小心执行动态命令,并警惕SQL注入。此声明是安全的。参见:
通用解决方案(9.4及以上版本)
如果有并发事务试图写入同一个表,则只需要
LOCK
。按照要求,对于
INSERT
情况,这只替换输入行中legacy
列中的NULL值。可以很容易地扩展到其他列或UPDATE
的情况下工作。例如,你也可以有条件地使用UPDATE
:仅当输入值为NOT NULL
时。我在上面的UPDATE
中添加了一个注解行。旁白:除了
VALUES
表达式中的第一行之外,您不需要对任何行中的值进行 * 转换 *,因为类型是从 * 第一行派生的。