<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.3 2002/09/05 21:32:23 petere Exp $ -->

(Details about data manipulation commands are in <xref linkend="dml">.)

column list. Column definitions and these constraint

check expression evaluates to true or the null value. Since most

expressions will evaluate to the null value if one operand is null

they will not prevent null values in the constrained columns. To

ensure that a column does not contain null values, the not-null

not-null constraint is functionally equivalent to creating a check

constraint <literal>CHECK (<replaceable>column_name</replaceable>

IS NOT NULL)</literal>, but in

<productname>PostgreSQL</productname> creating an explicit

not-null constraint is more efficient. The drawback is that you

cannot give explicit names to not-null constraints created that

way.

Of course, the number and type of the constrained columns needs to

match the number and type of the referenced columns.

When you create a table and you realize that you made a mistake, or

the requirements of the application changed, 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 <productname>PostgreSQL</productname>

provides a family of commands to make modifications on existing

tables.

<listitem>

<para>Remove a column,</para>

</listitem>

All these actions are performed using the <literal>ALTER

TABLE</literal> command.

<sect2>

<title>Adding a Column</title>

<para>

To add a column, use this command:

<programlisting>

ALTER TABLE products ADD COLUMN description text;

</programlisting>

The new column will initially be filled with null values in the

existing rows of the table.

</para>

<para>

You can also define a constraint on the column at the same time,

using the usual syntax:

<programlisting>

ALTER TABLE products ADD COLUMN description text CHECK (description &lt;&gt; '');

</programlisting>

A new column cannot have a not-null constraint since the column

initially has to contain null values. But you can add a not-null

constraint later. Also, you cannot define a default value on a

new column. According to the SQL standard, this would have to

fill the new columns in the existing rows with the default value,

which is not implemented yet. But you can adjust the column

default later on.

</para>

</sect2>

<sect2>

<title>Removing a Column</title>

<para>

To remove a column, use this command:

<programlisting>

ALTER TABLE products DROP COLUMN description;

</programlisting>

</para>

</sect2>

<sect2>

<title>Adding a Constraint</title>

<para>

To add a constraint, the table constraint syntax is used. For example:

<programlisting>

ALTER TABLE products ADD CHECK (name &lt;&gt; '');

ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);

ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

</programlisting>

To add a not-null constraint, which cannot be written as a table

constraint, use this syntax:

<programlisting>

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

</programlisting>

</para>

<para>

The constraint will be checked immediately, so the table data must

satisfy the constraint before it can be added.

</para>

</sect2>

<sect2>

<title>Removing a Constraint</title>

<para>

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

<application>psql</application> command <literal>\d

<replaceable>tablename</replaceable></literal> can be helpful

here; other interfaces might also provide a way to inspect table

details. Then the command is:

<programlisting>

ALTER TABLE products DROP CONSTRAINT some_name;

</programlisting>

This works the same for all constraint types except not-null

constraints. To drop a not null constraint use

<programlisting>

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

</programlisting>

(Recall that not-null constraints do not have names.)

</para>

</sect2>

<sect2>

<title>Changing the Default</title>

<para>

To set a new default for a column, use a command like this:

<programlisting>

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

</programlisting>

To remove any default value, use

<programlisting>

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

</programlisting>

This is equivalent to setting the default to null, at least in

PostgreSQL. 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.

</para>

</sect2>

<sect2>

<title>Renaming a Column</title>

<para>

To rename a column:

<programlisting>

ALTER TABLE products RENAME COLUMN product_no TO product_number;

</programlisting>

</para>

</sect2>

<sect2>

<title>Renaming a Table</title>

<para>

To rename a table:

<programlisting>

ALTER TABLE products RENAME TO items;

</programlisting>

</para>

</sect2>

Foreign key constraint dependencies and serial column dependencies

from <productname>PostgreSQL</productname> versions prior to 7.3

are <emphasis>not</emphasis> maintained or created during the

upgrade process. All other dependency types survive the upgrade.