record "new" has no field "cure" postgreSQL

Question

so here's the thing,I have two tables: apointments(with a single p) and medical_folder and i get this

ERROR: record "new" has no field "cure" CONTEXT: SQL statement "insert into medical_folder(id,"patient_AMKA",cure,drug_id) values(new.id,new."patient_AMKA",new.cure,new.drug_id)" PL/pgSQL function new_medical() line 3 at SQL statement

create trigger example_trigger after insert on apointments 
for each row execute procedure new_medical();

create or replace function new_medical()
returns trigger as $$
begin 
if apointments.diagnosis is not null then
insert into medical_folder(id,"patient_AMKA",cure,drug_id)
values(new.id,new."patient_AMKA",new.cure,new.drug_id);
return new;
end if;
end;
$$ language plpgsql;


insert into apointments(id,time,"patient_AMKA","doctor_AMKA",diagnosis)
values('30000','2017-05-24 0 
07:42:15','4017954515276','6304745877947815701','M3504');

I have checked multiple times and all of my tables and columns are existing Please help!Thank you!

Table structures are:

create table medical_folder (
  id      bigInt,
  patient bigInt,
  cure    text,
  drug_id bigInt);

create table apointments (
  id      bigint,
  time    timestamp without time zone,
  "patient_AMKA" bigInt,
  "doctor_AMKA"  bigInt);

Answer 1

I was facing the same issue. Change:

values(new.id,new."patient_AMKA",new.cure,new.drug_id);

to:

values(new.id,new."patient_AMKA",new."cure",new."drug_id");

Answer 2

This error means the table apointments (with 1 p) doesn't have a field named cure. The trigger occurs when inserting an apointment, so "new" is an apointment row. Maybe it is part of the diagnosis object?

The values for the second table are not available in the "new" row. You need a way to get and insert them, and using a trigger is not the easiest/clean way to go. You can have your application do two inserts, one by table, and wrap them in a transaction to ensure they are both committed/rolled back. Another option, which lets you better enforce the data integrity, is to create a stored procedure that takes the values to be inserted in both tables and do the two inserts. You can go as far as forbidding user to write to the tables, effectively leaving the stored procedure the only way to insert the data.