Contents /
Previous /
Next
Modifying a Table: ALTER TABLE
The ALTER TABLE statement is used to add or drop columns in an existing table.
<![CDATA[
ALTER TABLE table ADD column type [ column_constraint ]
ALTER TABLE table ALTER [ COLUMN ] column
{ SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table RENAME TO new_table
ALTER TABLE table OWNER TO new_owner
]]>
Example:
<![CDATA[
ALTER TABLE students ADD semester INTEGER;
ALTER TABLE students RENAME TO stud;
]]>
ALTER TABLE DROP COLUM ??##!!
Does not yet work in PostgreSQL!
You have to do the following instead:
<![CDATA[
SELECT [all columns except named] into temp1 from [table];
drop table [table];
SELECT * into [table] from temp1;
]]>
SET or DROP a NOT NULL of a Column
Wait for the new PostgresSQL command:
<![CDATA[
ALTER TABLE doof ALTER record_id SET NOT NULL;
ALTER TABLE doof ALTER record_id DROP NOT NULL;
]]>
Or change the system catalog by hand.
Example (change the "id" field in table "stud" to NOT NULL):
<![CDATA[
UPDATE pg_attribute SET attnotnull = TRUE
WHERE attname = 'id'
AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'stud') ;
]]>
Take care that there are no NULL values present when you set NOT NULL.
ADD a PRIMARY KEY
You have to set NOT NULL for the key attribute first (see above).
<![CDATA[
> Alter table stud add primary key(id);
> \d stud
Table "stud"
Column | Type | Modifiers
----------+-----------------------+---------------
id | integer | not null
name | character varying(80) |
Primary key: stud_pkey
]]>