PostgreSQL 11 preview - 虛拟列(自動根據表達式産生值)

标簽

PostgreSQL , 虛拟列 , GENERATED column

https://github.com/digoal/blog/blob/master/201803/20180322_10.md#%E8%83%8C%E6%99%AF 背景

通過增加虛拟字段，可以讓資料庫根據虛拟列的定義，自動填充值。

與自增，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           

https://github.com/digoal/blog/blob/master/201803/20180322_10.md#%E5%8F%82%E8%80%83 參考

https://commitfest.postgresql.org/17/1443/ https://www.postgresql.org/message-id/flat/[email protected]#[email protected]