Thread: ALTER TABLE
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;
Do you have this working:
ALTER TABLE / ADD COLUMN .. DEFAULT nextval('asdf')...
(eg. a default that needs to be re-evaluated for each row)
Also, did you remember about checking domain constraints, etc.?
Chris
----- Original Message -----
From: "Rod Taylor" <rbt@rbt.ca>
To: "PostgreSQL Development" <pgsql-hackers@postgresql.org>
Sent: Friday, August 29, 2003 8:31 AM
Subject: [HACKERS] ALTER TABLE
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;
Rod Taylor <rbt@rbt.ca> writes:
> 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?
Have you consulted the archives? ISTM we discussed these issues to
death a year or two back.
regards, tom lane
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_nametocurrent_ name; --------------- Hannu
On Thu, 2003-08-28 at 22:06, Christopher Kings-Lynne wrote:
> Do you have this working:
>
> ALTER TABLE / ADD COLUMN .. DEFAULT nextval('asdf')...
>
> (eg. a default that needs to be re-evaluated for each row)
>
> Also, did you remember about checking domain constraints, etc.?
Yes, and all necessary checks are performed.
On Fri, 2003-08-29 at 04:22, Hannu Krosing wrote: > 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; I presume we want the attnum needs to be preserved to preserve the constraints that are pointing at the column. Yes, constraints may need a cast injects (view too), but if we're not going to do that there is little advantage over doing the job by hand. as the above 4 step process.
On Thu, 2003-08-28 at 23:22, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > 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? > > Have you consulted the archives? ISTM we discussed these issues to > death a year or two back. I thought I had. Google doesn't give anything overly useful in a group search for 'alter table modify' or 'alter table change type' Found a nice long thread on dropping a column though. The discussion about SET / DROP NOT NULL had a little bit on modify. If you could point me in the right direction it would be appreciated.