PostgreSQL – set a default cell value according to another cell value

by

This is not possible with a simple DEFAULT value, as the manual clearly states:

The value is any variable-free expression (subqueries and
cross-references to other columns in the current table are not allowed).

You could use a trigger instead:

CREATE OR REPLACE FUNCTION trg_foo_b_default()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- For just a few constant options, CASE does the job:
   NEW.b := CASE NEW.a
               WHEN 'peter'  THEN 'doctor'
               WHEN 'weirdo' THEN 'shrink'
               WHEN 'django' THEN 'undertaker'
            -- ELSE null default
            END;

   /*
   -- For more, or dynamic options, consider a lookup table:
   SELECT INTO NEW.b  t.b
   FROM   def_tbl t
   WHERE  t.a = NEW.a;
   */

   RETURN NEW;
END
$func$;


CREATE TRIGGER b_default
BEFORE INSERT ON foo
FOR EACH ROW
WHEN (NEW.b IS NULL AND NEW.a IS NOT NULL)
EXECUTE FUNCTION trg_foo_b_default();

For Postgres 10 or older use the EXECUTE PROCEDURE ... instead. See:

  • Trigger function does not exist, but I am pretty sure it does

To make it more efficient use a WHEN clause in the trigger definition (available since Postgres 9.0). This way the trigger function is only executed when it’s actually useful. (Assuming we can let b IS NULL slide if a IS NULL.)

In Postgres 12 or later, a GENERATED column may be the better solution. See jian’s added answer. Note, however, these restrictions in the manual:

The generation expression can refer to other columns in the table, but
not other generated columns. Any functions and operators used must be
immutable. References to other tables are not allowed.

This trigger is subtly different from a DEFAULT value in that null in b is always replaced with the value derived from a, while a DEFAULT is just the default and can be overruled with any explicit input.
A GENERATED column does not allow input to begin with.

Leave a Comment

tech