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),
	drop column col2 cascade;
Thu, 28 Aug 2003 20:31:39 -0400
Rod Taylor <rbt@rbt.ca>
ALTER TABLE
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),
 drop column col2 cascade;
 


Fri, 29 Aug 2003 10:06:04 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: ALTER TABLE
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

Thu, 28 Aug 2003 23:22:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE 
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


Fri, 29 Aug 2003 11:22:11 +0300
Hannu Krosing <hannu@tm.ee>
Re: ALTER TABLE
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.
Fri, 29 Aug 2003 08:04:14 -0400
Rod Taylor <rbt@rbt.ca>
Re: ALTER TABLE
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.
Fri, 29 Aug 2003 08:56:19 -0400
Rod Taylor <rbt@rbt.ca>
Re: ALTER TABLE
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.
Fri, 29 Aug 2003 09:23:45 -0400
Rod Taylor <rbt@rbt.ca>
Re: ALTER TABLE