sql - 仅当记录存在时才插入到 Postgres 表中

Question

我正在 Postgres 中编写一些 sql 来更新审计表。我的 sql 将根据某些条件更新正在审核的表，然后选择该更新的记录来更新审核表中的信息。这是我目前所拥有的:

DO $$
  DECLARE
    jsonValue json;
    revId int;
    template RECORD;
    BEGIN
        jsonValue = '...Some JSON...'
        UPDATE projectTemplate set json = jsonValue where type='InstallationProject' AND account_id IS NULL;

        template := (SELECT pt FROM ProjectTemplate pt WHERE pt.type='InstallationProject' AND pt.account_id IS NULL);

        IF EXISTS (template) THEN
        (
            revId := nextval('hibernate_sequence');
            insert into revisionentity (id, timestamp) values(revId, extract(epoch from CURRENT_TIMESTAMP));

            insert into projectTemplate_aud (rev, revtype, id, name, type, category, validfrom, json, account_id)
                VALUES (revId, 1, template.id, template.name, template.type, template.category, template.validfrom, jsonValue, template.account_id);
        )
END $$;

我的理解是，如果表中没有匹配该查询的内容(目前没有)，template 将是未定义的。我想这样做，这样如果 template 不存在，我的查询就不会尝试更新审计表。

我该怎么做才能更新此 sql 以匹配我正在尝试执行的操作？

Answer 1

您不能使用 EXISTS像那样，它需要一个子查询 表达式。加上您的代码的其他一些问题。

这 strip 有数据修改 CTE 的 SQL DML 语句应该正确地替换您的 DO 命令。而且速度也更快:

WITH upd AS (
   UPDATE ProjectTemplate
   SET    json = '...Some JSON...'
   WHERE  type = 'InstallationProject'
   AND    account_id IS NULL
   RETURNING *
   )
, ins AS (
   INSERT INTO revisionentity (id, timestamp)
   SELECT nextval('hibernate_sequence'), extract(epoch FROM CURRENT_TIMESTAMP)
   WHERE  EXISTS (SELECT FROM upd)  -- minimal valid EXISTS expression!
   RETURNING id
   )
INSERT INTO ProjectTemplate_aud
      (rev , revtype,   id,   name,   type,   category,   validfrom,   json,   account_id)
SELECT i.id, 1      , u.id, u.name, u.type, u.category, u.validfrom, u.json, u.account_id
FROM   upd u, ins i;

如果 UPDATE 找到任何行，则在 revisionentity 中插入一个单个行。

插入与更新的行一样多的行projectTemplate_aud。

关于数据修改 CTE:

• Insert data in 3 tables at a time using Postgres

旁白:我看到混合了 CaMeL 大小写、一些下划线或只是小写的名称。只考虑合法的小写名称(并避免将基本类型名称作为列名称)。不过，最重要的是要保持一致。相关:

• Are PostgreSQL column names case-sensitive?
• Misnamed field in subquery leads to join