CODEWITHSUNDEEP
postgresqldatabase-designtriggersdefault-valuepostgresql-9.2
mosid
mosid

Reputation: 1114

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

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

Upvotes: 64

Views: 51470

Answers (2)

jian
jian

Reputation: 4824

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.

Upvotes: 26

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

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:

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.

Upvotes: 84

Related Questions