Thread: ALTER TABLE TODO items
TODO items completed:
o -ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT o -ALTER TABLE ADD COLUMN column
DEFAULTshould fill existing rows with DEFAULT value o -Allow ALTER TABLE to modify column lengths and
changeto binary compatible types
Seems we didn't have ALTER COLUMN TYPE on the TODO list.
Do we still want this TODO?
o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2]; have SELECT * and INSERT honor
suchordering
I don't think so. As I remember it was part of doing logical attribute
numbers as a way to add ALTER COLUMN TYPE, but because we now use table
recreate to implement this, it is unlikely we will ever add logical
attribute numbers (adds too much complexity to the code).
---------------------------------------------------------------------------
Tom Lane wrote:
> CVSROOT: /cvsroot
> Module name: pgsql-server
> Changes by: tgl@svr1.postgresql.org 04/05/05 01:48:48
>
> Modified files:
> doc/src/sgml/ref: alter_table.sgml
> src/backend/bootstrap: bootparse.y
> src/backend/catalog: dependency.c heap.c index.c
> src/backend/commands: cluster.c indexcmds.c tablecmds.c
> src/backend/nodes: copyfuncs.c equalfuncs.c
> src/backend/parser: analyze.c gram.y
> src/backend/tcop: utility.c
> src/backend/utils/adt: ruleutils.c
> src/include/catalog: dependency.h heap.h index.h
> src/include/commands: cluster.h defrem.h tablecmds.h
> src/include/nodes: nodes.h parsenodes.h
> src/include/parser: analyze.h
> src/include/utils: builtins.h
> src/test/regress/expected: alter_table.out foreign_key.out
> inherit.out
> src/test/regress/sql: alter_table.sql foreign_key.sql
> inherit.sql
>
> Log message:
> ALTER TABLE rewrite. New cool stuff:
>
> * ALTER ... ADD COLUMN with defaults and NOT NULL constraints works per SQL
> spec. A default is implemented by rewriting the table with the new value
> stored in each row.
>
> * ALTER COLUMN TYPE. You can change a column's datatype to anything you
> want, so long as you can specify how to convert the old value. Rewrites
> the table. (Possible future improvement: optimize no-op conversions such
> as varchar(N) to varchar(N+1).)
>
> * Multiple ALTER actions in a single ALTER TABLE command. You can perform
> any number of column additions, type changes, and constraint additions with
> only one pass over the table contents.
>
> Basic documentation provided in ALTER TABLE ref page, but some more docs
> work is needed.
>
> Original patch from Rod Taylor, additional work from Tom Lane.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
* Bruce Momjian (pgman@candle.pha.pa.us) wrote: > Do we still want this TODO? > > o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2]; > have SELECT * and INSERT honor such ordering > > I don't think so. As I remember it was part of doing logical attribute > numbers as a way to add ALTER COLUMN TYPE, but because we now use table > recreate to implement this, it is unlikely we will ever add logical > attribute numbers (adds too much complexity to the code). Does using table recreate break views built against the table? I recall someone saying the old way did, it would be very nice if that could be fixed or at least added to the TODO. I like the idea of being able to alter the positions of the columns too, but that's not as big of an issue as breaking views. Stephen
Stephen Frost <sfrost@snowman.net> writes:
> Does using table recreate break views built against the table?
Right now it just rejects the ALTER attempt:
regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create view v1 as select * from t1;
CREATE VIEW
regression=# alter table t1 alter f1 type bigint;
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v1 depends on column "f1"
regression=#
Improving this per the previous discussion probably ought to be
mentioned in the TODO list.
regards, tom lane
On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
>
> TODO items completed:
>
> o -ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT
> o -ALTER TABLE ADD COLUMN column DEFAULT should fill existing
> rows with DEFAULT value
> o -Allow ALTER TABLE to modify column lengths and change to binary
> compatible types
>
> Seems we didn't have ALTER COLUMN TYPE on the TODO list.
>
> Do we still want this TODO?
>
> o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
> have SELECT * and INSERT honor such ordering
>
> I don't think so. As I remember it was part of doing logical attribute
> numbers as a way to add ALTER COLUMN TYPE, but because we now use table
> recreate to implement this, it is unlikely we will ever add logical
> attribute numbers (adds too much complexity to the code).
>
I think we should leave since it is still functionality that people will
want. Furthermore I am not sure we are done with ALTER COLUMN TYPE
completely. Granted I've not yet had time to take a thorough look at the
implementation so I could be off here, but as I understand it the
current code seems a little problematic on large tables; recreating the
entire table is likely to cause excessive i/o and disk space issues
compared to a potentially much nicer add column/update column/drop
column routine.
Hmm... upon further thought, if the above implementation stands up, istm
that its machinations could also be used to implement the reordering
functionality... ie. rewrite the table and fix up any dependencies as
needed.
way to back track on myself eh?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Tom Lane wrote: > Stephen Frost <sfrost@snowman.net> writes: > > Does using table recreate break views built against the table? > > Right now it just rejects the ALTER attempt: > > regression=# create table t1 (f1 int); > CREATE TABLE > regression=# create view v1 as select * from t1; > CREATE VIEW > regression=# alter table t1 alter f1 type bigint; > ERROR: cannot alter type of a column used by a view or rule > DETAIL: rule _RETURN on view v1 depends on column "f1" > regression=# > > Improving this per the previous discussion probably ought to be > mentioned in the TODO list. Add to TODO? Allow views to be auto-recreated based on table changes -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Do we still want this TODO? > > o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2]; > have SELECT * and INSERT honor such ordering > > I don't think so. As I remember it was part of doing logical attribute > numbers as a way to add ALTER COLUMN TYPE, but because we now use table > recreate to implement this, it is unlikely we will ever add logical > attribute numbers (adds too much complexity to the code). Well, I manage a DB that is up and running 24/24 7/7 since 3 years now, the only off working time was during the engine update. At the beginning with few hundred record on each table, in order to add a column in the desidered position I was performing the recreation table adventure with the pain to reconstruct all views depending on it ( at that time postgres didn't even had any dependencies information ), and all foreign key refering the table. Now with milion of record this is not feseable. What we do now is add, in the development DB, the column at the end of the table, this just to have the table in production and in the development environment with the same definition. I think that have a way to reorder the column inside a table definition could save us some pains. Regards Gaetano Mendola
Stephen Frost wrote: > > I don't think so. As I remember it was part of doing logical attribute > > numbers as a way to add ALTER COLUMN TYPE, but because we now use table > > recreate to implement this, it is unlikely we will ever add logical > > attribute numbers (adds too much complexity to the code). > > Does using table recreate break views built against the table? I recall > someone saying the old way did, it would be very nice if that could be > fixed or at least added to the TODO. I like the idea of being able to > alter the positions of the columns too, but that's not as big of an > issue as breaking views. Yea, I think it will break views. You have to recreate them. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Wow, you can reference different column as part of the alter column.
Yeah, the USING is actually any arbitrary expression over the old table
row. (Getting that to work was a tad tricky...) So you can view this
as a full-table UPDATE operation that folds in possible column type changes.
regards, tom lane
Tom Lane wrote: > select * from another; > f1 | f2 > ----+------- > 1 | one > 2 | two > 3 | three > (3 rows) > > alter table another > alter f1 type text using f2 || ' more', > alter f2 type bigint using f1 * 10; > > select * from another; > f1 | f2 > ------------+---- > one more | 10 > two more | 20 > three more | 30 > (3 rows) Wow, you can reference different column as part of the alter column. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
>> Do we still want this TODO?
>>
>> o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
> I think we should leave since it is still functionality that people will
> want.
It's not that no one would want it, it's that the code impact (and risk
of bugs) associated with separate logical and physical column numbers
seems very disproportionate to the value. The main argument for it
AFAIR was to support column type substitution via drop col/add col/
reorder col. Now that we have a better way I think the value of such a
feature wouldn't be worth the work/risk.
> recreating the entire table is likely to cause excessive i/o and disk
> space issues compared to a potentially much nicer add column/update
> column/drop column routine.
How you figure that? The UPDATE step will in itself require 2X disk
space --- and after that you'll need a VACUUM FULL to get it back.
The implementation Rod came up with is much nicer.
> Hmm... upon further thought, if the above implementation stands up, istm
> that its machinations could also be used to implement the reordering
> functionality... ie. rewrite the table and fix up any dependencies as
> needed.
True. In fact, this example that I put into the regression tests may be
food for thought:
create table another (f1 int, f2 text);
insert into another values(1, 'one');
insert into another values(2, 'two');
insert into another values(3, 'three');
select * from another;f1 | f2
----+------- 1 | one 2 | two 3 | three
(3 rows)
alter table another alter f1 type text using f2 || ' more', alter f2 type bigint using f1 * 10;
select * from another; f1 | f2
------------+----one more | 10two more | 20three more | 30
(3 rows)
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >>Wow, you can reference different column as part of the alter column. > > > Yeah, the USING is actually any arbitrary expression over the old table > row. (Getting that to work was a tad tricky...) So you can view this > as a full-table UPDATE operation that folds in possible column type changes. Does that mean I'll want to disable triggers while I do this? Actually, if the structure's changing I presume I'll want to drop/recreate my triggers anyway (even if they get reparsed like view definitions). -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> Does that mean I'll want to disable triggers while I do this?
Hrm. Right now the code does not fire triggers at all, but that seems
wrong. However, I doubt that very many triggers could cope with update
events in which the old and new rows have different rowtypes :-(.
Any thoughts what to do about that?
regards, tom lane
> Yeah, the USING is actually any arbitrary expression over the old table > row. (Getting that to work was a tad tricky...) So you can view this > as a full-table UPDATE operation that folds in possible column type changes. All I can say is three cheers for Tom and Rod on this one!!!! Chris
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > Does that mean I'll want to disable triggers while I do this? > > Hrm. Right now the code does not fire triggers at all, but that seems > wrong. However, I doubt that very many triggers could cope with update > events in which the old and new rows have different rowtypes :-(. > Any thoughts what to do about that? If triggers exist, I think we should just throw a warning that triggers will not be fired. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Tom Lane wrote: > >>Richard Huxton <dev@archonet.com> writes: >> >>>Does that mean I'll want to disable triggers while I do this? >> >>Hrm. Right now the code does not fire triggers at all, but that seems >>wrong. However, I doubt that very many triggers could cope with update >>events in which the old and new rows have different rowtypes :-(. >>Any thoughts what to do about that? > > > If triggers exist, I think we should just throw a warning that triggers > will not be fired. Tom's point about triggers probably not working after the upgrade is a good one though. Is it reasonable to just refuse to act on a table until all triggers are dropped? I'd rather be forcedto go through and drop/restore triggers in a script than be surprised later on. -- Richard Huxton Archonet Ltd
Two very minor points with the new alter table (not sure if they are even bugs are have already been addressed). 1. alter table alter type on a clustered index seems to drop the cluster (by design)? 2. alter table cluster on seems to give a strange error message of the index name is really the name of a table. Ex: alter table test cluster on test; returns: ERROR: cache lookup failed for index 19013 Merlin Log: test=# create table test (id int); CREATE TABLE test=# create index test_id_idx on test(id); CREATE INDEX test=# alter table test cluster on test; ERROR: cache lookup failed for index 19046 test=# alter table test cluster on test_id_idx; ALTER TABLE test=# \d test Table "public.test"Column | Type | Modifiers --------+---------+-----------id | integer | Indexes: "test_id_idx" btree (id) CLUSTER test=# alter table test alter id type bigint; ALTER TABLE test=# \d test Table "public.test"Column | Type | Modifiers --------+--------+-----------id | bigint | Indexes: "test_id_idx" btree (id) test=#
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> 1. alter table alter type on a clustered index seems to drop the cluster
> (by design)?
Hmm. Not by design really, but because it's rebuilding all of the
indexes and that status bit doesn't get passed through. I'll see how
hard it is to fix.
> 2. alter table cluster on seems to give a strange error message of the
> index name is really the name of a table.
This is a pre-existing bug, but should be easy enough to fix.
regards, tom lane
On Thu, 6 May 2004, Richard Huxton wrote: > Bruce Momjian wrote: > > Tom Lane wrote: > > > >>Richard Huxton <dev@archonet.com> writes: > >> > >>>Does that mean I'll want to disable triggers while I do this? > >> > >>Hrm. Right now the code does not fire triggers at all, but that seems > >>wrong. However, I doubt that very many triggers could cope with update > >>events in which the old and new rows have different rowtypes :-(. > >>Any thoughts what to do about that? > > > > > > If triggers exist, I think we should just throw a warning that triggers > > will not be fired. > > Tom's point about triggers probably not working after the upgrade is a > good one though. Is it reasonable to just refuse to act on a table until > all triggers are dropped? I'd rather be forced to go through and > drop/restore triggers in a script than be surprised later on. How about "cascade drop triggers" as an option so you can still do it in one line should you want to?
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> 1. alter table alter type on a clustered index seems to drop the cluster
I tweaked things so that the clustered flag is preserved for indexes
that aren't directly affected by the ALTER TYPE. It would take more
work to preserve the setting for an index that is rebuilt by ALTER TYPE,
and I'm not even sure that it's sensible --- the new index could have
a significantly different ordering from the old. What do you think?
> 2. alter table cluster on seems to give a strange error message of the
> index name is really the name of a table.
There were several bugs there ... think I got them all ...
regards, tom lane
Tom Lane wrote: > I tweaked things so that the clustered flag is preserved for indexes > that aren't directly affected by the ALTER TYPE. It would take more > work to preserve the setting for an index that is rebuilt by ALTER TYPE, > and I'm not even sure that it's sensible --- the new index could have > a significantly different ordering from the old. What do you think? Fair enough. Perhaps a notice level log message would be appropriate? Merlin
Tom Lane wrote: > "Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > > 1. alter table alter type on a clustered index seems to drop the cluster > > I tweaked things so that the clustered flag is preserved for indexes > that aren't directly affected by the ALTER TYPE. It would take more > work to preserve the setting for an index that is rebuilt by ALTER TYPE, > and I'm not even sure that it's sensible --- the new index could have > a significantly different ordering from the old. What do you think? At a minimum, we should indicate we dropped the cluster on the index. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> At a minimum, we should indicate we dropped the cluster on the index.
[shrug] If you're going to make me do that, I might as well reinstall
the bit on the new index. The code's problem is it doesn't know that
any of the indexes it dropped were clustered, and finding that out is
90% of the issue.
What I want to know is whether it is sensible to mark the revised index
as clustered, given that its semantics might be significantly different
from before.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > At a minimum, we should indicate we dropped the cluster on the index. > > [shrug] If you're going to make me do that, I might as well reinstall > the bit on the new index. The code's problem is it doesn't know that > any of the indexes it dropped were clustered, and finding that out is > 90% of the issue. > > What I want to know is whether it is sensible to mark the revised index > as clustered, given that its semantics might be significantly different > from before. OK, yea, just leave the bit. We can add documentation that they should run CLUSTER again if they radically modified the column as part of the ALTER> -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thursday 06 May 2004 11:47, scott.marlowe wrote:
> On Thu, 6 May 2004, Richard Huxton wrote:
> > Bruce Momjian wrote:
> > > Tom Lane wrote:
> > >>Richard Huxton <dev@archonet.com> writes:
> > >>>Does that mean I'll want to disable triggers while I do this?
> > >>
> > >>Hrm. Right now the code does not fire triggers at all, but that seems
> > >>wrong. However, I doubt that very many triggers could cope with update
> > >>events in which the old and new rows have different rowtypes :-(.
> > >>Any thoughts what to do about that?
> > >
> > > If triggers exist, I think we should just throw a warning that triggers
> > > will not be fired.
> >
> > Tom's point about triggers probably not working after the upgrade is a
> > good one though. Is it reasonable to just refuse to act on a table until
> > all triggers are dropped? I'd rather be forced to go through and
> > drop/restore triggers in a script than be surprised later on.
>
> How about "cascade drop triggers" as an option so you can still do it in
> one line should you want to?
What about rules/views/functions and who knows what else (domains?) might be
dependant on the current type definition? It seems like a pretty big can of
worms really.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> What about rules/views/functions and who knows what else (domains?)
> might be dependant on the current type definition?
Yeah, I was just thinking about that this morning. We probably ought to
look for dependencies on the table rowtype as well as the individual
column.
But on the other side of the coin, should we actually reject the ALTER
if we see a function that uses the rowtype as a parameter or result
type? Without looking inside the function, we can't really tell if the
ALTER will break the function or not.
regards, tom lane
> I tweaked things so that the clustered flag is preserved for indexes > that aren't directly affected by the ALTER TYPE. It would take more > work to preserve the setting for an index that is rebuilt by ALTER TYPE, > and I'm not even sure that it's sensible --- the new index could have > a significantly different ordering from the old. What do you think? Out of interest what happens to other column features such as the existing statistics level and the existing storage spec? I guess these might have to change when type changes?? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Out of interest what happens to other column features such as the
> existing statistics level and the existing storage spec? I guess these
> might have to change when type changes??
I left the statistic setting as-is (do you think that's wrong?) but the
storage spec gets reset to whatever the default for the new type is.
We could talk about doing something more complicated, such as "keep the
old setting if both old and new types support toasting, else reset to
new default". Not sure if that'd be better or not.
regards, tom lane
> I left the statistic setting as-is (do you think that's wrong?) but the > storage spec gets reset to whatever the default for the new type is. Seems reasonable. > We could talk about doing something more complicated, such as "keep the > old setting if both old and new types support toasting, else reset to > new default". Not sure if that'd be better or not. Yeah, I was thinking along those lines. I don't now though... What happens with ordering of operations in the ALTER TABLE statement? Like if I put an alter TYPE and a SET STORAGE in the same statement (wiht commas between), in what order will things happen? Is it deterministic? Is it documented? Are there situations where a crazy collection of 20 commands in a single ALTER TABLE will have unpredictable effects? Also, should the syntax be SET TYPE, not just TYPE? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> What happens with ordering of operations in the ALTER TABLE statement?
> Like if I put an alter TYPE and a SET STORAGE in the same statement
> (wiht commas between), in what order will things happen?
The "right thing" will happen --- in this case, the SET STORAGE will
take effect (before we actually rewrite the table, so the end result
will be exactly what you want). Check out the multiple-pass structure
in commands/tablecmds.c.
> Is it deterministic? Is it documented? Are there situations where a
> crazy collection of 20 commands in a single ALTER TABLE will have
> unpredictable effects?
Yes, no (I did say we needed more docs effort), and I hope not.
> Also, should the syntax be SET TYPE, not just TYPE?
Shrug ... I dunno whether Rod had a precedent for that choice or not.
regards, tom lane
> > Also, should the syntax be SET TYPE, not just TYPE? > > Shrug ... I dunno whether Rod had a precedent for that choice or not. FireBird: ALTER COLUMN <column> TYPE <type> DB2: ALTER COLUMN <column> SET DATA TYPE <type>. Oracle: MODIFY <column> <type> MSSQL: ALTER COLUMN <column> <type> <constraints> MySQL: Both Oracle and MSSQL Sap: MODIFY <column> <type> Spec: Nothing (obvious) on changing column types MODIFY is horrible. It seems to drop all constraints, defaults, etc that are not specified in the second definition. It is essentially a replacement of the column. FireBird is the closest to our implementation. DB2 only allows changing the length of a VARCHAR and even then it is restrictive in the amount the length can change by. I remember polling -hackers to see if there were objections at the time, but the syntax is easy enough to change if you wish.
Rod Taylor <pg@rbt.ca> writes:
>>> Also, should the syntax be SET TYPE, not just TYPE?
>> Shrug ... I dunno whether Rod had a precedent for that choice or not.
> FireBird: ALTER COLUMN <column> TYPE <type>
> DB2: ALTER COLUMN <column> SET DATA TYPE <type>.
> Oracle: MODIFY <column> <type>
> MSSQL: ALTER COLUMN <column> <type> <constraints>
> MySQL: Both Oracle and MSSQL
> Sap: MODIFY <column> <type>
Given that, I'm happy with what we got ...
regards, tom lane
> I did say we needed more docs effort Yes, where should the docs for this go? The Alter table reference page, or Chapter 5.5 titled "Modifying Tables"?
>>FireBird: ALTER COLUMN <column> TYPE <type> >>DB2: ALTER COLUMN <column> SET DATA TYPE <type>. >>Oracle: MODIFY <column> <type> >>MSSQL: ALTER COLUMN <column> <type> <constraints> >>MySQL: Both Oracle and MSSQL >>Sap: MODIFY <column> <type> > > > Given that, I'm happy with what we got ... Yeah same, I was just wondering whether it should be like SET NOT NULL, that's all. Chris
Tom Lane wrote: > Robert Treat <xzilla@users.sourceforge.net> writes: > >>What about rules/views/functions and who knows what else (domains?) >>might be dependant on the current type definition? > > > Yeah, I was just thinking about that this morning. We probably ought to > look for dependencies on the table rowtype as well as the individual > column. > > But on the other side of the coin, should we actually reject the ALTER > if we see a function that uses the rowtype as a parameter or result > type? Without looking inside the function, we can't really tell if the > ALTER will break the function or not. With looking, you can't necessarily. What if I'm building a query with EXECUTE or for that matter, what if I've written it in C? -- Richard Huxton Archonet Ltd
> FireBird: ALTER COLUMN <column> TYPE <type> > DB2: ALTER COLUMN <column> SET DATA TYPE <type>. > Oracle: MODIFY <column> <type> > MSSQL: ALTER COLUMN <column> <type> <constraints> > MySQL: Both Oracle and MSSQL > Sap: MODIFY <column> <type> > > Spec: Nothing (obvious) on changing column types > > MODIFY is horrible. It seems to drop all constraints, defaults, etc that > are not specified in the second definition. It is essentially a > replacement of the column. In Oracle MODIFY leaves omitted parts unchanged, syntax is actually ALTER TABLE <table> MODIFY (<column> <type> <default> <constraint>) I think the parentheses are optional if only one column is modified. Andreas