How to make a PostgreSQL constraint only apply to a new value

Question

I'm new to PostgreSQL and really loving how constraints work with row level security, but I'm confused how to make them do what I want them to.

I have a column and I want add a constraint that creates a minimum length for a text column, this check works for that:

(length((column_name):: text) > 6)

BUT, it also then prevents users updating any rows where column_name is already under 6 characters.

I want to make it so they can't change that value TO that, but can still update a row where that is already happening, so they can change it as needed according to my new policy.

Is this possible?

No. If you want to do that you will need a trigger that takes different actions depending on whether it is an `INSERT` or an `UPDATE`. — Adrian Klaver, Sep 13 '21 at 22:10

Erwin Brandstetter · Answer 1 · 2021-09-14T01:06:05.563

BUT, it also then prevents users updating any rows where column_name is already under 6 characters.

Well, no. When you try to add that CHECK constraint, all existing rows are checked, and an exception is raised if any violation is found.
You would have to make it NOT VALID. Then yes.

You really need a trigger on INSERT or UPDATE that checks new values. Not as cheap and not as bullet-rpoof, but still pretty solid. Like:

CREATE OR REPLACE FUNCTION trg_col_min_len6()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF TG_OP = 'UPDATE'
   AND OLD.column_name IS NOT DISTINCT FROM NEW.column_name THEN
      -- do nothing
   ELSE
      RAISE EXCEPTION 'New value for column "note" must have at least 6 characters.';
   END IF;
  
   RETURN NEW;
END
$func$;

-- trigger
CREATE TRIGGER tbl1_column_name_min_len6
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW
WHEN (length(NEW.column_name) < 7)
EXECUTE FUNCTION trg_col_min_len6();

db<>fiddle here

It should be most efficient to check in a WHEN condition to the trigger directly. Then the trigger function is only ever called for short values and can be super simple.
See:

OP says "*user can still update a row where that is already happening*". I would interpret that to check `WHEN (OLD.column_name IS DISTINCT FROM NEW.column_name AND length(NEW.column_name) < 7)` — Bergi, Sep 14 '21 at 00:27
@Bergi: I agree, updates on rows with short values should be allowed - as long as the value itself is not updated. Thanks for pointing out. But it needs a different implementation because there is no `OLD` for inserts. — Erwin Brandstetter, Sep 14 '21 at 01:08

score 2 · Answer 2 · answered Sep 14 '21 at 18:59

You can create separate triggers for Insert and Update letting each completely define when it should fired. If completely different logic is required for the DML action this technique allows writing dedicated trigger functions. In this case that is not required the trigger function reduces to raise exception .... See Demo

-- Single trigger function for both Insert and Delete
create or replace function trg_col_min_len6()
  returns trigger
  language plpgsql 
as $$
begin
   raise exception 'Cannot % val = ''%''. Must have at least 6 characters.'
                 , tg_op, new.val;
   return null;
end;
$$;

-- trigger before insert 
create trigger tbl_val_min_len6_bir
    before insert 
        on tbl
       for each row
      when (length(new.val) < 6)
      execute function trg_col_min_len6();

-- trugger before update 
create trigger tbl_val_min_len6_bur
    before update
        on tbl
       for each row    
      when (    length(new.val) < 6
            and new.val is distinct from old.val 
           ) 
     execute function trg_col_min_len6();

Was about to add that as alternative, but you already provided it. I agree with everything. — Erwin Brandstetter, Sep 16 '21 at 00:19
Hey thanks for these different options. Trying to get my head around them. Does this version solve the issue from the one above by letting users still update a row with a short value, so long as they haven't changed that value? — Jonathan Plackett, Oct 01 '21 at 11:00

How to make a PostgreSQL constraint only apply to a new value

2 Answers2