1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
16 HREF="sql-commands.html"><LINK
18 TITLE="ALTER SEQUENCE"
19 HREF="sql-altersequence.html"><LINK
22 HREF="sql-altertrigger.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="sql-altersequence.html"
60 HREF="sql-altersequence.html"
73 HREF="sql-altertrigger.html"
81 HREF="sql-altertrigger.html"
102 >ALTER TABLE -- change the definition of a table</DIV
107 CLASS="REFSYNOPSISDIV"
115 >ALTER TABLE [ ONLY ] <VAR
127 >column_constraint</VAR
129 ALTER TABLE [ ONLY ] <VAR
136 > [ RESTRICT | CASCADE ]
137 ALTER TABLE [ ONLY ] <VAR
141 ALTER [ COLUMN ] <VAR
148 ALTER TABLE [ ONLY ] <VAR
152 ALTER [ COLUMN ] <VAR
155 > { SET | DROP } NOT NULL
156 ALTER TABLE [ ONLY ] <VAR
160 ALTER [ COLUMN ] <VAR
163 > SET STATISTICS <VAR
167 ALTER TABLE [ ONLY ] <VAR
171 ALTER [ COLUMN ] <VAR
174 > SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
175 ALTER TABLE [ ONLY ] <VAR
180 ALTER TABLE [ ONLY ] <VAR
184 RENAME [ COLUMN ] <VAR
199 ALTER TABLE [ ONLY ] <VAR
205 >table_constraint</VAR
207 ALTER TABLE [ ONLY ] <VAR
213 >constraint_name</VAR
214 > [ RESTRICT | CASCADE ]
243 > changes the definition of an existing table.
244 There are several subforms:
258 > This form adds a new column to the table using the same syntax as
260 HREF="sql-createtable.html"
274 > This form drops a column from a table. Indexes and
275 table constraints involving the column will be automatically
276 dropped as well. You will need to say <TT
280 anything outside the table depends on the column, for example,
281 foreign key references or views.
294 > These forms set or remove the default value for a column.
295 The default values only apply to subsequent <TT
299 commands; they do not cause rows already in the table to change.
300 Defaults may also be created for views, in which case they are
304 > statements on the view before
321 > These forms change whether a column is marked to allow null
322 values or to reject null values. You can only use <TT
326 > when the column contains no null values.
337 sets the per-column statistics-gathering target for subsequent
339 HREF="sql-analyze.html"
344 The target can be set in the range 0 to 1000; alternatively, set it
345 to -1 to revert to using the system default statistics target.
355 > This form sets the storage mode for a column. This controls whether this
356 column is held inline or in a supplementary table, and whether the data
357 should be compressed or not. <TT
361 for fixed-length values such as <TT
365 inline, uncompressed. <TT
369 compressible data. <TT
373 uncompressed data, and <TT
380 > is the default for all
381 data types that support it. The use of <TT
385 make substring operations on a <TT
388 > column faster, at the penalty of
389 increased storage space.
395 >SET WITHOUT OIDS</TT
399 > This form removes the <TT
403 table. Removing OIDs from a table does not occur immediately.
404 The space that the OID uses will be reclaimed when the row is
405 updated. Without updating the row, both the space and the value
406 of the OID are kept indefinitely. This is semantically similar
423 > forms change the name of a table
424 (or an index, sequence, or view) or the name of an individual column in
425 a table. There is no effect on the stored data.
433 >table_constraint</VAR
438 > This form adds a new constraint to a table using the same syntax as
440 HREF="sql-createtable.html"
454 > This form drops constraints on a table.
455 Currently, constraints on tables are not required to have unique
456 names, so there may be more than one constraint matching the specified
457 name. All such constraints will be dropped.
467 > This form changes the owner of the table, index, sequence, or view to the
478 > This form marks a table for future <A
479 HREF="sql-cluster.html"
492 > You must own the table to use <TT
498 >ALTER TABLE OWNER</TT
499 >, which may only be executed by a superuser.
521 > The name (possibly schema-qualified) of an existing table to
525 > is specified, only that table is
529 > is not specified, the table and all
530 its descendant tables (if any) are updated. <TT
534 appended to the table name to indicate that descendant tables are
535 to be altered, but in the current version, this is the default
536 behavior. (In releases before 7.1, <TT
540 default behavior. The default can be altered by changing the
541 configuration parameter <VAR
543 >sql_inheritance</VAR
554 > Name of a new or existing column.
564 > Data type of the new column.
574 > New name for an existing column.
584 > New name for the table.
590 >table_constraint</VAR
594 > New table constraint for the table.
600 >constraint_name</VAR
604 > Name of an existing constraint to drop.
614 > The user name of the new owner of the table.
624 > The index name on which the table should be marked for clustering.
634 > Automatically drop objects that depend on the dropped column
635 or constraint (for example, views referencing the column).
645 > Refuse to drop the column or constraint if there are any dependent
646 objects. This is the default behavior.
663 > is noise and can be omitted.
666 > In the current implementation of <TT
673 > clauses for the new column are not supported.
674 The new column always comes into being with all values null.
682 > to set the default afterward.
683 (You may also want to update the already existing rows to the
684 new default value, using
686 HREF="sql-update.html"
691 If you want to mark the column non-null, use the <TT
695 form after you've entered non-null values for the column in all rows.
701 > form does not physically remove
702 the column, but simply makes it invisible to SQL operations. Subsequent
703 insert and update operations in the table will store a null value for the column.
704 Thus, dropping a column is quick but it will not immediately reduce the
705 on-disk size of your table, as the space occupied
706 by the dropped column is not reclaimed. The space will be
707 reclaimed over time as existing rows are updated.
708 To reclaim the space at once, do a dummy <TT
712 and then vacuum, as in:
714 CLASS="PROGRAMLISTING"
715 >UPDATE table SET col = col;
716 VACUUM FULL table;</PRE
720 > If a table has any descendant tables, it is not permitted to add
721 or rename a column in the parent table without doing the same to
722 the descendants. That is, <TT
724 >ALTER TABLE ONLY</TT
726 will be rejected. This ensures that the descendants always have
727 columns matching the parent.
733 > operation will remove a
734 descendant table's column only if the descendant does not inherit
735 that column from any other parents and never had an independent
736 definition of the column. A nonrecursive <TT
742 >ALTER TABLE ONLY ... DROP
744 >) never removes any descendant columns, but
745 instead marks them as independently defined rather than inherited.
748 > Changing any part of a system catalog table is not permitted.
754 > for a further description
755 of valid parameters. <A
758 > has further information on
770 > To add a column of type <TT
775 CLASS="PROGRAMLISTING"
776 >ALTER TABLE distributors ADD COLUMN address varchar(30);</PRE
780 > To drop a column from a table:
782 CLASS="PROGRAMLISTING"
783 >ALTER TABLE distributors DROP COLUMN address RESTRICT;</PRE
787 > To rename an existing column:
789 CLASS="PROGRAMLISTING"
790 >ALTER TABLE distributors RENAME COLUMN address TO city;</PRE
794 > To rename an existing table:
796 CLASS="PROGRAMLISTING"
797 >ALTER TABLE distributors RENAME TO suppliers;</PRE
801 > To add a not-null constraint to a column:
803 CLASS="PROGRAMLISTING"
804 >ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;</PRE
806 To remove a not-null constraint from a column:
808 CLASS="PROGRAMLISTING"
809 >ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;</PRE
814 To add a check constraint to a table:
816 CLASS="PROGRAMLISTING"
817 >ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);</PRE
822 To remove a check constraint from a table and all its children:
824 CLASS="PROGRAMLISTING"
825 >ALTER TABLE distributors DROP CONSTRAINT zipchk;</PRE
830 To add a foreign key constraint to a table:
832 CLASS="PROGRAMLISTING"
833 >ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;</PRE
838 To add a (multicolumn) unique constraint to a table:
840 CLASS="PROGRAMLISTING"
841 >ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);</PRE
846 To add an automatically named primary key constraint to a table, noting
847 that a table can only ever have one primary key:
849 CLASS="PROGRAMLISTING"
850 >ALTER TABLE distributors ADD PRIMARY KEY (dist_id);</PRE
865 > form conforms with the SQL
866 standard, with the exception that it does not support defaults and
867 not-null constraints, as explained above. The <TT
871 > form is in full conformance.
874 > The clauses to rename tables, columns, indexes, views, and sequences are
878 > extensions of the SQL standard.
883 >ALTER TABLE DROP COLUMN</TT
884 > can be used to drop the only
885 column of a table, leaving a zero-column table. This is an
886 extension of SQL, which disallows zero-column tables.
894 SUMMARY="Footer navigation table"
905 HREF="sql-altersequence.html"
923 HREF="sql-altertrigger.html"
939 HREF="sql-commands.html"