> FireBird: ALTER COLUMN TYPE
> DB2: ALTER COLUMN SET DATA TYPE .
> Oracle: MODIFY
> MSSQL:ALTER COLUMN
> MySQL:Both Oracle and MSSQL
> Sap: MODIFY
>
> Spec: Nothing (obvious) on changing column types
>
> MODIFY is horrible. It seems to drop all constraints, def
Tom Lane wrote:
Robert Treat <[EMAIL PROTECTED]> 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 w
FireBird: ALTER COLUMN TYPE
DB2:ALTER COLUMN SET DATA TYPE .
Oracle: MODIFY
MSSQL: ALTER COLUMN
MySQL: Both Oracle and MSSQL
Sap:MODIFY
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
-
> 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"?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
Rod Taylor <[EMAIL PROTECTED]> 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 TYPE
> DB2: ALTER COLUMN SET DATA TYPE .
> Oracle: MODIFY
> MSSQL:ALTER COLUMN
>
> > 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 TYPE
DB2:ALTER COLUMN SET DATA TYPE .
Oracle: MODIFY
MSSQL: ALTER COLUMN
MySQL: Both Oracle and MSSQL
Sap:MODIFY
Spec: No
Christopher Kings-Lynne <[EMAIL PROTECTED]> 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
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 t
Christopher Kings-Lynne <[EMAIL PROTECTED]> 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 wro
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 di
Robert Treat <[EMAIL PROTECTED]> 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 ind
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 <[EMAIL PROTECTED]> writes:
> > >>>Does that mean I'll want to disable triggers while I do this?
> > >>
> > >>Hrm. Right now the code
Bruce Momjian <[EMAIL PROTECTED]> 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
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> 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 index
Tom Lane wrote:
> "Merlin Moncure" <[EMAIL PROTECTED]> 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 pres
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
"Merlin Moncure" <[EMAIL PROTECTED]> 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 inde
On Thu, 6 May 2004, Richard Huxton wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> >
> >>Richard Huxton <[EMAIL PROTECTED]> 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
"Merlin Moncure" <[EMAIL PROTECTED]> 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. a
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 na
Bruce Momjian wrote:
Tom Lane wrote:
Richard Huxton <[EMAIL PROTECTED]> 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 whic
Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> 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
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
---
Richard Huxton <[EMAIL PROTECTED]> 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 differ
Tom Lane wrote:
Bruce Momjian <[EMAIL PROTECTED]> 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 opera
Robert Treat <[EMAIL PROTECTED]> 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 t
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
> +
>
Bruce Momjian <[EMAIL PROTECTED]> 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 fold
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
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,
Tom Lane wrote:
> Stephen Frost <[EMAIL PROTECTED]> 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 VIE
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 TA
Stephen Frost <[EMAIL PROTECTED]> 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 alte
* Bruce Momjian ([EMAIL PROTECTED]) 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
> numb
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
comp
35 matches
Mail list logo
