Virtual Generated Columns
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/17/ddl-generated-columns.html Description: The documentation for generated columns in PostgreSQL versions prior to 18 mention "virtual" columns, but as far as I can tell, this is not available prior to 18. In v17, this is just in the first paragraph. > A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. 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. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically). PostgreSQL currently implements only stored generated columns.
Re: Inclusion of json in list of standard data types
On 2025-12-06 19:27 +0100, Tom Lane wrote:
> Bob Kline writes:
> > Would there be any reason not to add "json" to the list of data types
> > "specified by SQL" in the "Compatibility" box shown near the beginning
> > of Chapter 8 ("Data Types") of the PostgreSQL documentation?
>
> Yeah, that seems like an oversight. Will fix, thanks for noticing it.
But does Postgres' json type really map to the JSON type defined by
SQL:2023? jsonb appears to be closer to that, although I don't have
access to that particular version of the standard. Peter wrote [1]
about the compatibility with SQL:2023 and noted that the standard maps
more readily to jsonb (see 3rd bullet point in the notes section) and
features T879–T882 still only apply to jsonb.
I don't think we should list json in that compatibility box unless it's
an alias for jsonb (maybe in some future version). Or at least note
that jsonb is the better choice for people looking for standard
compatibility. To me, "compatibility" implies that Postgres also
implements certain features of those standard types rather than just
providing some type that happens to match the name of a standard type
for historical reasons.
[1] https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023
--
Erik Wienhold
Re: Inclusion of json in list of standard data types
Erik Wienhold writes: > But does Postgres' json type really map to the JSON type defined by > SQL:2023? jsonb appears to be closer to that, although I don't have > access to that particular version of the standard. Peter wrote [1] > about the compatibility with SQL:2023 and noted that the standard maps > more readily to jsonb (see 3rd bullet point in the notes section) and > features T879–T882 still only apply to jsonb. Well, there is the weasel wording right at the start of that : The following types (or spellings thereof) are specified by SQL: This note also doesn't get into the rather large semantic gap between what SQL says TIMESTAMP WITH TIME ZONE is and what timestamptz actually does. I think the point of the note is mostly to say that "you have some hope of interoperability with other DBMSes if you use these types". regards, tom lane
Re: Virtual Generated Columns
On 2025-12-05 14:57 +0100, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/17/ddl-generated-columns.html > Description: > > The documentation for generated columns in PostgreSQL versions prior to 18 > mention "virtual" columns, but as far as I can tell, this is not available > prior to 18. > > In v17, this is just in the first paragraph. > > > A generated column is a special column that is always computed from other > columns. Thus, it is for columns what a view is for tables. 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. Thus, a virtual generated column is similar to > a view and a stored generated column is similar to a materialized view > (except that it is always updated automatically). PostgreSQL currently > implements only stored generated columns. The last sentence correctly states that Postgres v17- does not implement virtual columns. The paragraph is meant as a general overview because other RDBMS at that time had already implemented both flavors, e.g. SQLite. -- Erik Wienhold
