ALTER TABLE: change precision for VARCHAR column

[Victor Pyankov]

Hi,

I am using H2 1.2.145 (if it matters). And I try to change precision for VARCHAR column in very big table.

Some thing like this:

ALTER TABLE news ALTER COLUMN body VARCHAR(16777216) DEFAULT NULL;

for "body VARCHAR(64000) DEFAULT NULL".

I think that the precision for VARCHAR type is only a size constraint. But H2 makes no optimisations for such ALTER TABLE statement.

Can you suggest some nonstandard/lowlevel method for change or drop this constraint? Is it theoretically possible to change MetaRecord for example? Do you have any idea?

[Noel Grandin]

pretty much the only way at the moment would be to edit the on-disk file
using a hex editor.

By the way, if you're using varchar columns that big, you should really
be using a CLOB column.

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/esH-_jZEzaIJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Victor Pyankov]

вторник, 24 января 2012 г. 13:50:30 UTC+6 пользователь Noel Grandin написал:

pretty much the only way at the moment would be to edit the on-disk file
using a hex editor.

It is not good idea. We have many geographically distributed H2 databases and schema changes are propagated in fully automatic mode.

[Noel Grandin]

Yeah, then there really isn't an answer.

You are welcome to try adding this feature to H2, patches are always accepted with joy :-)

On 2012-01-24 10:15, Victor Pyankov wrote:

О©╫О©╫О©╫О©╫О©╫О©╫О©╫, 24 О©╫О©╫О©╫О©╫О©╫О©╫ 2012О©╫О©╫. 13:50:30 UTC+6 О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫ Noel Grandin О©╫О©╫О©╫О©╫О©╫О©╫О©╫:

pretty much the only way at the moment would be to edit the on-disk file
using a hex editor.

It is not good idea. We have many geographically distributed H2 databases and schema changes are propagated in fully automatic mode.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/snp29_xkju8J.

[Victor Pyankov]

вторник, 24 января 2012 г. 13:50:30 UTC+6 пользователь Noel Grandin написал:

By the way, if you're using varchar columns that big, you should really

be using a CLOB column.

Realy size is not so big. We need only to drop the constraint. This constraint was introduced at design time by mistake.

[vrota...@gmail.com]

Will the following work?

alter table YOUR_TABLE add COPY_OF_REAL_COLUMN varchar default REAL_COLUMN;
alter table YOUR_TABLE drop column REAL_COLUMN;
alter table YOUR_TABLE alter column COPY_OF_REAL_COLUMN to REAL_COLUMN;

Or a variation with update?

--
   Vasile Rotaru

[Noel Grandin]

try this patch, but please test thoroughly.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

[Victor Pyankov]

Noel Grandin wrote:

try this patch, but please test thoroughly.

Very interesting. I will check.

[Victor Pyankov]

Thank you very much, Noel! Your patch is exactly what I am looking for. I have done small refactoring. Also class for debugging was added. See attached file.

[Noel Grandin]

Excellent.
This will be in the next release of H2. Not sure when that will be, Thomas might still be on holiday.

[Victor Pyankov]

A slight change. I forgot about the STRING_IGNORECASE type.

25.01.2012 13:41, Noel Grandin пишет:

[Noel Grandin]

thanks, SVN updated

[Thomas Mueller]

Hi,

I have changed the code slightly now. In any case, the next version of H2 will support this feature.

Regards,

Thomas

[Victor Pyankov]

It is very good. Thank you!

31.01.2012 0:31, Thomas Mueller пишет: