Check out the new generated columns feature in Postgres 12
PostgreSQL 12 comes with a new feature called generated columns. Other popular
RDBMSes already support generated columns as “computed columns” or “virtual
columns.” With Postgres 12, you can now use it in PostgreSQL as well. Read on to learn more.
What is a generated column?
A generated column is sort of like a view, but for columns. Here’s a basic
We created a table t with two regular columns called w and h,
and a generated column called area. The value of area is computed at
row creation time, and is persisted onto the disk.
The value of generated columns are recomputed when the row is updated:
Such functionality was earlier usually achieved with triggers, but with
generated columns this becomes much more elegant and cleaner.
A few points you should know about generated columns:
Persistence: Currently, the value of generated columns have to be
persisted, and cannot be computed on the fly at query time. The “STORED”
keyword must be present in the column definition.
The Expression: The expression used to compute the value has to be
immutable, that is, it has to be deterministic. It can depend on other
columns, but not other generated columns, of the table.
Indexes: Generated columns can be used in indexes, but cannot be used as
a partition key for partitioned tables.
Copy and pg_dump: The values of generated columns are omitted in the
output of “pg_dump” and “COPY table” commands, as it is unnecessary. You can
explicitly include them in COPY using COPY (SELECT * FROM t) TO STDOUT
rather than COPY t TO STDOUT.
A Practical Example
Let’s add full text search support to a table using generated columns. Here’s
a table that stores the entire text of all of Shakespeare’s plays:
Here’s how the data looks:
We’ll add a column that will contain the lexemes in the value of “body”.
The function to_tsvector
returns the lexemes we need:
The type of the value returned by to_tsvector is tsvector.
Let’s alter the table to add a generated column:
You can see the change with \d:
And just like that, you can now do full text searches:
If you have a need for pre-computed / “cached” data, especially with a workload
of few writes and lots of reads, generated columns should help simplify your
application / server-side code a lot.
pgDash is a modern, in-depth monitoring solution designed
specifically for PostgreSQL deployments. pgDash shows you information and
metrics about every aspect of your PostgreSQL database server, collected using
the open-source tool pgmetrics.
pgDash provides core reporting and visualization
functionality, including collecting and displaying PostgreSQL information and
providing time-series graphs, detailed reports, alerting, teams and more.
pgDash can monitor replication, locks, queries and more. It can also provide
quick diagnostics and extensive alerts. Learn more here or signup
today for a free trial.