1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>5.5.Modifying Tables</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="ddl.html" title="Chapter5.Data Definition">
<link rel="prev" href="ddl-system-columns.html" title="5.4.System Columns">
<link rel="next" href="ddl-priv.html" title="5.6.Privileges">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="ddl-alter"></a>5.5.Modifying Tables</h2></div></div></div>
<a name="id576053"></a><p> When you create a table and you realize that you made a mistake, or
the requirements of the application change, then you can drop the
table and create it again. But this is not a convenient option if
the table is already filled with data, or if the table is
referenced by other database objects (for instance a foreign key
constraint). Therefore <span class="productname">PostgreSQL</span>
provides a family of commands to make modifications to existing
tables. Note that this is conceptually distinct from altering
the data contained in the table: here we are interested in altering
the definition, or structure, of the table.
</p>
<p> You can
</p>
<div class="itemizedlist"><ul type="disc" compact>
<li><p>Add columns,</p></li>
<li><p>Remove columns,</p></li>
<li><p>Add constraints,</p></li>
<li><p>Remove constraints,</p></li>
<li><p>Change default values,</p></li>
<li><p>Change column data types,</p></li>
<li><p>Rename columns,</p></li>
<li><p>Rename tables.</p></li>
</ul></div>
<p>
All these actions are performed using the
<a href="sql-altertable.html">ALTER TABLE</a>
command.
</p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576140"></a>5.5.1.Adding a Column</h3></div></div></div>
<a name="id576144"></a><p> To add a column, use a command like this:
</p>
<pre class="programlisting">ALTER TABLE products ADD COLUMN description text;</pre>
<p>
The new column is initially filled with whatever default
value is given (null if you don't specify a <code class="literal">DEFAULT</code> clause).
</p>
<p> You can also define constraints on the column at the same time,
using the usual syntax:
</p>
<pre class="programlisting">ALTER TABLE products ADD COLUMN description text CHECK (description <> '');</pre>
<p>
In fact all the options that can be applied to a column description
in <code class="command">CREATE TABLE</code> can be used here. Keep in mind however
that the default value must satisfy the given constraints, or the
<code class="literal">ADD</code> will fail. Alternatively, you can add
constraints later (see below) after you've filled in the new column
correctly.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576204"></a>5.5.2.Removing a Column</h3></div></div></div>
<a name="id576208"></a><p> To remove a column, use a command like this:
</p>
<pre class="programlisting">ALTER TABLE products DROP COLUMN description;</pre>
<p>
Whatever data was in the column disappears. Table constraints involving
the column are dropped, too. However, if the column is referenced by a
foreign key constraint of another table,
<span class="productname">PostgreSQL</span> will not silently drop that
constraint. You can authorize dropping everything that depends on
the column by adding <code class="literal">CASCADE</code>:
</p>
<pre class="programlisting">ALTER TABLE products DROP COLUMN description CASCADE;</pre>
<p>
See <a href="ddl-depend.html" title="5.11.Dependency Tracking">Section5.11, “Dependency Tracking”</a> for a description of the general
mechanism behind this.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576263"></a>5.5.3.Adding a Constraint</h3></div></div></div>
<a name="id576267"></a><p> To add a constraint, the table constraint syntax is used. For example:
</p>
<pre class="programlisting">ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;</pre>
<p>
To add a not-null constraint, which cannot be written as a table
constraint, use this syntax:
</p>
<pre class="programlisting">ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;</pre>
<p>
</p>
<p> The constraint will be checked immediately, so the table data must
satisfy the constraint before it can be added.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576308"></a>5.5.4.Removing a Constraint</h3></div></div></div>
<a name="id576312"></a><p> To remove a constraint you need to know its name. If you gave it
a name then that's easy. Otherwise the system assigned a
generated name, which you need to find out. The
<span class="application">psql</span> command <code class="literal">\d
<em class="replaceable"><code>tablename</code></em></code> can be helpful
here; other interfaces might also provide a way to inspect table
details. Then the command is:
</p>
<pre class="programlisting">ALTER TABLE products DROP CONSTRAINT some_name;</pre>
<p>
(If you are dealing with a generated constraint name like <code class="literal">$2</code>,
don't forget that you'll need to double-quote it to make it a valid
identifier.)
</p>
<p> As with dropping a column, you need to add <code class="literal">CASCADE</code> if you
want to drop a constraint that something else depends on. An example
is that a foreign key constraint depends on a unique or primary key
constraint on the referenced column(s).
</p>
<p> This works the same for all constraint types except not-null
constraints. To drop a not null constraint use
</p>
<pre class="programlisting">ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;</pre>
<p>
(Recall that not-null constraints do not have names.)
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576387"></a>5.5.5.Changing a Column's Default Value</h3></div></div></div>
<a name="id576391"></a><p> To set a new default for a column, use a command like this:
</p>
<pre class="programlisting">ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;</pre>
<p>
Note that this doesn't affect any existing rows in the table, it
just changes the default for future <code class="command">INSERT</code> commands.
</p>
<p> To remove any default value, use
</p>
<pre class="programlisting">ALTER TABLE products ALTER COLUMN price DROP DEFAULT;</pre>
<p>
This is effectively the same as setting the default to null.
As a consequence, it is not an error
to drop a default where one hadn't been defined, because the
default is implicitly the null value.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576435"></a>5.5.6.Changing a Column's Data Type</h3></div></div></div>
<a name="id576439"></a><p> To convert a column to a different data type, use a command like this:
</p>
<pre class="programlisting">ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);</pre>
<p>
This will succeed only if each existing entry in the column can be
converted to the new type by an implicit cast. If a more complex
conversion is needed, you can add a <code class="literal">USING</code> clause that
specifies how to compute the new values from the old.
</p>
<p> <span class="productname">PostgreSQL</span> will attempt to convert the column's
default value (if any) to the new type, as well as any constraints
that involve the column. But these conversions may fail, or may
produce surprising results. It's often best to drop any constraints
on the column before altering its type, and then add back suitably
modified constraints afterwards.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576485"></a>5.5.7.Renaming a Column</h3></div></div></div>
<a name="id576490"></a><p> To rename a column:
</p>
<pre class="programlisting">ALTER TABLE products RENAME COLUMN product_no TO product_number;</pre>
<p>
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id576509"></a>5.5.8.Renaming a Table</h3></div></div></div>
<a name="id576513"></a><p> To rename a table:
</p>
<pre class="programlisting">ALTER TABLE products RENAME TO items;</pre>
<p>
</p>
</div>
</div></body>
</html>
|
