Thread: ALTER TABLE

ALTER TABLE

From
Rod Taylor
Date:
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; 

Re: ALTER TABLE

From
"Christopher Kings-Lynne"
Date:
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;
 



Re: ALTER TABLE

From
Tom Lane
Date:
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


Re: ALTER TABLE

From
Hannu Krosing
Date:
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



Re: ALTER TABLE

From
Rod Taylor
Date:
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.

Re: ALTER TABLE

From
Rod Taylor
Date:
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.


Re: ALTER TABLE

From
Rod Taylor
Date:
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.