Re: Bug in 7.2.1? -- ERROR: Adding columns with defaults is not implemented.

----- Original Message -----
From: <terry@greatgulfhomes.com>
To: "'ad wolf'" <adwolf1@yahoo.com>; <pgsql-general@postgresql.org>
Sent: Monday, July 08, 2002 12:10 PM
Subject: Re: [GENERAL] Bug in 7.2.1? -- ERROR: Adding columns with defaults
is not implemented.


> To implement the NOT NULL you either have to drop and recreate the table
> (which is what I do) or manually insert the appropriate trigger (Ugh).

Or update the system table:

UPDATE pg_attribute SET attnotnull=True WHERE attrelid=(SELECT oid FROM
pg_class WHERE relname='tablename') AND attname='fieldname'

Where "tablename" is the name of the table to update and "fieldname" is the
name of the NOT NULL field.

> What I do is add the column without the NOT NULL or default,
> then use the ALTER TABLE to set the column default,
> then use pg_dump -t tablename databasename > table_backup
> then vi table_backup and change the NULL to NOT NULL for the column
> then drop the table
> then reload the table with the NOT NULL constraint with:
> psql -e database < table_backup

Ugh, I find the system table UPDATE to be much easier to swallow :)

Greg