db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: problem with ALTER COLUMN DEFAULT on VARCHAR column
Date Fri, 23 Feb 2007 09:23:57 GMT
Hi Bryan,

I think you have identified the problem, but your guess was the wrong 
way round.

If the column is empty (SELECT MAX(COL_NAME) FROM TABLE_NAME returns 
NULL) then setting the default works fine.

If however the column contains data then the SELECT MAX... returns a 
string value.

No, the column is not automatically generated. Just a standard VARCHAR.


Tim


Bryan Pendleton wrote:
> 
>> alter table TABLE_NAME alter column COL_NAME DEFAULT 'new value'
>>
>> and with some VARCHAR columns I get an error like this:
>>
>> Invalid character string format for type long.
> 
> Well, I'm not sure what's causing this, but here's what I
> think is going on, maybe it makes sense: when you alter the
> default for a column which is automatically generated, the
> code appears to want to compute the current maximum value for
> that column, and internally it generates and executes the statement:
> 
>   SELECT MAX(COL_NAME) FROM TABLE_NAME
> 
> For some reason, this MAX query did not return a numeric value.
> 
> Perhaps the table is empty, and so the MAX query returned a NULL?
> 
> Does any of this make sense? Are you altering the default for
> an automatically generated VARCHAR column? If so, can you try
> running the SELECT MAX query by hand yourself prior to running
> the ALTER TABLE statement and see what the SELECT MAX query returns?
> 
> thanks,
> 
> bryan
> 
> 


Mime
View raw message