'Is there a way to create a generated column in postgresql that will store a boolean value from comparing 2 dates?

I am trying to create a new generated column call memberstat which is a boolean that will hold just a 'True' or 'false' if the current date is greater than expiration date.

So far, whenever i create 'memberstat boolean generated always as (case when expiredate < current_date then '0' else '1' end) stored' this generates an ERROR: generation expression is not immutable.

Is there a way around this? sorry I am not that familiar with postgresql

postgresqlgenerated


Solution 1:[1]

As you have seen the function CURRENT_DATE cannot be used is a virtual column definition because it will change without the values in the row changing. A virtual column must be based only on the data in the same row.
The solution is to create a view using either your case statement or a simple comparison operator.

CREATE VIEW expired_or_not AS
SELECT 
  product_id,
  product_name,
  expiredate < current_date AS "expired" 
FROM table_name;

which will return t or f.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions