postgresqlautogeneratecolumn

Postgres - create a column (alter table) as a calculation of other two columns

I have a table in Posgres that contains task start and task end dates. It's possible to generate a column in this tale as rate between (current day -start day) /(start day-end day) the column is the % of time elapse. I try in this way but does not work.

ALTER TABLE public.gantt_task
ADD COLUMN 
      percentage_progress 
GENERATED  ALWAYS AS (
(DATEDIFF("day", 
CURRENT_DATE,public.gantt_Tasks.start_date)) / DATEDIFF("day", public.gantt_Tasks.end_date ,public.gantt_Tasks.start_date)) 
STORED
Solution

  • The manual says postgres only supports materialized (ie, stored) generated columns, which means the value is generated when the row is inserted or updated, which means it will use the insert/update date, not the CURRENT_DATE you want.

    So, you need to create a view instead. This allows evaluating CURRENT_DATE at the date of the SELECT, not the INSERT/UPDATE, to generate columns.

    CREATE VIEW foo AS SELECT *, 
  (CURRENT_DATE - public.gantt_Tasks.start_date)
  / (public.gantt_Tasks.end_date-public.gantt_Tasks.start_date)
  AS percentage_progress
FROM public.gantt_task

    Note DATEDIFF is mysql syntax not postgres, and division by zero is not allowed, so if start_date and end_date can be identical then you'll have to modify the expression conditions depending on what you want. Also your expression will go over 100% when CURRENT_DATE is later than end_date. Perhaps something like:

    least( 1.0, (CURRENT_DATE-start_date)/greatest( 1, end_date-start_date)::FLOAT )