Q:

Postgresql BEFORE INSERT trigger not running before constraints are checked

I have a trigger which is trimming some data on insert.  One of the columns only holds 20 characters. If its 21 characters, we trim off the first character. However when I attempt to insert 21 characters, it response with an error that the value is too long.  So it seems like its checking constraints before running my trigger?
Trigger creation:
CREATE TRIGGER my_trigger
  BEFORE INSERT
  ON public.mytable
  FOR EACH ROW
  EXECUTE PROCEDURE public.my_trigger();

The Trigger has some code in it like this:
IF (NEW.mydata IS NOT NULL AND LENGTH(NEW.mydata) > 20 THEN
    NEW.mydata := substring(NEW.mydata from 2);
END IF;

However, when I attempt an insert and I insert 21 characters into it, it fails with:

[Err] ERROR:  value too long for type character varying(20)

A:

Postgres will not check table constraints before running the trigger. However, it guarantees that the types of the fields in NEW match the types of the fields in the table, and so it must check type constraints before giving you a chance to see the data.
You can work around this by removing the size limit from the type, and implementing your length check as a table constraint instead:
ALTER TABLE mytable
  ALTER COLUMN mydata TYPE TEXT,
  ADD CONSTRAINT mydata_length CHECK (length(mydata) <= 20);

Internally, TEXT and VARCHAR(N) are identical in Postgres, so the type change shouldn't have any impact as far as the database is concerned. However, these types are treated very differently by some client libraries, so be wary of differences in performance / behaviour in your application.

