Return to Answer

This is not a very good idea, and there is no to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this..

SELECT * ON table WHERE ST_Area(geom) > x;

Will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

Shy of that, if you really want to store the computed column on the row you're trigger needs to be BEFORE INSERT OR UPDATE. I really wish returning non-null in AFTER would generate a warning, but it's in the docs..

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.

This is not a very good idea, and there is no need to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this:

SELECT * ON table WHERE ST_Area(geom) > x;

will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

Shy of that, if you really want to store the computed column on the row your trigger needs to be BEFORE INSERT OR UPDATE. I really wish returning non-null in AFTER would generate a warning, but it's in the docs (emphasis added):

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.

This is not a very good idea, and there is no to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this..

SELECT * ON table WHERE ST_Area(geom) > x;

Will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

Shy of that, if you really want to store the computed column on the row you're trigger needs to be BEFORE INSERT OR UPDATE. I really wish returning non-null in AFTER would generate a warning, but it's in the docs..

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.

This is not a very good idea, and there is no to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this..

SELECT * ON table WHERE ST_Area(geom) > x;

Will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

Shy of that, if you really want to store the computed column on the row you're trigger needs to be BEFORE INSERT OR UPDATE. I really wish returning non-null in AFTER would generate a warning, but it's in the docs..

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.

This is not a very good idea, and there is no to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this..

SELECT * ON table WHERE ST_Area(geom) > x;

Will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

This is not a very good idea, and there is no to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this..

SELECT * ON table WHERE ST_Area(geom) > x;

Will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

Shy of that, if you really want to store the computed column on the row you're trigger needs to be BEFORE INSERT OR UPDATE. I really wish returning non-null in AFTER would generate a warning, but it's in the docs..

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.