Alternative for "Conversion from base type XXX to YYY is not supported."

[Lucas Schatz]

Hi.

When I try to "downgrade" a field in a table, a get a not supported conversion error.

I know that I could:

1. create a new field, update its value with the converted value
2. delete or rename old field
3. rename new field with expected name.

But with tons of triggers, SPs, views and functions it's impracticable.

Is it possible to implement a resource to overcome this error?

Example:

CREATE TABLE TEST_TABLE (
    NUMERIC_FIELD FLOAT,
    STRING_FIELD VARCHAR(10));

ALTER TABLE TEST_TABLE ALTER COLUMN STRING_FIELD TYPE varchar(5);

unsuccessful metadata update.
ALTER TABLE TEST_TABLE failed.
New size specified for column STRING_FIELD must be at least 10 characters.

I think something like:
ALTER TABLE TEST_TABLE ALTER COLUMN NUMERIC_FIELD TYPE integer CONVERTING  WITH round(value);

ALTER TABLE TEST_TABLE ALTER COLUMN STRING_FIELD TYPE varchar(5)  CONVERTING  WITH substring(value from 1 for 5);

I don't know if there is another easier way to do it, if exists, please let me know!

Thanks for any help, advise or implementation! :D

[Svein Erling Tysvær]

You haven't told us WHY you want to reduce the max field size, but wouldn't a simple alternative be adding a check constraint that raises an exception if the field is longer than varchar(5) and then run UPDATE TEST_TABLE SET STRING_FIELD = substring( STRING_FIELD from 1 for 5) to ascertain already existing values adhered? Sure, the field definition would still be varchar(10), but you would only be allowed to use the first 5 of them.

HTH,

Set

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/0b2b5275-55b4-4178-8074-c21c2c2d5b59n%40googlegroups.com.

[Mark Rotteveel]

On 14-08-2020 04:55, Lucas Schatz wrote:
> I think something like:
> ALTER TABLE TEST_TABLE ALTER COLUMN NUMERIC_FIELD TYPE integer
> CONVERTING  WITH round(value);
> ALTER TABLE TEST_TABLE ALTER COLUMN STRING_FIELD TYPE varchar(5)
> CONVERTING  WITH substring(value from 1 for 5);
>
> I don't know if there is another easier way to do it, if exists, please
> let me know!
> Thanks for any help, advise or implementation! :D

Unfortunately Firebird doesn't have such a feature. I'd recommend
creating a ticket in the tracker for this.

PostgreSQL offers something like this with its USING clause:
ALTER TABLE name
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE
collation ] [ USING expression ]

"""
SET DATA TYPE

This form changes the type of a column of a table. Indexes and
simple table constraints involving the column will be automatically
converted to use the new column type by reparsing the originally
supplied expression. The optional COLLATE clause specifies a collation
for the new column; if omitted, the collation is the default for the new
column type. The optional USING clause specifies how to compute the new
column value from the old; if omitted, the default conversion is the
same as an assignment cast from old data type to new. A USING clause
must be provided if there is no implicit or assignment cast from old to
new type.
"""
See: https://www.postgresql.org/docs/12/sql-altertable.html

Mark
--
Mark Rotteveel