Rod Taylor kirjutas R, 29.08.2003 kell 03:31:
> 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?
AFAIK alter table change column should do the equivalent of
alter table x add column temp_name newdatatype;
update table x set temp_name=convert(name);
alter table x drop colum name;
alter table x rename column temp_name to name;
This should not renumber OIDS.
> ALTER TABLE test ADD CHECK(col > 4),
> add column bob integer default 2 not null,
> add column bob2 serial check(bob2 <= 255),
> drop column col2 cascade;
or with your combined syntax
alter table x
add column temp_name newdatatype = convert(current_name),
drop column current_name,
rename column temp_name tocurrent_ name;
---------------
Hannu
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
