codekingpro/portable-devtools

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 Vsnapshot" /><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 id="docContent" class="container-fluid col-10"><div 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 16.3 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 /></div><div class="sect1" id="DDL-GENERATED-COLUMNS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.3. Generated Columns <a href="#DDL-GENERATED-COLUMNS" class="id_link">#</a></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 other4   columns.  Thus, it is for columns what a view is for tables.  There are two5   kinds of generated columns: stored and virtual.  A stored generated column6   is computed when it is written (inserted or updated) and occupies storage7   as if it were a normal column.  A virtual generated column occupies no8   storage and is computed when it is read.  Thus, a virtual generated column9   is similar to a view and a stored generated column is similar to a10   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 ALWAYS14   AS</code> clause in <code class="command">CREATE TABLE</code>, for example:15</p><pre class="programlisting">16CREATE 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 the23   stored kind of generated column.  See <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> for24   more details.25  </p><p>26   A generated column cannot be written to directly.  In27   <code class="command">INSERT</code> or <code class="command">UPDATE</code> commands, a value28   cannot be specified for a generated column, but the keyword29   <code class="literal">DEFAULT</code> may be specified.30  </p><p>31   Consider the differences between a column with a default and a generated32   column.  The column default is evaluated once when the row is first33   inserted if no other value was provided; a generated column is updated34   whenever the row changes and cannot be overridden.  A column default may35   not refer to other columns of the table; a generation expression would36   normally do so.  A column default can use volatile functions, for example37   <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 and41   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 cannot45      use subqueries or reference anything other than the current row in any46      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, except51      <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 and partitioning:</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, its child column must also60        be a generated column; however, the child column can have a61        different generation expression.  The generation expression that is62        actually applied during insert or update of a row is the one63        associated with the table that the row is physically in.64        (This is unlike the behavior for column defaults: for those, the65        default value associated with the table named in the query applies.)66       </p></li><li class="listitem"><p>67        If a parent column is not a generated column, its child column must68        not be generated either.69       </p></li><li class="listitem"><p>70        For inherited tables, if you write a child column definition without71        any <code class="literal">GENERATED</code> clause in <code class="command">CREATE TABLE72        ... INHERITS</code>, then its <code class="literal">GENERATED</code> clause73        will automatically be copied from the parent.  <code class="command">ALTER TABLE74        ... INHERIT</code> will insist that parent and child columns75        already match as to generation status, but it will not require their76        generation expressions to match.77       </p></li><li class="listitem"><p>78        Similarly for partitioned tables, if you write a child column79        definition without any <code class="literal">GENERATED</code> clause80        in <code class="command">CREATE TABLE ... PARTITION OF</code>, then81        its <code class="literal">GENERATED</code> clause will automatically be copied82        from the parent.  <code class="command">ALTER TABLE ... ATTACH PARTITION</code>83        will insist that parent and child columns already match as to84        generation status, but it will not require their generation85        expressions to match.86       </p></li><li class="listitem"><p>87        In case of multiple inheritance, if one parent column is a generated88        column, then all parent columns must be generated columns.  If they89        do not all have the same generation expression, then the desired90        expression for the child must be specified explicitly.91       </p></li></ul></div></li></ul></div><p>92  </p><p>93   Additional considerations apply to the use of generated columns.94   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>95      Generated columns maintain access privileges separately from their96      underlying base columns.  So, it is possible to arrange it so that a97      particular role can read from a generated column but not from the98      underlying base columns.99     </p></li><li class="listitem"><p>100      Generated columns are, conceptually, updated after101      <code class="literal">BEFORE</code> triggers have run.  Therefore, changes made to102      base columns in a <code class="literal">BEFORE</code> trigger will be reflected in103      generated columns.  But conversely, it is not allowed to access104      generated columns in <code class="literal">BEFORE</code> triggers.105     </p></li></ul></div><p>106  </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" title="5.2. Default Values">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-constraints.html" title="5.4. Constraints">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" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.4. Constraints</td></tr></table></div></body></html>