I've given some examples from PHP just to point my development environment. The question is not about PHP, it's purely about PostgreSQL.
PostgreSQL documentation about generated column states that:
There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read.
However, it only shows an example of stored column in the example of that page and not a virtual one. Also it says:
The keyword STORED must be specified to choose the stored kind of generated column. See CREATE TABLE for more details.
...where it links to CREATE TABLE page. In that page, the documentation clearly points the pattern is GENERATED ALWAYS AS (expression) STORED.
GENERATED ALWAYS AS ( generation_expr ) STORED
This clause creates the column as a generated column. The column cannot be written to, and when read the result of the specified expression will be returned.
The keyword STORED is required to signify that the column will be computed on write and will be stored on disk.
The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.
I actually try to implement a virtual field in Laravel (PHP) and this is what I've came up with so far in my migrations:
DB::statement('ALTER TABLE entries ADD COLUMN do_hint BOOLEAN GENERATED ALWAYS AS (hint_hash OR hint_tags OR hint_due_date OR hint_created_at OR hint_updated_at) VIRTUAL');
As you can see in this statement, there are hint_hash, hint_tags and other several boolean columns (named in hint_* glob pattern) in entries table. I'd like to compute do_hint on the fly so that:
And considering stored columns only generate once (when the row is inserted), this is not the behavior I desire. I'd like to have a virtual column so that I can read it on the fly.
I get statement errors from my code.
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "VIRTUAL"
LINE 1: ...hint_due_date OR hint_created_at OR hint_updated_at) VIRTUAL
^ (SQL: ALTER TABLE entries ADD COLUMN do_hint BOOLEAN GENERATED ALWAYS AS (hint_hash OR hint_tags OR hint_due_date OR hint_created_at OR hint_updated_at) VIRTUAL)
It points at VIRTUAL part is the problem. So I thought "Maybe, virtual generated column is the default behavior." so I removed it and tried again, yet it failed again, complaining the type of the generated column must be defined:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at end of input
LINE 1: ...tags OR hint_due_date OR hint_created_at OR hint_updated_at)
^ (SQL: ALTER TABLE entries ADD COLUMN do_hint BOOLEAN GENERATED ALWAYS AS (hint_hash OR hint_tags OR hint_due_date OR hint_created_at OR hint_updated_at))
So, my question is in the title: Does PostgreSQL, at this point in time, only support stored virtual columns? Is there no way to get virtual columns at this point?
I mean, the documentation is confusing me when it says "There are two kinds of generated columns: stored and virtual.". Is it (i) only trying to introduce the concept of virtual and stored generated column here or (ii) advertising a new feature it has on PostgreSQL 12? I mean, if there is no way to create virtual generated columns, they could add a warning saying "Virtual generated columns are not possible at this point. We only support stored ones yet." but there is not such a warning anywhere in the documentation. I'm confused.
Thanks in advance.
The documentation clearly states that virtual generated columns are not supported.
PostgreSQL currently implements only stored generated columns.
This appears just one sentence after the part of the documnetation that you quoted.