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

Solution 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
            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 PROCEDURE trg_foo_b_default();

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.)

Works in a similar, but subtly different fashion from a DEFAULT value.
With a default value, you can explicitly insert NULL to overrule the default. That's not possible here, NULL in b is replaced with the value derived from a.

Solution 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.