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

Question

If i have a column say column a of any given values, and i want another column column b to have a default value according to the value of column a

In another words:
if column a = 'peter' then column b default value = 'doctor'.

Epinephrine

Answer 1

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.

Dimaggio

Answer 2

In PostgreSQL 12 or later we can use Generated Columns.
https://www.postgresql.org/docs/12/ddl-generated-columns.html example:

create temp table foo (
  a text,
  b text GENERATED ALWAYS AS (
      case WHEN a = 'telegram' THEN 'im'
           WHEN a = 'proton' THEN 'email'
           WHEN a = 'infinity' THEN 'idea'
           ELSE 'bad idea'
      end) stored
);

--Test time.

insert into foo(a) values ('infinity'); 
insert into foo(a) values ('infinity1');

returns;

   a     |    b
-----------+----------
 infinity1 | bad idea
 infinity  | idea

When you try to insert into foo(b) values ('infinity1') yield Errors.

--ERROR:  cannot insert into column "b" DETAIL:  Column "b" is a generated column.

Selfeducated

Exactly what I was looking for and definitely the preferred solution for PostgreSQL 12 onward. Thanks! – Crossstaff

While useful, this one is not really a DEFAULT because you cannot insert on the column. Trying to change it to GENERATED BY DEFAULT while yield a ERROR: for a generated column, GENERATED ALWAYS must be specified. – Tails