postgresql - 无法更新 postgresql 中的值

Question

如果我的桌子上有任何更新，我正在使用触发器 - “测试”。我已经创建了如下所述的触发器。

create table log_changes(one integer primary key not null, old_three text, new_three text, old_four text, new_four text, old_five text, new_five text, update_time timestamp DEFAULT Now());
    
create or replace function log_changes()
returns trigger as
$$
BEGIN
  insert into log_changes(one,old_three , new_three , old_four , new_four , old_five , new_five ) 
  values 
  (one, old.old_three , new.new_three , old.old_four , new.new_four , old.old_five , new.new_five);
  return new;
END;
$$ language plpgsql


create trigger update_log_table 
after update on test
for each row execute procedure log_changes();

update test 
  set three = 'aa' and four = 'aa' and five = 'aa' 
where one = 1 and two = 'a';

执行更新查询后，出现以下错误

ERROR:  invalid input syntax for type boolean: "aa"
LINE 1: update test set three = 'aa' and four = 'aa' and five = 'aa'...
                                ^
SQL state: 22P02
Character: 25

==================================================== ================

另一个错误::

触发功能：

create or replace function log_changes()
returns trigger as
$$
BEGIN
insert into log_changes(one,old_three , new_three , old_four , new_four , old_five , new_five ) values (new.one, old.old_three , new.new_three , old.old_four , new.new_four , old.old_five , new.new_five);
return new;
END;
$$ language plpgsql

更新后，错误是：

ERROR:  record "old" has no field "old_three"
CONTEXT:  SQL statement "insert into log_changes(one,old_three , new_three , old_four , new_four , old_five , new_five ) values (new.one, old.old_three , new.new_three , old.old_four , new.new_four , old.old_five , new.new_five)"
PL/pgSQL function log_changes() line 3 at SQL statement
SQL state: 42703

标签： postgresqlsql-update

Answer 1

如手册中所述，UPDATE 中的赋值表达式用逗号分隔：

update test 
  set three = 'aa',  --<< comma here, no AND
      four = 'aa', 
      five = 'aa' 
where one = 1 
  and two = 'a';