使用PostgreSQL 9.3在CTE UPSERT中生成DEFAULT值

本文介绍了使用PostgreSQL 9.3在CTE UPSERT中生成DEFAULT值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我发现使用可写CTE在PostgreSQL中模拟upsert是一个非常好的解决方案，直到我们在Postgres中获得实际的upsert/merge. (请参阅: stackoverflow/a/8702291/558819 )

I'm finding that using writable CTEs to emulate an upsert in PostgreSQL to be quite an elegant solution until we get actual upsert/merge in Postgres. (see: stackoverflow/a/8702291/558819)

但是，有一个问题:如何插入默认值?使用NULL当然无济于事，因为NULL被显式插入为NULL，与MySQL不同.一个例子:

However, there is one problem: how can I insert the default value? Using NULL won't help of course as NULL gets explicitly inserted as NULL, unlike for example with MySQL. An example:

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的默认值.

So I'd like for example for the legacy column to take on its default value for the second VALUES row.

我尝试了一些操作，例如在VALUES列表中显式使用DEFAULT，该操作不起作用，因为CTE不知道它要插入的内容.我还尝试了在插入中使用coalesce(col, DEFAULT)似乎也不起作用的声明.那么，有可能做我想做的事吗?

I've tried a few things, such as explicitly using DEFAULT in the VALUES list, which doesn't work because the CTE has no idea what it's inserting in. I've also tried coalesce(col, DEFAULT) in the insert statement which didn't seem to work either. So, is it possible to do what I want?

推荐答案

由Postgres 9.5实现的UPSERT.参见下文.

Postgres 9.5 implemented UPSERT. See below.

这是一个棘手的问题.您遇到了此限制(每个文档) :

This is a tricky problem. You are running into this restriction (per documentation):

在出现在INSERT顶层的VALUES列表中， 表达式可以用DEFAULT替换以指示目标位置 列的默认值应插入. DEFAULT在以下情况下无法使用 VALUES出现在其他上下文中.

In a VALUES list appearing at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the destination column's default value should be inserted. DEFAULT cannot be used when VALUES appears in other contexts.

强调粗体.如果没有要插入的表，则不会定义默认值.因此，您的问题没有直接解决方案，但是有许多可能的替代路线，具体取决于确切的需求.

Bold emphasis mine. Default values are not defined without a table to insert into. So there is no direct solution to your question, but there is a number of possible alternative routes, depending on exact requirements.

您可以从系统目录 pg_attrdef 就像@Patrick评论一样或来自"> c14> .在此处完成说明:

You could fetch those from the system catalog pg_attrdef like @Patrick commented or from information_schema.columns. Complete instructions here:

• 获取Postgres中表列的默认值?

但是您 still 仍然只有一个行列表，其中包含表达式的文本表示形式，以准备默认值.您将必须动态构建和执行语句才能使用值.乏味而凌乱.相反，我们可以让内置Postgres功能为我们做到这一点:

But then you still only have a list of rows with a text representation of the expression to cook the default value. You would have to build and execute statements dynamically to get values to work with. Tedious and messy. Instead, we can let built-in Postgres functionality do that for us:

插入一个虚拟行，并使其返回以使用生成的默认值:

Insert a dummy row and have it returned to use generated defaults:

INSERT INTO playlist_items DEFAULT VALUES RETURNING *;

问题/解决方案范围

• 仅保证此功能适用于 STABLE或IMMUTABLE默认表达式 .大多数VOLATILE功能都可以正常运行，但不能保证. current_timestamp函数系列之所以稳定，是因为它们的值在事务中不会改变. 特别是，这会对 serial 列(或序列中的其他任何默认设置)产生副作用.但这应该不成问题，因为通常不会直接写入serial列.这些根本不应该在INSERT语句中列出. serial列的剩余缺陷:一次调用仍可推进序列以获取默认行，从而在编号上产生间隙.再次，这应该不成问题，因为通常会在serial列中出现间隙 .

Problems / scope of the solution

• This is only guaranteed to work for STABLE or IMMUTABLE default expressions. Most VOLATILE functions will work just as well, but there are no guarantees. The current_timestamp family of functions qualify as stable, since their values do not change within a transaction. In particular, this has side effects on serial columns (or any other defaults drawing from a sequence). But that should not be a problem, because you don't normally write to serial columns directly. Those shouldn't be listed in INSERT statements at all. Remaining flaw for serial columns: the sequence is still advanced by the single call to get a default row, producing a gap in the numbering. Again, that should not be a problem, because gaps are generally to be expected in serial columns.

可以解决另外两个问题:

Two more problems can be solved:

• 如果已定义 NOT NULL 列，则必须插入虚拟值并在结果中替换为NULL.

我们实际上并不想插入虚拟行.我们可以稍后删除(在同一事务中)，但这可能会有更多副作用，例如触发器ON DELETE.有更好的方法:

We do not actually want to insert the dummy row. We could delete later (in the same transaction), but that may have more side effects, like triggers ON DELETE. There is a better way:

克隆包含列默认值的临时表，并将其插入那个:

Clone a temporary table including column defaults and insert into that:

BEGIN; CREATE TEMP TABLE tmp_playlist_items (LIKE playlist_items INCLUDING DEFAULTS) ON COMMIT DROP; -- drop at end of transaction INSERT INTO tmp_playlist_items DEFAULT VALUES RETURNING *; ...

相同的结果，更少的副作用.由于默认表达式是逐字复制的，因此克隆将从相同的序列中提取(如果有).但是，完全避免了不必要的行或触发器带来的其他副作用.

Same result, fewer side effects. Since default expressions are copied verbatim, the clone draws from the same sequences if any. But other side effects from the unwanted row or triggers are avoided completely.

向伊戈尔(Igor)征求意见:

Credit to Igor for the idea:

• Postgresql，选择假"行

您必须为NOT NULL列提供伪值，因为(每个文档):

You would have to provide dummy values for NOT NULL columns, because (per documentation):

非空约束始终会复制到新表中.

Not-null constraints are always copied to the new table.

要么适应INSERT语句中的要求，要么(更好)消除约束:

Either accommodate for those in the INSERT statement or (better) eliminate the constraints:

ALTER TABLE tmp_playlist_items ALTER COLUMN foo DROP NOT NULL , ALTER COLUMN bar DROP NOT NULL;

有一种具有超级用户特权的 快速且肮脏的方式 :

There is a quick and dirty way with superuser privileges:

UPDATE pg_attribute SET attnotnull = FALSE WHERE attrelid = 'tmp_playlist_items'::regclass AND attnotnull AND attnum > 0;

它只是一个临时表，没有数据，没有其他用途，在事务结束时被删除.所以捷径很诱人.不过，基本规则是:永远不要直接篡改系统目录.

It is just a temporary table with no data and no other purpose, and it's dropped at the end of the transaction. So the shortcut is tempting. Still, the basic rule is: never tamper with system catalogs directly.

因此，让我们研究一下 干净的方式 : 在DO语句中使用动态SQL自动执行.您只需要保证拥有常规特权，因为同一角色创建了临时表.

So, let's look into a clean way: Automate with dynamic SQL in a DO statement. You just need the regular privileges you are guaranteed to have since the same role created the temp table.

DO $$BEGIN EXECUTE ( SELECT 'ALTER TABLE tmp_playlist_items ALTER ' || string_agg(quote_ident(attname), ' DROP NOT NULL, ALTER ') || ' DROP NOT NULL' FROM pg_catalog.pg_attribute WHERE attrelid = 'tmp_playlist_items'::regclass AND attnotnull AND attnum > 0 ); END$$

更清洁，但速度仍然非常快.使用动态命令执行维护，并警惕SQL注入.这句话是安全的.我已经发布了几个相关的答案以及更多的解释.

Much cleaner and still very fast. Execute care with dynamic commands and be wary of SQL injection. This statement is safe. I have posted several related answers with more explanation.

BEGIN; CREATE TEMP TABLE tmp_playlist_items (LIKE playlist_items INCLUDING DEFAULTS) ON COMMIT DROP; DO $$BEGIN EXECUTE ( SELECT 'ALTER TABLE tmp_playlist_items ALTER ' || string_agg(quote_ident(attname), ' DROP NOT NULL, ALTER ') || ' DROP NOT NULL' FROM pg_catalog.pg_attribute WHERE attrelid = 'tmp_playlist_items'::regclass AND attnotnull AND attnum > 0 ); END$$; LOCK TABLE playlist_items IN EXCLUSIVE MODE; -- forbid concurrent writes WITH default_row AS ( INSERT INTO tmp_playlist_items DEFAULT VALUES RETURNING * ) , new_values (id, playlist, item, group_name, duration, sort, legacy) AS ( VALUES (651, 21, 30012, 'a', 30, 1, FALSE) , (NULL, 21, 1, 'b', 34, 2, NULL) , (668, 21, 30012, 'c', 30, 3, FALSE) , (7428, 21, 23068, 'd', 0, 4, FALSE) ) , upsert AS ( -- *not* replacing existing values in UPDATE (?) UPDATE playlist_items m SET ( playlist, item, group_name, duration, sort, legacy) = (n.playlist, n.item, n.group_name, n.duration, n.sort, n.legacy) -- ..., COALESCE(n.legacy, m.legacy) -- see below FROM new_values n WHERE n.id = m.id RETURNING m.id ) INSERT INTO playlist_items (playlist, item, group_name, duration, sort, legacy) SELECT n.playlist, n.item, n.group_name, n.duration, n.sort , COALESCE(n.legacy, d.legacy) FROM new_values n, default_row d -- single row can be cross-joined WHERE NOT EXISTS (SELECT 1 FROM upsert u WHERE u.id = n.id) RETURNING id; COMMIT;

仅当您有尝试写入同一表的并发事务时，才需要LOCK.

You only need the LOCK if you have concurrent transactions trying to write to the same table.

根据要求，这仅替换INSERT情况下输入行中的legacy列中的NULL值.可以轻松扩展以适用于其他列或UPDATE情况.例如，也可以有条件地UPDATE:仅当输入值为NOT NULL时.我在上面的UPDATE中添加了一条注释行.

As requested, this only replaces NULL values in the column legacy in the input rows for the INSERT case. Can easily be extended to work for other columns or in the UPDATE case as well. For instance, you could UPDATE conditionally as well: only if the input value is NOT NULL. I added a commented line to the UPDATE above.

此外:由于类型是从 first 行派生的，因此您无需在VALUES表达式中的第一行中的任何行中 cast 值.

Aside: You do not need to cast values in any row but the first in a VALUES expression, since types are derived from the first row.

通过INSERT .. ON CONFLICT .. DO NOTHING | UPDATE实现 UPSERT .这在很大程度上简化了操作:

implements UPSERT with INSERT .. ON CONFLICT .. DO NOTHING | UPDATE. This largely simplifies the operation:

INSERT INTO playlist_items AS m (id, playlist, item, group_name, duration, sort, legacy) VALUES (651, 21, 30012, 'a', 30, 1, FALSE) , (DEFAULT, 21, 1, 'b', 34, 2, DEFAULT) -- ! , (668, 21, 30012, 'c', 30, 3, FALSE) , (7428, 21, 23068, 'd', 0, 4, FALSE) ON CONFLICT (id) DO UPDATE SET (playlist, item, group_name, duration, sort, legacy) = (EXCLUDED.playlist, EXCLUDED.item, EXCLUDED.group_name , EXCLUDED.duration, EXCLUDED.sort, EXCLUDED.legacy) -- (..., COALESCE(l.legacy, EXCLUDED.legacy)) -- see below RETURNING m.id;

我们可以将VALUES子句直接附加到INSERT，这允许使用DEFAULT关键字.如果在(id)上发生唯一冲突，则Postgres会更新.我们可以在UPDATE中使用排除的行. 手册:

We can attach the VALUES clause to INSERT directly, which allows the DEFAULT keyword. In the case of unique violations on (id), Postgres updates instead. We can use excluded rows in the UPDATE. The manual:

ON CONFLICT DO UPDATE中的SET和WHERE子句可以访问 使用表名(或别名)的现有行以及行 建议使用特殊的excluded表进行插入.

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

并且:

请注意，所有按行BEFORE INSERT触发器的影响都是 反映在排除的值中，因为这些影响可能会有所贡献 到从插入中排除的行.

Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, since those effects may have contributed to the row being excluded from insertion.

剩余的拐角处情况

UPDATE有多种选择:您可以...

Remaining corner case

You have various options for the UPDATE: You can ...

• ...根本不更新:在UPDATE中添加WHERE子句以仅写入选定的行.
• ...仅更新选定的列.
• ...仅当列当前为NULL时更新:COALESCE(l.legacy, EXCLUDED.legacy)
• ...仅在新值为NOT NULL时更新:COALESCE(EXCLUDED.legacy, l.legacy)
• ... not update at all: add a WHERE clause to the UPDATE to only write to selected rows.
• ... only update selected columns.
• ... only update if the column is currently NULL: COALESCE(l.legacy, EXCLUDED.legacy)
• ... only update if the new value is NOT NULL: COALESCE(EXCLUDED.legacy, l.legacy)

但是无法辨别DEFAULT值和INSERT中实际提供的值.仅显示结果EXCLUDED行.如果需要区别对待，请退回到先前的解决方案，在此解决方案中，我们都可以使用.

But there is no way to discern DEFAULT values and values actually provided in the INSERT. Only resulting EXCLUDED rows are visible. If you need the distinction, fall back to the previous solution, where you have both at our disposal.