I've been fiddling away on ALTER TABLE and have a few questions about a
possible datatype change. Just alter portions.
I presume I'll need to do a table rewrite. What is the best way to
change a single datum? heap_modify() takes a single relation type where
I will need to deal with different types. Simply build a new tuple with
old datums (easy enough)? Do we care about OIDs being renumbered?
If clustering is enabled, should I re-cluster the table at the same
time? Read table in index order rather than sequential scan. All other
work would be the same in either case.
Since the table is being rewritten and tuples rebuilt anyway, is it safe
to remove dropped columns completely from pg_attribute (renumbering the
others) or should a NULL be stored?
I'm sure lots of problems will be found with cached rules / views for
datatype changes that I've not even started looking for.
Thus far I have:
ALTER TABLE ... ADD COLUMN .. SERIAL
ALTER TABLE .. ADD COLUMN .. DEFAULT 42 NOT NULL
A new combination syntax that does all of the checks / defaults in a
single pass of the table rather than one per operation has also been
implemented.
ALTER TABLE test ADD CHECK(col > 4),add column bob integer default 2 not null,add column bob2 serial check(bob2 <=
255),dropcolumn col2 cascade;
