Automatically truncate a string before inserting to PostgreSQL

August 25, 2017

This trick doesn’t work with a row of type varchar(3), I don’t know why.

BEGIN;
CREATE TABLE t (
  z text,
  CHECK (length(z) <= 3)
);
CREATE FUNCTION
  f()
  RETURNS trigger
  LANGUAGE plpgsql
  AS '
  BEGIN
    NEW.z := substr(NEW.z, 1, 3);
    RETURN NEW;
  END';
CREATE TRIGGER tr BEFORE INSERT OR UPDATE
  ON t
  FOR EACH ROW
  EXECUTE PROCEDURE f();
INSERT INTO t(z)
  VALUES ('qwerty')
  RETURNING z; -- returns 'qwe'