Forum OpenACS Q&A: Could I alter a column type or modifier in PG ?

Collapse
1: Could I alter a column type or modifier in PG ?
Posted by Yeung Heunry on 08/08/02 04:29 AM
As title
Collapse
2: Response to Could I alter a column type or modifier in PG ? (response to 1)
Posted by Andrei Popov on 08/08/02 07:51 AM
There was a thread on column type, see: https://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=00054L&topic_id=OpenACS&topic=11 (well, it is about resizing, rather, not type change...)

As far as modifier is concerned, if you try 

ALTER TABLE table ADD COLUMN column integer not null;

you will be prompted with:

ERROR:  Adding NOT NULL columns is not implemented.
        Add the column, then use ALTER TABLE ADD CONSTRAINT.

Then you abide and do this instead:

ALTER TABLE table ADD COLUMN column integer;
ALTER TABLE table ADD CONSTRAINT column_nn CHECK (column is not null);
Collapse
3: Response to Could I alter a column type or modifier in PG ? (response to 1)
Posted by Jonathan Ellis on 08/08/02 05:31 PM
I believe it's more efficient to have it actually recorded as "not null" at the attribute level than as a check constraint. To do that you can do this: 
UPDATE pg_attribute SET attnotnull = TRUE
WHERE attname = 'my_column_name'
AND attrelid = (SELECT oid FROM pg_class WHERE relname = 'my_table_name')
It's quite safe, although you'll need to separately check for any existing NULL values.