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

发布时间：2020-12-13 16:18:37 所属栏目：百科来源：网络整理

导读：我发现使用可写CTE在PostgreSQL中模拟upsert是一个非常优雅的解决方案,直到我们在Postgres中获得实际的upsert / merge. (见： https://stackoverflow.com/a/8702291/558819) 但是,有一个问题：如何插入默认值？当然,使用NULL无助于将NULL显式插入为NULL,这与

我发现使用可写CTE在PostgreSQL中模拟upsert是一个非常优雅的解决方案,直到我们在Postgres中获得实际的upsert / merge. (见： https://stackoverflow.com/a/8702291/558819)

但是,有一个问题：如何插入默认值？当然,使用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,'b',34,2,NULL::boolean),(668,'c',3,(7428,23068,'d',4,FALSE)
),upsert AS (
    UPDATE playlist_items m
    SET    (playlist,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,legacy)
SELECT playlist,legacy
FROM   new_values nv
WHERE  NOT EXISTS (SELECT 1
                   FROM   upsert m
                   WHERE  nv.id = m.id)
RETURNING id

因此,我希望遗留列能够采用第二个VALUES行的默认值.

我已经尝试过一些东西,例如在VALUES列表中明确使用DEFAULT,这不起作用,因为CTE不知道它插入了什么.我还在insert语句中尝试了coalesce(col,DEFAULT)似乎也没有工作.那么,有可能做我想要的吗？

Postgres 9.5实现了UPSERT.见下文.

Postgres 9.4或更早

这是一个棘手的问题.您遇到此限制(per documentation)：

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.

大胆强调我的.如果没有要插入的表,则不会定义缺省值.因此,您的问题没有直接的解决方案,但根据具体要求,有许多可能的替代路线.

从系统目录中获取默认值？

您可以从系统目录pg_attrdef like @Patrick commented或information_schema.columns中获取它们.完整说明如下：

> Get the default values of table columns in Postgres?

但是,您仍然只有一个行列表,其中包含表达式的文本表示以烹饪默认值.您必须动态构建和执行语句才能获取要使用的值.单调乏味.相反,我们可以让内置的Postgres功能为我们做到这一点：

简单的捷径

插入一个虚拟行并让它返回使用生成的默认值：

INSERT INTO playlist_items DEFAULT VALUES RETURNING *;

解决方案的问题/范围

>这仅保证适用于STABLE or IMMUTABLE default expressions.大多数VOLATILE功能也可以正常工作,但无法保证. current_timestamp系列函数符合稳定条件,因为它们的值在事务中不会更改.
特别是,这会对串行列(或从序列中绘制的任何其他默认值)产生副作用.但这应该不是问题,因为您通常不直接写入串行列.那些不应该在INSERT语句中列出.
串行列的剩余缺陷：序列仍然通过单次调用来提前获取默认行,从而在编号中产生间隙.同样,这应该不是问题,因为在串行列中通常会出现间隙.

还有两个问题可以解决：

>如果您将列定义为NOT NULL,则必须插入虚拟值并在结果中替换为NULL.
>我们实际上并不想插入虚拟行.我们可以稍后删除(在同一个事务中),但可能有更多的副作用,如触发器ON DELETE.有一个更好的方法：

避免虚拟行

克隆临时表,包括列默认值并插入到：

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 *;
...

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

感谢Igor的想法：

> Postgresql,select a “fake” row

删除NOT NULL约束

您必须为NOT NULL列提供虚拟值,因为(per documentation)：

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

适应INSERT语句中的那些或(更好)消除约束：

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

有超级用户权限的快速和脏的方式：

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

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

那么,让我们看看一个干净的方式：
在DO语句中使用动态SQL自动执行.您只需要保证您拥有的常规权限,因为相同的角色创建了临时表.

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注入.这句话很安全.我已经发布了several related answers with more explanation.

一般解决方案(9.4及以上)

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),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,legacy) AS (
   VALUES
      (651,(NULL,NULL),FALSE)
   ),upsert AS (  -- *not* replacing existing values in UPDATE (?)
   UPDATE playlist_items m
   SET   (  playlist,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,legacy)
SELECT n.playlist,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.

根据请求,这仅替换INSERT案例的输入行中legacy列中的NULL值.可以轻松扩展到其他列或在UPDATE情况下工作.例如,您也可以有条件地更新：仅当输入值为NOT NULL时.我在上面的UPDATE中添加了一条注释行.

旁白：您不需要在任何行中转换值,而是在VALUES表达式中转换第一行,因为类型是从第一行派生的.

Postgres 9.5

使用INSERT实现UPSERT .. ON CONFLICT .. DOOUTHING | UPDATE.这大大简化了操作：

INSERT INTO playlist_items AS m (id,legacy)
VALUES (651,(DEFAULT,DEFAULT)  -- !,FALSE)
ON CONFLICT (id) DO UPDATE
SET (playlist,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中使用排除的行. The manual:

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.

和：

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添加WHERE子句以仅写入选定的行.
> …仅更新选定的列.
> …仅当列当前为NULL时才更新：COALESCE(l.legacy,EXCLUDED.legacy)
> …仅在新值为NOT NULL时才更新：COALESCE(EXCLUDED.legacy,l.legacy)

但是无法识别INSERT中实际提供的DEFAULT值和值.只显示生成的EXCLUDED行.如果您需要区分,请回到之前的解决方案,您可以随意使用.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!