Understanding the GENERATED ALWAYS Column Option in PostgreSQL

Understanding the GENERATED ALWAYS Column Option in PostgreSQL

The GENERATED ALWAYS column option in PostgreSQL functions similarly to a view for a table, allowing for on-the-fly calculation of the column's content. This feature is useful for generating computed columns based on expressions.

The syntax is straightforward:

<column_name> <datatype> GENERATED ALWAYS AS (expression) STORED

You define a column name and its datatype, then use the GENERATED ALWAYS AS option to specify the expression PostgreSQL should use to generate the column's content. Here’s a geospatial example:

geom geometry(point, 2154)
    GENERATED ALWAYS AS
      (ST_Transform(ST_Point(longitude, latitude, 4326), 2154)) STORED

In this example, a column named geom is created with the datatype geometry, representing a point with a projection number 2154 (French projection). The GENERATED ALWAYS AS option specifies that this point is generated from two other columns, longitude and latitude, initially in the "GPS" projection (SRID 4326), and then reprojected to the French projection using ST_Transform.

Here’s another example, tailored for a business scenario:

totalPrice numeric GENERATED ALWAYS AS (unitPrice * quantity) STORED

In this case, the totalPrice column is generated based on the unitPrice and quantity columns, calculating the total price of items by multiplying the unit price by the quantity.

According to PostgreSQL documentation, there are specific rules for using the GENERATED ALWAYS option:

• The generation expression can reference other columns in the table but not other generated columns.
• Functions and operators used in the expression must be immutable.
• References to other tables are not allowed.

The keyword STORED is essential, indicating that the column's value is computed on write and stored on disk. This ensures that the generated values are persistent and do not need to be recalculated on every read, enhancing performance.

By using the GENERATED ALWAYS option, you can streamline calculations and maintain consistency within your tables, making it a powerful tool for database management in PostgreSQL.