CODEWITHSUNDEEP
sqldatabaseinformix
Abdullah Nasir
Abdullah Nasir

Reputation: 74

Changing data type of column in a table from TEXT to LVARCHAR(10000)

I want to change/modify the data type TEXT of column batch_calc_query in a table d_etl_queries to LVARCHAR(10000). I am doing this by executing the following command:

ALTER TABLE d_etl_queries MODIFY (batch_calc_query LVARCHAR(10000));

But, it gives me the following error:

9633: ALTER TABLE can not modify column (batch_calc_query) type.  Need a cast from the current type

Is there any way I can avoid this error?

Upvotes: 2

Views: 956

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753455

Running the two statements:

CREATE TABLE blob_to_lvarchar(s SERIAL NOT NULL PRIMARY KEY, b TEXT IN TABLE);
ALTER TABLE blob_to_lvarchar MODIFY b LVARCHAR(10000);

generates the advertised error. There isn't an easy way around that — you'd have to add a cast to the system (CREATE CAST) backed up with the appropriate C function to make the change.

You are going to need to unload the table, drop it, recreate it with the correct column type, and reload the table.

Upvotes: 4

Related Questions