Tag: column

Waiting for PostgreSQL 18 – Virtual generated columns

On 7th of February 2025, Peter Eisentraut committed patch:

Virtual generated columns
 
This adds a new variant of generated columns that are computed on read
(like a view, unlike the existing stored generated columns, which are
computed on write, like a materialized view).
 
The syntax for the column definition is
 
    ... GENERATED ALWAYS AS (...) VIRTUAL
 
and VIRTUAL is also optional.  VIRTUAL is the default rather than
STORED to match various other SQL products.  (The SQL standard makes
no specification about this, but it also doesn't know about VIRTUAL or
STORED.)  (Also, virtual views are the default, rather than
materialized views.)
 
Virtual generated columns are stored in tuples as null values.  (A
very early version of this patch had the ambition to not store them at
all.  But so much stuff breaks or gets confused if you have tuples
where a column in the middle is completely missing.  This is a
compromise, and it still saves space over being forced to use stored
generated columns.  If we ever find a way to improve this, a bit of
pg_upgrade cleverness could allow for upgrades to a newer scheme.)
 
The capabilities and restrictions of virtual generated columns are
mostly the same as for stored generated columns.  In some cases, this
patch keeps virtual generated columns more restricted than they might
technically need to be, to keep the two kinds consistent.  Some of
that could maybe be relaxed later after separate careful
considerations.
 
Some functionality that is currently not supported, but could possibly
be added as incremental features, some easier than others:
 
- index on or using a virtual column
- hence also no unique constraints on virtual columns
- extended statistics on virtual columns
- foreign-key constraints on virtual columns
- not-null constraints on virtual columns (check constraints are supported)
- ALTER TABLE / DROP EXPRESSION
- virtual column cannot have domain type
- virtual columns are not supported in logical replication
 
The tests in generated_virtual.sql have been copied over from
generated_stored.sql with the keyword replaced.  This way we can make
sure the behavior is mostly aligned, and the differences can be
visible.  Some tests for currently not supported features are
currently commented out.
 
Reviewed-by: Jian He <jian.universality@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Tested-by: Shlok Kyal <shlok.kyal.oss@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org

Continue reading Waiting for PostgreSQL 18 – Virtual generated columns

Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default

On 28th of March 2018, Andrew Dunstan committed patch:

Fast ALTER TABLE ADD COLUMN with a non-NULL default
 
 
Currently adding a column to a table with a non-NULL default results in
a rewrite of the table. For large tables this can be both expensive and
disruptive. This patch removes the need for the rewrite as long as the
default value is not volatile. The default expression is evaluated at
the time of the ALTER TABLE and the result stored in a new column
(attmissingval) in pg_attribute, and a new column (atthasmissing) is set
to true. Any existing row when fetched will be supplied with the
attmissingval. New rows will have the supplied value or the default and
so will never need the attmissingval.
 
Any time the table is rewritten all the atthasmissing and attmissingval
settings for the attributes are cleared, as they are no longer needed.
 
The most visible code change from this is in heap_attisnull, which
acquires a third TupleDesc argument, allowing it to detect a missing
value if there is one. In many cases where it is known that there will
not be any (e.g.  catalog relations) NULL can be passed for this
argument.
 
Andrew Dunstan, heavily modified from an original patch from Serge
Rielau.
Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley.
 
Discussion: https://postgr.es/m/-7002-4c27-59f5-@2ndQuadrant.com

Continue reading Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default

Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN

On 30th of July, Andrew Dunstan committed patch:

Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN
 
Fabrízio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera and
Michael Paquier.

Continue reading Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN

Stupid tricks – hiding value of column in select *

One of the most common questions is “how do I get select * from table, but without one of the column".

Short answer is of course – name your columns, instead of using *. Or use a view.

But I decided to take a look at the problem.

Continue reading Stupid tricks – hiding value of column in select *