CHANGE COLUMN DATA TYPE?

older
Announcement: New Jan2022-SP2...

Alberto Ferrari

21 Apr 2022 21 Apr '22

1:38 p.m.

Hi all, I need to change the data type of a column. (I know the trick of creating a new temp column, copy the data from old, etc...) But I expected newer versions already have a command equivalent to MySql "CHANGE COLUMN" or similar, but I can't found in monetdb documentation. All examples of "ALTER COLUMN" are for setting other attributes. Anybody knows a specific command to do what I need? Kind regards, Alberto

Attachments:

attachment.html (text/html — 1010 bytes)

Show replies by date

Alberto Ferrari

21 Apr 21 Apr

1:56 p.m.

I just found: "Change of the data type of a column is not supported. Instead use command sequence: ALTER TABLE tbl ADD COLUMN new_column _new_data_type_; UPDATE tbl SET new_column = CONVERT(old_column, _new_data_type_); ALTER TABLE tbl DROP COLUMN old_column RESTRICT; ALTER TABLE tbl RENAME COLUMN new_column TO old_column" Maybe you could add and sql command that internally performs all those steps in just one step? regards El jue, 21 abr 2022 a la(s) 10:38, Alberto Ferrari (aferrari@starconnecting.com) escribió:

...

Hi all, I need to change the data type of a column. (I know the trick of creating a new temp column, copy the data from old, etc...) But I expected newer versions already have a command equivalent to MySql "CHANGE COLUMN" or similar, but I can't found in monetdb documentation. All examples of "ALTER COLUMN" are for setting other attributes. Anybody knows a specific command to do what I need?

Kind regards, Alberto

Ying Zhang

2:14 p.m.

Hai Alberto, Please feel free to add a feature request at https://github.com/monetdb/monetdb/issues That helps us keeping track of user wishes and plan our roadmap accordingly. Thanks Jennie

...

On 21 Apr 2022, at 15:56, Alberto Ferrari wrote:

I just found: "Change of the data type of a column is not supported. Instead use command sequence: ALTER TABLE tbl ADD COLUMN new_column _new_data_type_; UPDATE tbl SET new_column = CONVERT(old_column, _new_data_type_); ALTER TABLE tbl DROP COLUMN old_column RESTRICT; ALTER TABLE tbl RENAME COLUMN new_column TO old_column"

Maybe you could add and sql command that internally performs all those steps in just one step?

regards

El jue, 21 abr 2022 a la(s) 10:38, Alberto Ferrari (aferrari@starconnecting.com) escribió:

...
Hi all, I need to change the data type of a column. (I know the trick of creating a new temp column, copy the data from old, etc...) But I expected newer versions already have a command equivalent to MySql "CHANGE COLUMN" or similar, but I can't found in monetdb documentation. All examples of "ALTER COLUMN" are for setting other attributes. Anybody knows a specific command to do what I need?

Kind regards, Alberto

_______________________________________________ users-list mailing list -- users-list@monetdb.org To unsubscribe send an email to users-list-leave@monetdb.org

Alberto Ferrari

2:21 p.m.

thank you Jennie El jue, 21 abr 2022 a la(s) 11:14, Ying Zhang (Y.Zhang@cwi.nl) escribió:

...

Hai Alberto,

Please feel free to add a feature request at https://github.com/monetdb/monetdb/issues That helps us keeping track of user wishes and plan our roadmap accordingly.

Thanks Jennie

...
On 21 Apr 2022, at 15:56, Alberto Ferrari wrote:

I just found: "Change of the data type of a column is not supported. Instead use command sequence: ALTER TABLE tbl ADD COLUMN new_column _new_data_type_; UPDATE tbl SET new_column = CONVERT(old_column, _new_data_type_); ALTER TABLE tbl DROP COLUMN old_column RESTRICT; ALTER TABLE tbl RENAME COLUMN new_column TO old_column"

Maybe you could add and sql command that internally performs all those steps in just one step?

regards

El jue, 21 abr 2022 a la(s) 10:38, Alberto Ferrari (aferrari@starconnecting.com) escribió:

...
Hi all, I need to change the data type of a column. (I know the trick of creating a new temp column, copy the data from old, etc...) But I expected newer versions already have a command equivalent to MySql "CHANGE COLUMN" or similar, but I can't found in monetdb documentation. All examples of "ALTER COLUMN" are for setting other attributes. Anybody knows a specific command to do what I need?

Kind regards, Alberto

_______________________________________________ users-list mailing list -- users-list@monetdb.org To unsubscribe send an email to users-list-leave@monetdb.org

_______________________________________________ users-list mailing list -- users-list@monetdb.org To unsubscribe send an email to users-list-leave@monetdb.org

Alberto Ferrari

2:41 p.m.

created in https://github.com/MonetDB/MonetDB/issues/7288 including the able to change the column position/ordinal in the table. Regards. El jue, 21 abr 2022 a la(s) 11:21, Alberto Ferrari (aferrari@starconnecting.com) escribió:

...

thank you Jennie

El jue, 21 abr 2022 a la(s) 11:14, Ying Zhang (Y.Zhang@cwi.nl) escribió:

...
Hai Alberto,

Please feel free to add a feature request at https://github.com/monetdb/monetdb/issues That helps us keeping track of user wishes and plan our roadmap accordingly.

Thanks Jennie

...
On 21 Apr 2022, at 15:56, Alberto Ferrari wrote:

I just found: "Change of the data type of a column is not supported. Instead use command sequence: ALTER TABLE tbl ADD COLUMN new_column _new_data_type_; UPDATE tbl SET new_column = CONVERT(old_column, _new_data_type_); ALTER TABLE tbl DROP COLUMN old_column RESTRICT; ALTER TABLE tbl RENAME COLUMN new_column TO old_column"

Maybe you could add and sql command that internally performs all those steps in just one step?

regards

El jue, 21 abr 2022 a la(s) 10:38, Alberto Ferrari (aferrari@starconnecting.com) escribió:

...
Hi all, I need to change the data type of a column. (I know the trick of creating a new temp column, copy the data from old, etc...) But I expected newer versions already have a command equivalent to MySql "CHANGE COLUMN" or similar, but I can't found in monetdb documentation. All examples of "ALTER COLUMN" are for setting other attributes. Anybody knows a specific command to do what I need?

Kind regards, Alberto

_______________________________________________ users-list mailing list -- users-list@monetdb.org To unsubscribe send an email to users-list-leave@monetdb.org

_______________________________________________ users-list mailing list -- users-list@monetdb.org To unsubscribe send an email to users-list-leave@monetdb.org

981

Age (days ago)

981

Last active (days ago)

List overview

Download

4 comments

2 participants

participants (2)

Alberto Ferrari
Ying Zhang