Question 89429 in PostgreSQL Question posted in
The official documentation can be found here.

"ERROR: generation expression is not immutable" why is the alter table for new generated column is immutable? – Postgresql

Why is the below code not valid. I have only used immutable columns.
Below code is supposed to add a new column to the users table. There are few conditions that needs to meet for the is_valid column to be true else it will be false. The email and legacy_password columns must not be null or empty. And expiry_date must not be passed.

ALTER TABLE IF EXISTS vnext.users
ADD COLUMN is_valid boolean GENERATED ALWAYS AS
 ((
    Case when (((trim(coalesce(email,'')) <> '') IS NOT False) 
    AND ((trim(coalesce(legacy_password,'')) <> '') IS NOT False)
    AND (Case WHEN expiry_date is null Then True ELSE date_trunc('day', 
    expiry_date)>date_trunc('day', current_timestamp) END)) THEN TRUE ELSE FALSE END
  )) STORED;
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    In this case, the use of the current_timestamp in the query made it mutable, and got the above error. Removing the current_timestamp should work for now.

    Not sure if there's a way to check if the expiry_date is expired without being mutable.

    ALTER TABLE IF EXISTS vnext.users
ADD COLUMN is_valid boolean GENERATED ALWAYS AS
 ((
    Case when (((trim(coalesce(email,'')) <> '') IS NOT False) 
    AND ((trim(coalesce(legacy_password,'')) <> '') IS NOT False))
    THEN TRUE ELSE FALSE END
  )) STORED;

  2. 0

    current_timestamp is obviously not immutable.

    Also, date_trunc when the 2nd parameter is a timestamptz is only stable, because the result it gives depends on your session’s timezone setting.

    Login or Signup to reply.
  3. 0

    One way you can overcome the problem of current_timestamp not being IMMUTABLE is to use a BEFORE INSERT OR UPDATE trigger instead of a generated column. The trigger function can simply assign the value to the boolean column, and you are not restricted to IMMUTABLE expressions.

    The difference is that the trigger only runs at a certain point in time, while a generated column guarantees that the generated value will always be equal to the result of the generation expression. Hence the requirement for immutability.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search