Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Watch 1
Star 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/ddl-generated-columns.html

85 lines
7.2KB
Raw Permalink Blame History 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.3. Generated Columns</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="ddl-default.html" title="5.2. Default Values" /><link rel="next" href="ddl-constraints.html" title="5.4. Constraints" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.3. Generated Columns</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-default.html" title="5.2. Default Values">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-constraints.html" title="5.4. Constraints">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="DDL-GENERATED-COLUMNS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.3. Generated Columns</h2></div></div></div><a id="id-1.5.4.5.2" class="indexterm"></a><p>

  3.    A generated column is a special column that is always computed from other

  4.    columns.  Thus, it is for columns what a view is for tables.  There are two

  5.    kinds of generated columns: stored and virtual.  A stored generated column

  6.    is computed when it is written (inserted or updated) and occupies storage

  7.    as if it were a normal column.  A virtual generated column occupies no

  8.    storage and is computed when it is read.  Thus, a virtual generated column

  9.    is similar to a view and a stored generated column is similar to a

  10.    materialized view (except that it is always updated automatically).

  11.    PostgreSQL currently implements only stored generated columns.

  12.   </p><p>

  13.    To create a generated column, use the <code class="literal">GENERATED ALWAYS

  14.    AS</code> clause in <code class="command">CREATE TABLE</code>, for example:

  15. </p><pre class="programlisting">

  16. CREATE TABLE people (

  17.     ...,

  18.     height_cm numeric,

  19.     height_in numeric <span class="emphasis"><strong>GENERATED ALWAYS AS (height_cm / 2.54) STORED</strong></span>

  20. );

  21. </pre><p>

  22.    The keyword <code class="literal">STORED</code> must be specified to choose the

  23.    stored kind of generated column.  See <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> for

  24.    more details.

  25.   </p><p>

  26.    A generated column cannot be written to directly.  In

  27.    <code class="command">INSERT</code> or <code class="command">UPDATE</code> commands, a value

  28.    cannot be specified for a generated column, but the keyword

  29.    <code class="literal">DEFAULT</code> may be specified.

  30.   </p><p>

  31.    Consider the differences between a column with a default and a generated

  32.    column.  The column default is evaluated once when the row is first

  33.    inserted if no other value was provided; a generated column is updated

  34.    whenever the row changes and cannot be overridden.  A column default may

  35.    not refer to other columns of the table; a generation expression would

  36.    normally do so.  A column default can use volatile functions, for example

  37.    <code class="literal">random()</code> or functions referring to the current time;

  38.    this is not allowed for generated columns.

  39.   </p><p>

  40.    Several restrictions apply to the definition of generated columns and

  41.    tables involving generated columns:

  42. 

  43.    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  44.       The generation expression can only use immutable functions and cannot

  45.       use subqueries or reference anything other than the current row in any

  46.       way.

  47.      </p></li><li class="listitem"><p>

  48.       A generation expression cannot reference another generated column.

  49.      </p></li><li class="listitem"><p>

  50.       A generation expression cannot reference a system column, except

  51.       <code class="varname">tableoid</code>.

  52.      </p></li><li class="listitem"><p>

  53.       A generated column cannot have a column default or an identity definition.

  54.      </p></li><li class="listitem"><p>

  55.       A generated column cannot be part of a partition key.

  56.      </p></li><li class="listitem"><p>

  57.       Foreign tables can have generated columns.  See <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a> for details.

  58.      </p></li><li class="listitem"><p>For inheritance:</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>

  59.         If a parent column is a generated column, a child column must also be

  60.         a generated column using the same expression.  In the definition of

  61.         the child column, leave off the <code class="literal">GENERATED</code> clause,

  62.         as it will be copied from the parent.

  63.        </p></li><li class="listitem"><p>

  64.         In case of multiple inheritance, if one parent column is a generated

  65.         column, then all parent columns must be generated columns and with the

  66.         same expression.

  67.        </p></li><li class="listitem"><p>

  68.         If a parent column is not a generated column, a child column may be

  69.         defined to be a generated column or not.

  70.        </p></li></ul></div></li></ul></div><p>

  71.   </p><p>

  72.    Additional considerations apply to the use of generated columns.

  73.    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  74.       Generated columns maintain access privileges separately from their

  75.       underlying base columns.  So, it is possible to arrange it so that a

  76.       particular role can read from a generated column but not from the

  77.       underlying base columns.

  78.      </p></li><li class="listitem"><p>

  79.       Generated columns are, conceptually, updated after

  80.       <code class="literal">BEFORE</code> triggers have run.  Therefore, changes made to

  81.       base columns in a <code class="literal">BEFORE</code> trigger will be reflected in

  82.       generated columns.  But conversely, it is not allowed to access

  83.       generated columns in <code class="literal">BEFORE</code> triggers.

  84.      </p></li></ul></div><p>

  85.   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-default.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-constraints.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.2. Default Values </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 5.4. Constraints</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1