标签
PostgreSQL , 虚拟列 , GENERATED column
背景
通过增加虚拟字段，可以让数据库根据虚拟列的定义，自动填充值。
与自增，DEFAULT不同的是，虚拟列中可以从其他列的内容产生。
例如
CREATE TABLE t1 (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm * 2.54)
);
PostgreSQL 11中，有望提交这个PATCH，使用前请详细参考文档说明。
Here is another attempt to implement generated columns. This is a
well-known SQL-standard feature, also available for instance in DB2,
MySQL, Oracle. A quick example:
CREATE TABLE t1 (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm * 2.54)
);
(This is not related to the recent identity columns feature, other than
the similar syntax and some overlap internally.)
In previous discussions, it has often been a source of confusion whether
these generated columns are supposed to be computed on insert/update and
stored, or computed when read. The SQL standard is not explicit, but
appears to lean toward stored. DB2 stores. Oracle computes on read.
MySQL supports both. So I target implementing both. This makes sense:
Both regular views and materialized views have their uses, too. For the
syntax, I use the MySQL/Oracle syntax of appending [VIRTUAL|STORED]. In
this patch, only VIRTUAL is fully implemented. I also have STORED kind
of working, but it wasn't fully baked, so I haven't included it here.
Known bugs:
- pg_dump produces a warning about a dependency loop when dumping these.
Will need to be fixed at some point, but it doesn't prevent anything
from working right now.
Open design issues:
- COPY behavior: Currently, generated columns are automatically omitted
if there is no column list, and prohibited if specified explicitly.
When stored generated columns are implemented, they could be copied out.
Some user options might be possible here.
- Catalog storage: I store the generation expression in pg_attrdef, like
a default. For the most part, this works well. It is not clear,
however, what pg_attribute.atthasdef should say. Half the code thinks
that atthasdef means "there is something in pg_attrdef", the other half
thinks "column has a DEFAULT expression". Currently, I'm going with the
former interpretation, because that is wired in quite deeply and things
start to crash if you violate it, but then code that wants to know
whether a column has a traditional DEFAULT expression needs to check
atthasdef && !attgenerated or something like that.
Missing/future functionality:
- STORED variant
- various ALTER TABLE variants
- index support (and related constraint support)
These can be added later once the basics are nailed down.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
