http://www.postgresql.org/docs/9.1/static/sql-altertable.html Examples To add a column of type varchar to a table: ALTER TABLE distributors ADD COLUMN address varchar(30); To drop a column from a table: ALTER TABLE distributors DROP COLUMN address RESTRICT; To change the types of two existing columns in one operation: ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80), ALTER COLUMN name TYPE varchar(100); To change an integer column containing UNIX timestamps to timestamp with time zone via a USING clause: ALTER TABLE foo ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'; The same, when the column has a default expression that won't automatically cast to the new data type: ALTER TABLE foo ALTER COLUMN foo_timestamp DROP DEFAULT, ALTER COLUMN foo_timestamp TYPE timestamp with time zone USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second', ALTER COLUMN foo_timestamp SET DEFAULT now(); To rename an existing column: ALTER TABLE distributors RENAME COLUMN address TO city; To rename an existing table: ALTER TABLE distributors RENAME TO suppliers; To add a not-null constraint to a column: ALTER TABLE distributors ALTER COLUMN street SET NOT NULL; To remove a not-null constraint from a column: ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL; To add a check constraint to a table and all its children: ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); To remove a check constraint from a table and all its children: ALTER TABLE distributors DROP CONSTRAINT zipchk; To remove a check constraint from one table only: ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk; (The check constraint remains in place for any child tables.) To add a foreign key constraint to a table: ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL; To add a (multicolumn) unique constraint to a table: ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode); To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key: ALTER TABLE distributors ADD PRIMARY KEY (dist_id); To move a table to a different tablespace: ALTER TABLE distributors SET TABLESPACE fasttablespace; To move a table to a different schema: ALTER TABLE myschema.distributors SET SCHEMA yourschema; To recreate a primary key constraint, without blocking updates while the index is rebuilt: CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
