update columns

Some features are not yet implemented though :

However, these features can be achieved by using temporary tables and moving the data back and forth.

In these examples, we'll work with this table :

CREATE TABLE pers (
    id INT NOT NULL,
    fname TEXT NOT NULL,
    lname TEXT NOT NULL,
    gender CHAR NOT NULL,
    email VARCHAR(30),
    phone TEXT NOT NULL
);

The example requires you to create a new temporary table which is
slightly different from the 'pers' table.  In your database, you might not
still have the SQL "CREATE TABLE" statement handy, but you can always get it with pg_dump :

$ pg_dump -sn -t <tablename> <dbname>

1. Dropping a Column

To drop a column, create a new table without the offending column.

For example, to drop the phone column :

(See the important comments below about indexes, triggers, rules, and sequences.)

CREATE TABLE pers_temp (
    id INT NOT NULL,
    fname TEXT NOT NULL,
    lname TEXT NOT NULL,
    gender CHAR NOT NULL,
    email VARCHAR(30)
);

INSERT INTO pers_temp
SELECT * FROM pers;

DROP TABLE pers;

ALTER TABLE pers_temp RENAME TO pers;

2. Changing a column from NULL to NOT NULL (or vice versa)

This is much easier -- there's a field in the system catalog we can
switch.

To change the email field to NOT NULL :

UPDATE pg_attribute SET attnotnull = TRUE
WHERE attname = 'email'
AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'pers') ;

(set the same field to FALSE to clear the NOT NULL setting.)

This change does *not* require any fixing of indexes, triggers, etc.  The
only problem is when you change a column to NOT NULL, and there are null values in it.  In this case, PostgreSQL will *allow* the initial change, but refuses any UPDATEs to records with NULL values in the field.  If you do set it to NOT NULL when there are NULL values present, the only sane choice would be to immediate populate those NULL values.

3. Adding a Primary Key

A primary key in PostgreSQL is a unique index on a column which is NOT NULL.  Unless you name the primary key differently, PostgreSQL assumes the name of the key is '<tablename>_pkey'.

Creating a primary key is easy:

CREATE UNIQUE INDEX pers_pkey ON pers ( id );

4. Changing a column type

This is the same idea as #1, above - create a temporary table and use this as the new table.  Again, if you use triggers, sequences, or rules, see the important note below.

To make our email field larger:

CREATE TABLE pers_temp (
    id INT NOT NULL PRIMARY KEY,
    fname TEXT NOT NULL,
    lname TEXT NOT NULL,
    gender CHAR NOT NULL,
    email VARCHAR(50)
);

INSERT INTO pers_temp
SELECT * FROM pers;

DROP TABLE pers;

ALTER TABLE pers_temp RENAME TO pers;

IMPORTANT NOTES

Solutions #2 and #3 should work anywhere without problems.

Solutions #1 and #4 can be a pain if you use the following features of
PostgreSQL:

Sequences can be a pain because, in creating our new temporary table, if we use a SERIAL field type, PostgreSQL creates a sequence that is named for the table -- with the new, temporary table name.  So, if our 'pers' table used a SERIAL for the id, our new temporary table (created with field declaration "id SERIAL NOT NULL") really looks like this:

CREATE TABLE pers_temp (
id INT NOT NULL DEFAULT nextval('pers_temp_id_seq')
...

When we rename this table, the sequence won't be renamed with
it.  Everything will still work fine, but it's strange to have 'pers' getting
its serial id value from pers_temp_id_seq.

Therefore:

DROP SEQUENCE pers_temp_id_seq;

ALTER TABLE pers ALTER COLUMN id SET DEFAULT nextval('pers_id_seq');

Now, you're using your original sequence with the new 'pers' table.

Triggers, rules, and views

These are more of a problem.  Put simply, these objects in PostgreSQL know tables by their OID, not by their names (for a good description of OIDs, see Bruce Momjian's book).

To fix these, you'd have to DROP and CREATE every trigger that was placed on the 'pers' table, every rule that was placed on it, every view that drew from it.  And, worse, you have to then DROP and CREATE every view that relied on the view you just dropped, and so on.

Unless your database is small and simple, the best solution for this
is: DON'T DO IT.  Instead, make your changes with pg_dump and pg_restore.

(Functions aren't a problem, because functions are re-created with every new backend.  If you have functions that refer to the 'pers' table, and you drop it/rename it, you can simply quit psql and restart it to regain use of your functions that referred to it.)

Doing it with pg_dump / pg_restore

In complicated cases, the best solution is to make a dump file, edit that,
and restore it :

$ pg_dump -S postgres <dbname> > database.sql

then use your favorite text editor to edit this.  Some text editors
(such as Emacs/XEmacs) have a 'SQL mode' which can color-highlight or help you edit a SQL file.

Then, quit your editor and use psql to :

\c template1
DROP DATABASE <dbname>;
CREATE DATABASE <dbname> WITH TEMPLATE=template0;
\c <dbname>
\i database.sql

Doing so will drop your database, recreate it, and load in your (edited) dump file.

Notice the use of TEMPLATE=template0.  Template0 is a database that is empty of all user functions/tables/etc.  Template1 (the default template) starts off empty of user functions, but often, people add
languages/functions/tables, etc to template1, making it a bad choice for
using in a dump/restore cycle.

The restore process shows many NOTICE messages, and if your syntax is wrong, ERROR messages. To ensure that I don't miss any important messages, I actually do my dump like this:

Joel Burton
Director of Information Systems
Support Center of Washington