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

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