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;
3
Answers
In this case, the use of the
current_timestamp
in the query made it mutable, and got the above error. Removing thecurrent_timestamp
should work for now.Not sure if there's a way to check if the
expiry_date
is expired without being mutable.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.
One way you can overcome the problem of
current_timestamp
not beingIMMUTABLE
is to use aBEFORE INSERT OR UPDATE
trigger instead of a generated column. The trigger function can simply assign the value to theboolean
column, and you are not restricted toIMMUTABLE
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.