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.6. Modifying Tables</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-system-columns.html" title="5.5. System Columns" /><link rel="next" href="ddl-priv.html" title="5.7. Privileges" /></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.6. Modifying Tables</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-system-columns.html" title="5.5. System Columns">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-priv.html" title="5.7. Privileges">Next</a></td></tr></table><hr /></div><div class="sect1" id="DDL-ALTER"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.6. Modifying Tables <a href="#DDL-ALTER" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN">5.6.1. Adding a Column</a></span></dt><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-REMOVING-A-COLUMN">5.6.2. Removing a Column</a></span></dt><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-ADDING-A-CONSTRAINT">5.6.3. Adding a Constraint</a></span></dt><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-REMOVING-A-CONSTRAINT">5.6.4. Removing a Constraint</a></span></dt><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-COLUMN-DEFAULT">5.6.5. Changing a Column's Default Value</a></span></dt><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-COLUMN-TYPE">5.6.6. Changing a Column's Data Type</a></span></dt><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-RENAMING-COLUMN">5.6.7. Renaming a Column</a></span></dt><dt><span class="sect2"><a href="ddl-alter.html#DDL-ALTER-RENAMING-TABLE">5.6.8. Renaming a Table</a></span></dt></dl></div><a id="id-1.5.4.8.2" class="indexterm"></a><p>3   When you create a table and you realize that you made a mistake, or4   the requirements of the application change, you can drop the5   table and create it again.  But this is not a convenient option if6   the table is already filled with data, or if the table is7   referenced by other database objects (for instance a foreign key8   constraint).  Therefore <span class="productname">PostgreSQL</span>9   provides a family of commands to make modifications to existing10   tables.  Note that this is conceptually distinct from altering11   the data contained in the table: here we are interested in altering12   the definition, or structure, of the table.13  </p><p>14   You can:15   </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>Add columns</p></li><li class="listitem"><p>Remove columns</p></li><li class="listitem"><p>Add constraints</p></li><li class="listitem"><p>Remove constraints</p></li><li class="listitem"><p>Change default values</p></li><li class="listitem"><p>Change column data types</p></li><li class="listitem"><p>Rename columns</p></li><li class="listitem"><p>Rename tables</p></li></ul></div><p>16 17   All these actions are performed using the18   <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>19   command, whose reference page contains details beyond those given20   here.21  </p><div class="sect2" id="DDL-ALTER-ADDING-A-COLUMN"><div class="titlepage"><div><div><h3 class="title">5.6.1. Adding a Column <a href="#DDL-ALTER-ADDING-A-COLUMN" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.5.2" class="indexterm"></a><p>22    To add a column, use a command like:23</p><pre class="programlisting">24ALTER TABLE products ADD COLUMN description text;25</pre><p>26    The new column is initially filled with whatever default27    value is given (null if you don't specify a <code class="literal">DEFAULT</code> clause).28   </p><div class="tip"><h3 class="title">Tip</h3><p>29     From <span class="productname">PostgreSQL</span> 11, adding a column with30     a constant default value no longer means that each row of the table31     needs to be updated when the <code class="command">ALTER TABLE</code> statement32     is executed. Instead, the default value will be returned the next time33     the row is accessed, and applied when the table is rewritten, making34     the <code class="command">ALTER TABLE</code> very fast even on large tables.35    </p><p>36     However, if the default value is volatile (e.g.,37     <code class="function">clock_timestamp()</code>)38     each row will need to be updated with the value calculated at the time39     <code class="command">ALTER TABLE</code> is executed. To avoid a potentially40     lengthy update operation, particularly if you intend to fill the column41     with mostly nondefault values anyway, it may be preferable to add the42     column with no default, insert the correct values using43     <code class="command">UPDATE</code>, and then add any desired default as described44     below.45    </p></div><p>46    You can also define constraints on the column at the same time,47    using the usual syntax:48</p><pre class="programlisting">49ALTER TABLE products ADD COLUMN description text CHECK (description &lt;&gt; '');50</pre><p>51    In fact all the options that can be applied to a column description52    in <code class="command">CREATE TABLE</code> can be used here.  Keep in mind however53    that the default value must satisfy the given constraints, or the54    <code class="literal">ADD</code> will fail.  Alternatively, you can add55    constraints later (see below) after you've filled in the new column56    correctly.57   </p></div><div class="sect2" id="DDL-ALTER-REMOVING-A-COLUMN"><div class="titlepage"><div><div><h3 class="title">5.6.2. Removing a Column <a href="#DDL-ALTER-REMOVING-A-COLUMN" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.6.2" class="indexterm"></a><p>58    To remove a column, use a command like:59</p><pre class="programlisting">60ALTER TABLE products DROP COLUMN description;61</pre><p>62    Whatever data was in the column disappears.  Table constraints involving63    the column are dropped, too.  However, if the column is referenced by a64    foreign key constraint of another table,65    <span class="productname">PostgreSQL</span> will not silently drop that66    constraint.  You can authorize dropping everything that depends on67    the column by adding <code class="literal">CASCADE</code>:68</p><pre class="programlisting">69ALTER TABLE products DROP COLUMN description CASCADE;70</pre><p>71    See <a class="xref" href="ddl-depend.html" title="5.14. Dependency Tracking">Section 5.14</a> for a description of the general72    mechanism behind this.73   </p></div><div class="sect2" id="DDL-ALTER-ADDING-A-CONSTRAINT"><div class="titlepage"><div><div><h3 class="title">5.6.3. Adding a Constraint <a href="#DDL-ALTER-ADDING-A-CONSTRAINT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.7.2" class="indexterm"></a><p>74    To add a constraint, the table constraint syntax is used.  For example:75</p><pre class="programlisting">76ALTER TABLE products ADD CHECK (name &lt;&gt; '');77ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);78ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;79</pre><p>80    To add a not-null constraint, which cannot be written as a table81    constraint, use this syntax:82</p><pre class="programlisting">83ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;84</pre><p>85   </p><p>86    The constraint will be checked immediately, so the table data must87    satisfy the constraint before it can be added.88   </p></div><div class="sect2" id="DDL-ALTER-REMOVING-A-CONSTRAINT"><div class="titlepage"><div><div><h3 class="title">5.6.4. Removing a Constraint <a href="#DDL-ALTER-REMOVING-A-CONSTRAINT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.8.2" class="indexterm"></a><p>89    To remove a constraint you need to know its name.  If you gave it90    a name then that's easy.  Otherwise the system assigned a91    generated name, which you need to find out.  The92    <span class="application">psql</span> command <code class="literal">\d93    <em class="replaceable"><code>tablename</code></em></code> can be helpful94    here; other interfaces might also provide a way to inspect table95    details.  Then the command is:96</p><pre class="programlisting">97ALTER TABLE products DROP CONSTRAINT some_name;98</pre><p>99    (If you are dealing with a generated constraint name like <code class="literal">$2</code>,100    don't forget that you'll need to double-quote it to make it a valid101    identifier.)102   </p><p>103    As with dropping a column, you need to add <code class="literal">CASCADE</code> if you104    want to drop a constraint that something else depends on.  An example105    is that a foreign key constraint depends on a unique or primary key106    constraint on the referenced column(s).107   </p><p>108    This works the same for all constraint types except not-null109    constraints. To drop a not null constraint use:110</p><pre class="programlisting">111ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;112</pre><p>113    (Recall that not-null constraints do not have names.)114   </p></div><div class="sect2" id="DDL-ALTER-COLUMN-DEFAULT"><div class="titlepage"><div><div><h3 class="title">5.6.5. Changing a Column's Default Value <a href="#DDL-ALTER-COLUMN-DEFAULT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.9.2" class="indexterm"></a><p>115    To set a new default for a column, use a command like:116</p><pre class="programlisting">117ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;118</pre><p>119    Note that this doesn't affect any existing rows in the table, it120    just changes the default for future <code class="command">INSERT</code> commands.121   </p><p>122    To remove any default value, use:123</p><pre class="programlisting">124ALTER TABLE products ALTER COLUMN price DROP DEFAULT;125</pre><p>126    This is effectively the same as setting the default to null.127    As a consequence, it is not an error128    to drop a default where one hadn't been defined, because the129    default is implicitly the null value.130   </p></div><div class="sect2" id="DDL-ALTER-COLUMN-TYPE"><div class="titlepage"><div><div><h3 class="title">5.6.6. Changing a Column's Data Type <a href="#DDL-ALTER-COLUMN-TYPE" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.10.2" class="indexterm"></a><p>131    To convert a column to a different data type, use a command like:132</p><pre class="programlisting">133ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);134</pre><p>135    This will succeed only if each existing entry in the column can be136    converted to the new type by an implicit cast.  If a more complex137    conversion is needed, you can add a <code class="literal">USING</code> clause that138    specifies how to compute the new values from the old.139   </p><p>140    <span class="productname">PostgreSQL</span> will attempt to convert the column's141    default value (if any) to the new type, as well as any constraints142    that involve the column.  But these conversions might fail, or might143    produce surprising results.  It's often best to drop any constraints144    on the column before altering its type, and then add back suitably145    modified constraints afterwards.146   </p></div><div class="sect2" id="DDL-ALTER-RENAMING-COLUMN"><div class="titlepage"><div><div><h3 class="title">5.6.7. Renaming a Column <a href="#DDL-ALTER-RENAMING-COLUMN" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.11.2" class="indexterm"></a><p>147    To rename a column:148</p><pre class="programlisting">149ALTER TABLE products RENAME COLUMN product_no TO product_number;150</pre><p>151   </p></div><div class="sect2" id="DDL-ALTER-RENAMING-TABLE"><div class="titlepage"><div><div><h3 class="title">5.6.8. Renaming a Table <a href="#DDL-ALTER-RENAMING-TABLE" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.8.12.2" class="indexterm"></a><p>152    To rename a table:153</p><pre class="programlisting">154ALTER TABLE products RENAME TO items;155</pre><p>156   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-system-columns.html" title="5.5. System Columns">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-priv.html" title="5.7. Privileges">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.5. System Columns </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.7. Privileges</td></tr></table></div></body></html>