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.
Top comments (0)