Скачать или смотреть How to Create a Calculated Column in PostgreSQL Using Current Date

Learn how to effectively generate a column in PostgreSQL that calculates the percentage of time elapsed between task start and end dates using the current date.
---
This video is based on the question https://stackoverflow.com/q/65765912/ asked by the user 'user2717639' ( https://stackoverflow.com/u/2717639/ ) and on the answer https://stackoverflow.com/a/65766397/ provided by the user 'bobflux' ( https://stackoverflow.com/u/144450/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Postgres - create a column (alter table) as a calculation of other two columns

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Creating a Calculated Column in PostgreSQL

In database management, particularly with PostgreSQL, you may find yourself needing to generate calculated columns that update dynamically based on current data. A common scenario involves dealing with task management, where you want to track the percentage of time elapsed between a task’s start and end dates. For instance, you might want to calculate how far along a task is in relation to today’s date.

However, you might encounter issues while trying to implement such functionality directly in your ALTER TABLE statements. Let's explore how to correctly set this up to reflect the data you need accurately.

The Problem

Imagine you have a PostgreSQL table named gantt_task that logs the start and end dates of various tasks. You want to create a new column that displays the percentage of time elapsed from the start date to the end date, relative to the current date. An initial attempt at creating this column using standard SQL syntax might look something like this:

[[See Video to Reveal this Text or Code Snippet]]

This attempt, however, fails to account for certain PostgreSQL constraints and specifics about how it handles generated columns.

Understanding Generated Columns in PostgreSQL

PostgreSQL only supports materialized (or stored) generated columns. This means that the value for a generated column is set at the time the row is inserted or updated, not dynamically reflecting current values like CURRENT_DATE. This limitation means that directly trying to calculate the percentage_progress using CURRENT_DATE during the creation of the column will not yield the desired outcome.

Solution: Use a View Instead!

To dynamically calculate the percentage of time elapsed using CURRENT_DATE, the best approach is to create a view. A view allows you to define a virtual table based on a query, which evaluates values at the time of selection rather than at insertion.

Here’s how to create a view that calculates the percentage_progress:

[[See Video to Reveal this Text or Code Snippet]]

Important Considerations

MySQL Syntax: The DATEDIFF function you used is specific to MySQL. In PostgreSQL, you can simply subtract dates to get the difference in days.

Division by Zero: Be cautious about dividing by zero, particularly if the start_date and end_date are the same. You may want to conditionally handle this by setting a minimum value or implementing error handling in your logic.

Control Over 100% Scenario: If your current date surpasses the end date, the percentage could exceed 100%. To manage this, you might adjust your formula to include a limit:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Using PostgreSQL views is a powerful way to create dynamic calculations based on current data like the percentage_progress of tasks. By applying the methods described above, you can effectively keep track of task timelines and progress. This approach allows for greater flexibility and instant reflection of the current state of your data.

Now, you’re ready to apply these concepts to your database and enhance your task management capabilities. Happy querying!