Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

ALTER TABLE

Name

ALTER TABLE -- Modifies table and column attributes.

Synopsis

ALTER TABLE 
table
 [ * ]
      ADD [ COLUMN ] 
column
 
type

ALTER TABLE 
table
 [ * ]
      ALTER [ COLUMN ] 
column
 { SET DEFAULT 
defaultvalue
 | DROP DEFAULT }
ALTER TABLE 
table
 [ * ]
      RENAME [ COLUMN ] 
column
 TO 
newcolumn

ALTER TABLE 
table

      RENAME TO 
newtable

ALTER TABLE 
table

      ADD CONSTRAINT 
newconstraint
 
definition

ALTER TABLE 
table

      OWNER TO 
newowner

Parameters

table

The name of the (existing) table you intend to modify.

column

The name of a new column, or existing column that you intend to modify.

type

The data type of a new column being created. (This is used only during the creation of a new column.)

defaultvalue

A new default value for the specified column.

newcolumn

A new name for column .

newtable

A new name for table .

newconstraint definition

The name and definition of a new table constraint to be added to an existing table. See Chapter 7 for more details on how to define a table constraint.

newowner

The new owner of table (when transferring ownership).

Results

ALTER

The message returned when a column or table modification is completed successfully.

ERROR

The message returned if the table or column modifications cannot be completed, along with an explanation of what failed, if possible.

Description

The ALTER TABLE command is used to modify the structure of tables existing within a database in various ways. To rename a column or table, use the RENAME keyword. Renaming in this manner will not alter any of the data in either a column or a table. If you wish to add a new table constraint to a table, use the ADD CONSTRAINT clause with the same constraint syntax used with CREATE TABLE for a table constraint (see CREATE TABLE").

As of the most current version of PostgreSQL available at this printing (7.1.x), the only constraints that can be added to a table through the ADD CONSTRAINT clause are the CHECK and FOREIGN KEY constraints. To implicitly add a UNIQUE constraint, a workaround is to create a UNIQUE index, using the CREATE INDEX command (see CREATE INDEX"). To add any other constraints, you will have to recreate and reload data into the table in question.

To add a new column to a table, use ADD COLUMN with the same column syntax used in CREATE TABLE (see CREATE TABLE"). To modify or delete a column's default setting, use ALTER COLUMN with either the SET DEFAULT or DROP DEFAULT clause. (Remember that defaults are only applicable to newly added rows, and will not affect existing rows.)

As of PostgreSQL 7.1.x, you are not able to set the default value or constraint settings for a column at the same time as when it is added with the ADD COLUMN clause. You can, however, use the SET DEFAULT clause of ALTER TABLE to set the default values after the column is created. If you do this after the table has been in use for any period of time, be sure to use the UPDATE command to update the column's data in any existing rows to the new default.

Note: You must be the owner of a table, or a superuser, in order to modify it.

Examples

The following example adds a text column named address to the employees table:

booktown=# 
ALTER TABLE employees ADD COLUMN address text;

ALTER

Next, the newly added address column is renamed to mailing_address:

booktown=# 
ALTER TABLE employees RENAME COLUMN address TO mailing_address;

ALTER

The following example renames the employees table to personnel:

booktown=# 
ALTER TABLE employees RENAME TO personnel;

ALTER

The following example then changes the owner of the personnel table to the PostgreSQL user jonathan:

booktown=# 
ALTER TABLE personnel OWNER TO jonathan;

ALTER

Finally, the following syntax adds a FOREIGN KEY constraint to the schedules table named valid_employee, which verifies the employee id column in the personnel table:

booktown=# 
ALTER TABLE schedules ADD CONSTRAINT valid_employee

booktown-# 
                      FOREIGN KEY (employee_id)

booktown-# 
                      REFERENCES personnel (id) MATCH FULL;

NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire