Postgres update view with NOT NULL constraints on its base table

Question

#PostgreSQL 10.22

Lets say I have a table and a view of it such as in:

CREATE TABLE item
(
    id integer Not Null,
    name varchar(50) Not Null
);

CREATE OR REPLACE VIEW all_items AS(
SELECT i.id 
FROM item i
)
WITH CHECK OPTION

When I try to insert a tuple to all_items view I get an error because of the 'Not Null' constraint on the base table. Eg:

INSERT INTO all_items
VALUES (999)

ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (999, null).

I tried to use triggers in order for it to work but it didn't:

CREATE OR REPLACE FUNCTION fill_NULL_attributes()
RETURNS trigger AS $$
BEGIN
  IF NEW.name IS NULL THEN
    NEW.name := 'X'; -- fills empty attribute with some value
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql

CREATE TRIGGER all_items_insert_fix
    INSTEAD OF INSERT ON all_items
    FOR EACH ROW
    EXECUTE PROCEDURE fill_NULL_attributes();

This trigger solution didn't work because NEW doesn't have the "name" attribute.

Is there any way of doing this?

Answer 1

Your trigger function would be correct in a BEFORE trigger, since it modifies the row you are about to insert. But that is not correct in an INSTEAD OF trigger: there, you have to perform the INSERT into the base table yourself.