Complex default expression when adding new column-CodePudding

I want to alter existing PostgreSQL (14) table by adding a new column. There is no need for it to be NULL, so I need to provide a default value for existing columns. However, this default value depends on another column, so I'd like to use complex expression (namely, IF-ELSE), like so:

ALTER TABLE products
    ADD COLUMN IF NOT EXISTS description TEXT NOT NULL DEFAULT (
        IF products.id < 100 THEN
            'BLUE'
        ELSE
            'YELLOW'
        END IF
    )
;

I've tried the code above and got the following insightful (no) error message:

error: syntax error at or near "products"

I've also tried this:

ALTER TABLE products
    ADD COLUMN IF NOT EXISTS description TEXT NOT NULL DEFAULT (
        CASE
            WHEN products.id < 100 THEN 'BLUE'
            ELSE                        'YELLOW'
        END
    )
;

-- error: cannot use column reference in DEFAULT expression

Is it possible? If yes, then how? If not, should I use a dummy default value and follow ALTER TABLE with an immediate UPDATE statement?

CodePudding user response：

You cannot refer to other columns in the DEFAULT clause. Besides, SQL knows no IF clause.

You will have to use a BEFORE INSERT trigger:

CREATE FUNCTION set_desc() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.description :=
      coalesce(
         NEW.description,
         CASE WHEN NEW.id < 100 THEN 'BLUE'
              ELSE 'YELLOW'
         END
      );
   RETURN NEW;
END;$$;

CREATE TRIGGER set_desc
   BEFORE INSERT ON products FOR EACH ROW
   EXECUTE PROCEDURE set_desc();

CodePudding user response：

generated columns can easily solve your problem. You don't need update. It will automatically update. The only issue is that you cannot update the generated columns directly. You need to update the base column, in your case is id to update generated columns.

alter table products   
ADD COLUMN description text 
GENERATED ALWAYS AS (case when id < 100 then 'blue' else 'yellow' end ) STORED;

and you can also manage column accesss.

Generated columns maintain access privileges separately from their underlying base columns. So, it is possible to arrange it so that a particular role can read from a generated column but not from the underlying base columns.