这是不可能简单的一个 DEFAULT 值，如手册明确说明：

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

该值是任何无变量的表达式（子查询和
不允许当前表中其他列的交叉引用） p>

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 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, you could use a lookup table:
SELECT INTO NEW.b  t.b
FROM   def_tbl t
WHERE  t.a = NEW.a;
*/

RETURN NEW;

END
$func$ LANGUAGE plpgsql;

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();

为了使其更有效我使用 WHEN 子句（自Postgres 9.0起可用）与触发器定义。这样触发功能只有在实际使用时才会执行。我假设在这里，我们可以让 b IS NULL slide if a IS NULL 。

To make this more effective I use a WHEN clause (available since Postgres 9.0) with the trigger definition. This way the trigger function is only executed, when it's actually useful. I am assuming here, we can let b IS NULL slide if a IS NULL.

与 DEFAULT 值相似但略有不同的方式。

With一个默认值，您可以显式地插入 NULL 来覆盖默认值。这不可能在这里， NULL 替换为 b 中的值。

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 is replaced with the value from b.