Trying to automatically insert into a table using triggers in POSTGRESQL

Question

I am trying to make a trigger and function that inserts into the table purchases the values which have been inserted into the table customers.

Columns of table customers

1-customer_id serial PK references customer_id in purchases

2-c_name VARCHAR

3-amount DOUBLE PRECISION

Columns of table purchases

1- customer_id serial PK 2- amount DOUBLE PRECISION

The code for the trigger and the function:

CREATE OR REPLACE FUNCTION auto_insert_purchases()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$body$
BEGIN
insert into purchases(customer_id,purchase) values
(NEW.customer_id,NEW.purchase);
END
$body$
CREATE TRIGGER tr_auto_insert_purchases
AFTER INSERT ON customers
EXECUTE PROCEDURE auto_insert_purchases()

As you can see its supposed to take the new row data and insert it into the table but after doing and insertion to customers like this:

insert into customers values(2,'Stewie Griffin',4.99);

I get this error message:

ERROR:  null value in column "customer_id" of relation "purchases" violates not-null 
constraint
DETAIL:  Failing row contains (null, null).
CONTEXT:  SQL statement "insert into purchases(customer_id,purchase) values
(NEW.customer_id,NEW.purchase)"
auto_insert_purchases() PL/pgSQL fonksiyonu, 3. satır, SQL ifadesi içinde
SQL state: 23502

Why does the failing row contain null? Am I using the NEW keyword incorrectly?

Answer 1

CREATE TABLE customers (
    customer_id int4 NULL,
    c_name varchar NULL,
    amount float8 NULL
);


CREATE TABLE purchases (
    customer_id int4 NULL,
    amount float8 NULL
);


CREATE OR REPLACE FUNCTION auto_insert_purchases()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    insert into purchases(customer_id, amount) values
    (NEW.customer_id, NEW.amount);
    return new; 
END;
$function$
;

create trigger tr_auto_insert_purchases 
after insert ON customers
for each row 
    execute procedure auto_insert_purchases();
       

insert into customers(customer_id, c_name, amount) values (2,'Stewie Griffin', 4.99);

select * from purchases;
-- Result:
 
customer_id|amount|
-----------+------+
          2|  4.99|

May be you just forgot to write for each row statement after CREATE TRIGGER tr_auto_insert_purchases AFTER INSERT ON customers