|
1 | | -<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.36 2005/01/08 01:44:05 tgl Exp $ --> |
| 1 | +<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.37 2005/01/09 17:47:30 tgl Exp $ --> |
2 | 2 |
|
3 | 3 | <chapter id="ddl"> |
4 | 4 | <title>Data Definition</title> |
@@ -1279,23 +1279,22 @@ WHERE c.altitude > 500 and c.tableoid = p.oid; |
1279 | 1279 | <programlisting> |
1280 | 1280 | ALTER TABLE products ADD COLUMN description text; |
1281 | 1281 | </programlisting> |
1282 | | - The new columnwill initiallybefilled withnull values in the |
1283 | | -existing rows of the table. |
| 1282 | + The new columnis initially filled withwhatever default |
| 1283 | +value is given (null if you don't specify a <literal>DEFAULT</> clause). |
1284 | 1284 | </para> |
1285 | 1285 |
|
1286 | 1286 | <para> |
1287 | | - You can also definea constraint on the column at the same time, |
| 1287 | + You can also defineconstraints on the column at the same time, |
1288 | 1288 | using the usual syntax: |
1289 | 1289 | <programlisting> |
1290 | 1290 | ALTER TABLE products ADD COLUMN description text CHECK (description <> ''); |
1291 | 1291 | </programlisting> |
1292 | | - A new column cannot have a not-null constraint since the column |
1293 | | - initially has to contain null values. But you can add a not-null |
1294 | | - constraint later. Also, you cannot define a default value on a |
1295 | | - new column. According to the SQL standard, this would have to |
1296 | | - fill the new columns in the existing rows with the default value, |
1297 | | - which is not implemented yet. But you can adjust the column |
1298 | | - default later on. |
| 1292 | + In fact all the options that can be applied to a column description |
| 1293 | + in <command>CREATE TABLE</> can be used here. Keep in mind however |
| 1294 | + that the default value must satisfy the given constraints, or the |
| 1295 | + <literal>ADD</> will fail. Alternatively, you can add |
| 1296 | + constraints later (see below) after you've filled in the new column |
| 1297 | + correctly. |
1299 | 1298 | </para> |
1300 | 1299 | </sect2> |
1301 | 1300 |
|
@@ -1390,12 +1389,17 @@ ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; |
1390 | 1389 | <programlisting> |
1391 | 1390 | ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; |
1392 | 1391 | </programlisting> |
| 1392 | + Note that this doesn't affect any existing rows in the table, it |
| 1393 | + just changes the default for future <command>INSERT</> commands. |
| 1394 | + </para> |
| 1395 | + |
| 1396 | + <para> |
1393 | 1397 | To remove any default value, use |
1394 | 1398 | <programlisting> |
1395 | 1399 | ALTER TABLE products ALTER COLUMN price DROP DEFAULT; |
1396 | 1400 | </programlisting> |
1397 | | - This is equivalent to setting the default to null, at least in |
1398 | | -<productname>PostgreSQL</>.As a consequence, it is not an error |
| 1401 | + This is equivalent to setting the default to null. |
| 1402 | + As a consequence, it is not an error |
1399 | 1403 | to drop a default where one hadn't been defined, because the |
1400 | 1404 | default is implicitly the null value. |
1401 | 1405 | </para> |
|