Postgres - создать столбец (alter table) для вычисления двух других столбцов

#postgresql #autogeneratecolumn

Вопрос:

У меня есть таблица в Posgres, которая содержит даты начала и окончания задачи. В этом отчете можно сгенерировать столбец в качестве показателя между (текущий день — начальный день) / (начальный день-конечный день) столбец — это% прошедшего времени. Я пытаюсь таким образом, но не работает.

 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

Ответ №1:

В руководстве говорится, что postgres поддерживает только материализованные (т. Е. Сохраненные) сгенерированные столбцы, что означает, что значение генерируется при вставке или обновлении строки, что означает, что в нем будет использоваться дата вставки / обновления, а не CURRENT_DATE, которую вы хотите.

Итак, вам нужно создать представление вместо этого. Это позволяет вычислять CURRENT_DATE на дату выбора, а не ВСТАВКИ / ОБНОВЛЕНИЯ, для создания столбцов.

 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

Обратите внимание, что DATEDIFF — это синтаксис mysql, а не postgres, и деление на ноль не допускается, поэтому, если start_date и end_date могут быть идентичными, вам придется изменить условия выражения в зависимости от того, что вы хотите. Также ваше выражение превысит 100%, если CURRENT_DATE будет позже end_date. Возможно, что-то вроде:

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

1. Другой альтернативой было бы создать неизменяемую функцию, содержащую это выражение, чтобы «обмануть» Postgres, разрешив его для сгенерированного столбца. Но представление также не будет работать. В Postgres нет datediff() функции

2. Если функция содержит CURRENT_DATE, то postgres не позволит объявить ее как неизменяемую. И да, DATEDIFF — это синтаксис MySQL…

3. dbfiddle.uk/…

4. Вы правы, я перепутал его со СТАБИЛЬНЫМ. Не меняет того факта, что столбец будет сгенерирован и сохранен с датой вставки, а не с датой выбора…

Ответ №2:

Я не буду писать правильный код SQL. Но вы можете / должны разделить его на две или три задачи:

Добавьте новый столбец, который допускает значение null (это должно быть по умолчанию)
Обновить таблицу
Добавьте ограничения (если требуется)