How to make a column mandatory in every Insert / Update (not meaning default null)

Following table:

CREATE TABLE kontrolle.negativtext
(
  id serial NOT NULL,
  ausschluss character varying(255) NOT NULL, 
  nur_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  nicht_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  bemerkung character varying(255), 
  last_change_user character varying(3) NOT NULL,
  last_change_timestamp timestamp without time zone,
  hits integer NOT NULL DEFAULT 0,
  is_regex boolean NOT NULL DEFAULT false, 
  check_doc negative_doc_check[] DEFAULT '{CONTENT_TXT_DIRTY, CONTENT_TXT_CLEAN}', 
  CONSTRAINT negativtext_pkey PRIMARY KEY (id)
);

Values of datarecords in this table should be changeable by multiple users. I want to force everyone updating or inserting values in this table to provide a username (we don't need to discuss why this is no foreign key).

Setting the column to NOT NULL DEFAULT NULL will prevent the value to ever be null, which is fine. But when user abc inserted a row and user xyz updates it later, xyz should need to provide a value for column last_change_user, meaning this update statement should throw an error:

Update kontrolle.negativtext set ausschluss = :a, regex : rgx where id = :id

even if there is already a username set, but this should work:

Update kontrolle.negativtext
set ausschluss = :a, regex : rgx, last_change_user = :user
where id = :id

I think a constraint wouldn't work, a trigger before update on should work but I don't have success:

create trigger mandatory_username before update on kontrolle.negativtext
for each row
execute procedure check_mandatory_username();

create or replace function check_mandatory_username()
returns trigger as $bla$
begin
    if NEW.last_change_user is null then 
        raise exception 'no last_change_user provided'
        USING HINT = 'please add your name';
    else return NEW;
    end if;
end;
$bla$ language plpgsql;

This never raises an exception. If I

Your check NEW.last_change_user is null in the trigger will only be true if the UPDATE statement explicitly contains a set last_change_user = NULL, not if the value did not change (then new and old are simply identical).

You can check if the value changed using if new.last_change_user is distinct from old.last_change_user, but you can not check if the value was specified at all, possibly supplying the same value that is already in the column.

Something like Oracle's updating(column_name) function would be needed here, but Postgres does not have that.

So I think the answer to your question is: no, that's not possible.

StackExchange Database Administrators Q#211769, answer score: 5