Unable to update values in postgresql

Question

I am using triggers if any update on my table - 'test'. I have created trigger as mentioned below.

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';

After I am executing update query, I am getting following error

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

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

Another Error ::

Trigger Function :

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

After update, error is :

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

user330315 · Accepted Answer · 2021-03-16 08:36:22Z

1

As documented in the manual the assignment expressions in an UPDATE are separated by commas:

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

answered Mar 16, 2021 at 8:36

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

R_S_C Over a year ago

Thanks It worked but gave another error, I'll update this to show the new error.

user330315 Over a year ago

That is a different question, but the error message is clear: your table test has no column named old_three

Collectives™ on Stack Overflow

Unable to update values in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related