Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit



SELECT 1


create table tbl( id   integer generated always as identity 
                , val  text not null
                , note text not null
                ) ; 
 
insert into tbl (val, note)
     values ('1234','Short, but I''m here before the 6 character minimun') 
          , ('1234567','I would always be OK');               

select * 
  from tbl;    

CREATE TABLE



INSERT 0 2




id
val
note


1
1234
Short, but I'm here before the 6 character minimun

2
1234567
I would always be OK



SELECT 2


-- 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();

CREATE FUNCTION



CREATE TRIGGER



CREATE TRIGGER


insert into tbl (val, note) 
     values ('123456789', 'OK, I''m good! Yea me.');

INSERT 0 1


insert into tbl (val, note) 
     values ('123','NO, NO too short'); 
    

ERROR:  Cannot INSERT val = '123'. Must have at least 6 characters.
CONTEXT:  PL/pgSQL function trg_col_min_len6() line 3 at RAISE


update tbl 
   set note = 'Short, but still OK. I was here before the 6 character minimun'
 where val = '1234'; 

UPDATE 1


select * 
  from tbl;


id
val
note


2
1234567
I would always be OK

3
123456789
OK, I'm good! Yea me.

1
1234
Short, but still OK. I was here before the 6 character minimun



SELECT 3


update tbl
   set val = '12345'
     , note = 'OOPS, Now I must follow the rule'
 where val = '1234';

ERROR:  Cannot UPDATE val = '12345'. Must have at least 6 characters.
CONTEXT:  PL/pgSQL function trg_col_min_len6() line 3 at RAISE


update tbl
   set val = null
     , note = 'How about all the wat to nothing?? '
 where val = '1234';

ERROR:  null value in column "val" violates not-null constraint
DETAIL:  Failing row contains (1, null, How about all the wat to nothing?? ).

id	val	note
1	1234	Short, but I'm here before the 6 character minimun
2	1234567	I would always be OK

id	val	note
2	1234567	I would always be OK
3	123456789	OK, I'm good! Yea me.
1	1234	Short, but still OK. I was here before the 6 character minimun