How to register trigger in PostgreSQL?

I have trigger and procedure. I want to be sure that string saved to database will have no ‘-‘ characters.

After executing

UPDATE interesant SET interesant_nip = '555-555-5555' 

I get error

value is too long for varchar(10).

This suggests that ‘-‘ characters were not removed before insert-update.

Why is my trigger not being executed?

CREATE FUNCTION normalize_nip() RETURNS TRIGGER AS $$
BEGIN
    -- replace '' to NULL
    IF (NEW.interesant_nip LIKE '') THEN
        NEW.interesant_nip := NULL;
        RETURN NEW;
    END IF;

    -- remove '-' from string
    NEW.interesant_nip := REPLACE(NEW.interesant_nip, '-', '');

    RETURN NEW;
END; $$ LANGUAGE plpgsql;"

CREATE TRIGGER interesant_nip_normalize BEFORE INSERT OR UPDATE ON public.interesant FOR EACH ROW EXECUTE PROCEDURE normalize_nip()

Answer

The updated or inserted value is always treated as type varchar(10) – before and after the trigger function. So, you cannot handle the value because the input type does not fit the value even if the trigger function converts it into a valid one.

It works if you have an unbounded text type. There you can see that the trigger is executed:

demo:db<>fiddle

---

So, the only chance to handle this, is, to normalize it before inserting or updating:

demo:db<>fiddle

INSERT INTO interesant VALUES (normalize_nip('555-555-5555'));

UPDATE interesant SET interesant_nip = normalize_nip('555-555-5555')