Default values in Postgres view with triggered instead-of update

Question

I was planning on having a view with instead of insert trigger. There seems to be a problem with inserting a default value though.

Having the trigger set up as below, the following query fails

INSERT INTO v1.clients (foo) VALUES ('bar')

this returns a

null value in column "is_admin" violates not-null constraint

even though the underlying data.users (not null) table has a default value set.

What I'd say happens is the view translates all missing values to null and the instead of is applied, trying to insert null to a not null default false column.

Can I somehow set up the trigger to instead of trying to insert null to insert the default value? Having coalesce(NEW.is_admin, default) in the relevant insert in the trigger is a syntax error. I would rather not duplicate the default value manually in trigger.

Is this supported in postgres? What would be the best approach to split a view of two tables to those tables, while allowing default values?

definitions:

CREATE OR REPLACE VIEW v1.clients AS
    SELECT
        c.id, c.foo,
        u.id user_id, u.is_admin
    FROM data.clients c
    INNER JOIN data.users u ON u.client_id = c.id;

CREATE FUNCTION data.separate_client_user_data()
RETURNS TRIGGER AS $$
DECLARE
    client_id clients.id%TYPE;
BEGIN
    INSERT INTO data.clients (foo) VALUES (NEW.foo) RETURNING id INTO client_id;
    INSERT INTO data.users (client_id, is_admin)
        VALUES (client_id, NEW.is_admin);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER user_data_trigger
INSTEAD OF INSERT ON v1.clients
FOR EACH ROW EXECUTE PROCEDURE data.separate_client_user_data();

Answer 1

Late to the game, I know, but I have extra insight I want to share.

Note that, as Patrick said, the NEW variable that the TRIGGER PROCEDURE uses contains all the fields - with NULLs added for any fields that were not originally part of your INSERT statement.

As you pointed out, the underlying table has a default value for the is_admin field, and unfortunately that default value doesn't get a chance to be seen, because the NEW variable already has NEW.is_admin = NULL.

However, I want to point out a different solution from Patrick's. It's true that you can hand-code the IF checks (essentially re-implementing the default value logic!) in your plpgsql function. But this may feel non-optimal from a DRY perspective. Here was the key for me when I had your problem:

TL/DR

The default values of the view itself are honored when building the NEW variable. Therefore if you do ALTER TABLE <view_name> ALTER is_admin SET DEFAULT false it should cause false to be passed in to NEW.is_admin, instead of NULL.

Hope that helps!