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

by

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.

More Related Contents:

  1. Calculating and saving space in PostgreSQL
  2. INSERT with dynamic table name in trigger function
  3. Store common query as column?
  4. Passing user id to PostgreSQL triggers
  5. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  6. Making sense of Postgres row sizes
  7. Do nullable columns occupy additional space in PostgreSQL?
  8. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  9. Permission denied for relation
  10. CONSTRAINT to check values from a remotely related table (via join etc.)
  11. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  12. Grant all on a specific schema in the db to a group role in PostgreSQL
  13. Refresh a materialized view automatically using a rule or notify
  14. Does a Postgres UNIQUE constraint imply an index?
  15. How to make a select with array contains value clause in psql
  16. Return SETOF rows from PostgreSQL function
  17. Postgresql insert trigger to set value
  18. ERROR: unterminated quoted string at or near
  19. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  20. Select from a table variable
  21. Is there a way to disable updates/deletes but still allow triggers to perform them?
  22. Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy
  23. How do I temporarily disable triggers in PostgreSQL?
  24. How to close idle connections in PostgreSQL automatically?
  25. Is it possible to have autoincrement number without it being an ID?
  26. function returns multiple columns as a single column instead of multiple columns
  27. Dynamic pivot query using PostgreSQL 9.3
  28. Find Parent Recursively using Query
  29. Hashing a String to a Numeric Value in PostgreSQL
  30. Selecting rows ordered by some column and distinct on another

Leave a Comment