Cambiando el tipo de una columna: de caracter a numérico y viceversa

3 comentarios

Lo ideal es siempre que una vez que has definido la cantidad de columnas de cada tabla y el tipo de cada una de esas columnas ya no necesites modificarlas. Pero lo ideal … no siempre es lo que ocurre en la vida real.

Entonces, ahora te encuentras con una situación en la cual consideras conveniente o imprescindible cambiar el tipo de una columna. Por ejemplo era VARCHAR y la quieres convertir a INTEGER. O era SMALLINT y la quieres convertir a CHAR, cosas así.

El comando deseado

Si tienes una columna que es de tipo CHAR o VARCHAR y la quieres cambiar a INTEGER muchos desearían que existiera algo similar a:

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiColumna
TYPE
   INTEGER

Pero tal comando no existe en Firebird, si lo intentas obtendrás el mensaje: “Cannot change datatype for column MiColumna from a character type to a non-character type.

¿Por qué, cuál es el problema? El problema es que Firebird no hace la conversión en el momento en que escribes un comando como el anterior sino en el momento en que consultas su valor (con un SELECT) o cambias su valor (con un UPDATE). Por lo tanto cuando escribes el comando ALTER TABLE él no puede saber si todos los valores pueden convertirse a INTEGER o no. Y entonces hace lo más seguro: rechaza el cambio.

Claro, tú puedes decir: “pero si solamente hay números enteros en esa columna, debería aceptar el cambio del tipo de datos”. Eso tú lo sabes, pero el Firebird no lo sabe porque no verifica que efectivamente así sea. ¿Y por qué no lo verifica? Porque tu tabla podría tener millones y millones de filas y verificarla podría demorar mucho tiempo, por lo tanto hace la sencilla: impide que modifiques el tipo de datos.

La solución

Ya hemos visto cual es el problema, ahora veremos que hay una solución. No es tan sencilla como escribir un comando ALTER TABLE pero funciona.

Paso 1. Verificar que todos los valores puedan ser convertidos a INTEGER

Evidentemente que el 100% de los valores deben poder convertirse a INTEGER, si hay al menos uno que no puede convertirse entonces primero hay que solucionar eso y luego continuar con los siguientes pasos.

SELECT
   CAST(MiColumna AS INTEGER) AS TodosSonNumerosEnteros
FROM
   MiTabla;

Si el SELECT anterior terminó con errores, y quieres ver cuales son las filas que tienen esos errores (o sea, las filas que no tienen números enteros) el siguiente artículo te muestra lo que puedes hacer para ver a esas filas problemáticas:

Validando que el contenido de un CHAR o VARCHAR sea numérico

Si el SELECT anterior terminó sin errores, entonces seguimos con el:

Paso 2. Renombrar la vieja columna

COMMIT;

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiColumna
TO
   ColumnaVieja;

Si esta columna tiene dependencias (es decir, si ha sido usada en alguna vista, stored procedure o trigger) entonces obtendrás un mensaje de error. Debes solucionar eso antes de poder continuar.

Este paso es muy importante porque te permite descubrir si la columna tiene dependencias.

Paso 3. Agregarle una columna a la tabla con el tipo de datos deseado

COMMIT;

ALTER TABLE
   MiTabla
ADD
   MiNuevaColumna INTEGER;

Paso 4. Agregarle los valores a la nueva columna

COMMIT;

UPDATE
   MiTabla
SET
   MiNuevaColumna = CAST(ColumnaVieja AS INTEGER)
WHERE
   ColumnaVieja IS NOT NULL;

Paso 5. Verificar que la nueva columna tenga los valores correctos

COMMIT;

SELECT
   (MiNuevaColumna + 1)
FROM
   MiTabla;

Si el Firebird te muestra algún mensaje de error (supuestamente todo debería estar ok, pero siempre lo mejor es asegurarse de que así sea) entonces tienes que buscar el motivo y solucionarlo, antes de continuar.

Paso 6. Borrar la columna vieja

Si el SELECT anterior finalizó sin errores, entonces ya podemos borrar con toda seguridad a la columna vieja, porque ya no la necesitaremos más.

COMMIT;

ALTER TABLE
   MiTabla
DROP COLUMN
   ColumnaVieja;

COMMIT;

Este paso es muy conveniente hacerlo para no confundirnos. Si no borramos a la columna vieja más adelante podríamos estar asignándole valores, algo que muy probablemente ya no deberíamos hacer.

Paso 7 (opcional). Renombrar a la nueva columna

Si deseas que la nueva columna tenga el mismo nombre que tenía la columna vieja, entonces:

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiNuevaColumna
TO
   MiColumna

COMMIT;

Paso 8 (opcional). Poner a la nueva columna en la misma posición que tenía la vieja columna

Si quieres que la nueva columna que acabas de crear se encuentre en la misma posición que tenía la columna vieja que acabas de borrar, entonces:

ALTER TABLE
   MiTabla
ALTER
   MiColumna
POSITION
   6;

COMMIT;

Si la vieja columna se encontraba en la posición 1, entonces después de POSITION escribirías 1. Si se encontraba en la posición 2, escribirías 2, y así sucesivamente. El 6 que se puso arriba es solamente un ejemplo.

Conclusión:

En Firebird no hay un comando que nos permita cambiar el tipo de datos de una columna. Y no lo hay por nuestra propia seguridad, de esa manera se evita que cometamos errores graves como convertir texto a INTEGER.

Sin embargo, si necesitamos hacerlo hay formas de conseguirlo. En este artículo se mostró la forma más segura de todas (no la única, pero sí la más segura y conveniente de utilizar).

Con el Paso 1. verificamos que todos los números sean enteros. Con el Paso 2. verificamos que no hay dependencias. Con el Paso 3. agregamos una columna que tiene el tipo de datos que deseamos tener. Con el Paso 4. le agregamos a la nueva columna los valores que debe tener. Con el Paso 5. verificamos que la nueva columna tenga los valores correctos. Con el Paso 6. borramos a la vieja columna, esto nos evitará confusiones más adelante. Con el Paso 7. renombramos a la nueva columna, para que se llame igual a la columna original. Con el Paso 8. ponemos a la nueva columna en la misma posición que tenía la columna original.

Siguiendo estos pasos te asegurarás que todo se ha realizado correctamente. Todo quedará como debe quedar.

Por lo tanto, si alguna vez necesitas cambiar el tipo de datos de una columna, ya sabes como lograrlo.

Artículos relacionados:

Validando que el contenido de un CHAR o VARCHAR sea numérico

El índice del blog Firebird21

El foro del blog Firebird21

La forma más fácil de cambiar un dominio

1 comentario

Lo correcto es que cuando definimos la estructura de una tabla todas sus columnas referencien a un dominio. ¿Pero qué pasa si luego queremos cambiar el dominio de una columna?

Supongamos que en muchas tablas tenemos columnas que referencian al dominio D_NOMBRE25 (que es un VARCHAR(25)) y luego queremos usar D_NOMBRE30 (que es un VARCHAR(30)). En ese caso, ningún problema, porque pasamos de un número menor (25 en este ejemplo) a un número mayor (30 en este ejemplo).

O tenemos columnas que usan un dominio D_INTEGER que es un INTEGER y queremos pasarlas a D_BIGINT que es un BIGINT. Ningún problema tampoco, ya que pasamos de INTEGER a BIGINT, de menor a mayor, está todo ok.

Sin embargo encontraremos un problema cuando queremos hacer al revés: pasar de un número mayor a un número menor. Si queremos pasar de VARCHAR(30) a VARCHAR(25) el Firebird no lo permitirá. Tampoco permitirá pasar de BIGINT a INTEGER.

¿Por qué no permite?

Porque se puede perder precisión.

Pero yo estoy seguro que los datos cabrán bien.

No importa eso. Supongamos que quieres pasar de VARCHAR(30) a VARCHAR(25) y tú estás completamente seguro que todos los datos que tienes caben en un VARCHAR(25), que ninguno necesita más caracteres. Puedes tener razón, pero el Firebird no verifica eso. Él simplemente no permite pasar de una precisión mayor a una precisión menor. Aunque el mayor número que se guarde en una columna sea el 12, tampoco te permitirá cambiar de INTEGER a SMALLINT, por ejemplo.

¿Y cómo lo soluciono si necesito hacer esos cambios?

Tienes tres formas, dos largas y una corta.

Una forma larga es revisar todas las dependencias de cada columna, eliminar o poner comentarios en las columnas relacionadas, borrar la columna problemática y luego volver a insertarla, ya con el nuevo tipo de datos. Una vez que está insertada, recrear o quitar los comentarios de todas las dependencias.

Otra forma larga es agregar una columna adicional, con el nuevo tipo de datos, copiar todos los datos de la columna original a la nueva columna, cambiar todas las referencias a la columna original por referencias a la nueva columna y finalmente borrar la columna original.

En ambos casos, si no se trata solamente de una o dos columnas sino de decenas o de centenas de columnas, el trabajo será laborioso y demandará bastante tiempo.

Hay una forma más práctica: crear una nueva Base de Datos con los cambios deseados.

Ejemplo: Cambiar el dominio D_IDENTIFICADOR que es un BIGINT a INTEGER

DOMINIOS1

Captura 1. Si haces clic en el imagen la verás más grande

DOMINIOS2

Captura 2. Si haces clic en el imagen la verás más grande

DOMINIOS3

Captura 3. Si haces clic en el imagen la verás más grande

despues “Next”, “Next”, “Next”, “Finish” y “Close”.

Al finalizar el proceso, tendrás un script con el contenido de tu Base de Datos. Ahora ya es simplemente cuestión de buscar a D_IDENTIFICADOR y  cambiar su tipo de BIGINT a INTEGER.

DOMINIOS4

Captura 4. Si haces clic en el imagen la verás más grande

Una vez hecho eso, se ejecuta el script y listo, asunto solucionado.

DOMINIOS5

Captura 5. Si haces clic en el imagen la verás más grande

entonces, sin importar si el dominio D_IDENTIFICADOR había sido usado 40 veces, 70 veces, o lo que fuera, al crear una nueva Base de Datos su tipo de datos es el tipo de datos que queríamos que tuviera. Realizar todos estos pasos no tarda más de uno o dos minutos, una ganancia considerable de tiempo comparado con los otros dos métodos.

Advertencia:

Para asegurarte que todos los metadatos se hayan copiado, siempre es importante que hagas esa verificación.

DOMINIOS6

Captura 6. Si haces clic en el imagen la verás más grande

O sea, si la Base de Datos original tiene 44 dominios, la Base de Datos nueva también debe tener 44 dominios. Si la original tiene 80 tablas la nueva también debe tener 80 tablas, etc.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Usando NUMERIC y DECIMAL

3 comentarios

En Firebird tenemos dos tipos de datos numéricos que tienen una cantidad fija de lugares decimales: NUMERIC y DECIMAL. También tenemos dos tipos de datos numéricos cuya cantidad de lugares decimales es variable (en Matemática se les dice “flotantes”) que son: FLOAT y DOUBLE PRECISION.

En este artículo nos referiremos a NUMERIC y a DECIMAL, los cuales siempre tienen una cantidad fija de lugares decimales.

A la cantidad total de dígitos se la llama precisión y se la representa con la letra “p“. A la cantidad de lugares decimales se le llama escala (scale, en inglés) y se la representa con la letra “s“.

Por lo tanto, si una columna está definida como NUMERIC(5, 2) se la representa así: ppp.ss

La precisión debe estar entre 1 y 18. La escala debe estar entre 0 y 18. La escala siempre debe ser menor o igual que la precisión, nunca puede ser mayor.

Si quieres, puedes declarar una columna NUMERIC o DECIMAL sin especificar la precisión y en ese caso será convertida a INTEGER.

Lo importante a recordar es que en NUMERIC y en DECIMAL la parte decimal es siempre exacta. Se necesita que la parte decimal sea exacta cuando se trata de dinero o de cualquier otro número que resulta de contar o de realizar operaciones aritméticas sobre unidades.

Como el resultado de multiplicar o dividir números que tienen una parte decimal fija es predecible, es el motivo por el cual se los utiliza cuando se trata de dinero. A tu usuario no le puedes decir: “el cliente Juan Pueblo está debiendo más o menos 100 dólares”, el usuario quiere conocer la cantidad exacta de la deuda de Juan Pueblo, eso de “más o menos 100 dólares” no le va a gustar.

Como tanto NUMERIC como DECIMAL usan una escala se los suele llamar “escalados“. Otros sinónimos son: “números de punto fijo” o “números de coma fija“.

En el estandar SQL-92 tanto NUMERIC como DECIMAL restringen al número almacenado a estar dentro de la escala elegida. La diferencia entre ambos tipos de datos es en la forma en la cual la precisión es restringida. Cuando el tipo de dato es NUMERIC, la precisión es exactamente la declarada. En cambio, cuando el tipo de datos es DECIMAL la precisión es al menos igual a la declarada. Eso significa que NUMERIC es más estricto que DECIMAL.

Sin embargo, en Firebird NUMERIC y DECIMAL son idénticos excepto cuando la precisión es menor que 5. Ambos cumplen con el estandar SQL-92 para el tipo de datos DECIMAL y eso significa que NUMERIC no cumple con el estandar de SQL-92.

Si además de usar Firebird usas o has usado o piensas usar otro motor SQL entonces para el dinero utiliza NUMERIC porque es el recomendado según el estandar SQL-92. Si solamente usas y piensas usar Firebird, entonces puedes usar cualquiera de los dos.

Internamente, Firebird almacena a estos tipos de datos como SMALLINT, como INTEGER, o como BIGINT, dependiendo de la precisión declarada.

La forma de almacenarlos internamente es la siguiente:

  • Se almacena un número entero, o sea que no tiene decimales
  • Se almacena la escala a un factor menor que cero que representa a un exponente de 10

Cuando el número debe ser utilizado en una consulta o en una operación matemática se lo obtiene multiplicando el número entero almacenado por 10 elevado a la escala almacenada.

Ejemplo:

Un número ha sido declarado como NUMERIC(4, 3) y por lo tanto Firebird lo almacena como un SMALLINT.

Quieres guardar en esa columna el número 7,2348 pero como la escala de la columna es 3 y la escala del número que quieres guardar es 4 el Firebird lo redondea a 7,235

Por lo tanto, el Firebird guarda el número 7235 y el número -3

Cuando quieres utilizar ese número el Firebird multiplica 7235 por 10 elevado a la -3 y por lo tanto obtienes 7,235 ya que multiplicar un número por 10 elevado a la -3 es lo mismo que dividirlo por 1000. Y 7235 dividido por 1000 es igual a 7,235

Rango de valores

Tipo de datos                Precisión    Almacenado como

NUMERIC             1 a 4    SMALLINT
DECIMAL             1 a 4    INTEGER
NUMERIC y DECIMAL   5 a 9    INTEGER
NUMERIC y DECIMAL  10 a 18   BIGINT

El tipo de datos NUMERIC

El formato del tipo de datos NUMERIC es:

NUMERIC(p, s)

Por ejemplo, NUMERIC(4, 2) define a un número que puede tener como máximo 4 dígitos, de los cuales 2 de ellos sí o sí corresponderán a la parte decimal.

Eso significa que el número 1,23 será almacenado como 1,23 pero el número 1,234 será almacenado como 1,23 porque la escala es 2 y por lo tanto no puede almacenarse con más de 2 dígitos decimales. También 1,2345678 será almacenado como 1,23. El número 567,89 no podrá ser almacenado porque la parte entera tiene 3 dígitos y solamente debería tener 2 dígitos, si intentamos almacenarlo el Firebird nos mostrará un error de overflow (número mayor que el máximo permitido para esa columna).

Sin embargo, como NUMERIC se guarda internamente como un SMALLINT y en SMALLINT se puede guardar desde el número -32.768 hasta el número 32.767 al declarar a una columna como NUMERIC(4, 2) el mayor número que podemos almacenar en ella no es 99,99 como podría suponerse viendo la declaración sino 327,67. Cuidado con esto porque NUMERIC no cumple el estándar SQL-92 ya que según ese estándar el mayor número tendría que ser 99,99.

El tipo de datos DECIMAL

El formato del tipo de datos DECIMAL es:

DECIMAL(p, s)

Similarmente a NUMERIC el mayor número que puede guardarse en una columna de este tipo no es el declarado. Por ejemplo si una columna se declara como DECIMAL(4, 1) uno podría pensar que el máximo número permitido sería 999,9 pero sin embargo el mayor número permitido es 214.748.364,7 y el menor número permitido es -214.748.364,8

¿Por qué eso? porque el tipo de datos DECIMAL se guarda internamente como un INTEGER y el rango de valores de INTEGER es de -2.147.483.648 hasta 2.147.483.647 y como en este ejemplo la escala es 1 entonces para hallar el rango de valores se divide por 10. Si la escala fuera 2 se dividiría por 100, si fuera 3 se dividiría por 1.000 y así sucesivamente.

Advertencia

El hecho de que puedas almacenar en una columna números mayores a los declarados para esa columna no significa que debas hacerlo. En futuras versiones de Firebird este comportamiento puede cambiar para adecuarse al estándar y si estás dependiendo de algo que está fuera del estándar podrás encontrarte con graves problemas más adelante.

Comportamiento de NUMERIC y DECIMAL en operaciones aritméticas

División

La precisión del resultado siempre es 18. La escala del resultado es la suma de las escalas del dividendo y del divisor. Esto implica que la escala del cociente siempre será mayor que las del dividendo y del divisor. Debes tener cuidado con esto si los números que empleas tienen muchos decimales o podrías sobrepasar la escala máxima que es 18 o necesitar una precisión mayor que 18 y en ambos casos eso te ocasionaría un error de overflow.

SELECT
   1234567.89 / 54321.2345
FROM
   RDB$DATABASE

El resultado de esa división es 22,727169 que como puedes ver tiene 6 dígitos decimales. ¿Por qué? porque el dividendo tiene 2 dígitos decimales y el divisor tiene 4 dígitos decimales y si sumas 2 + 4 obtienes 6.

SELECT
   1 / 3
FROM
   RDB$DATABASE

Aquí, uno esperaría que el resultado fuera 0,33333333 sin embargo el resultado es 0 (cero). ¿Por qué? porque tanto el dividendo como el divisor son números enteros y la escala de los números enteros es cero. Y como la escala del cociente es la suma de la escala del dividendo más la escala del divisor esa escala también debe ser cero (0 + 0 = 0).

Si queremos efectuar la división de arriba pero que el resultado tenga dos decimales entonces deberíamos escribir:

SELECT
   1.00 / 3
FROM
   RDB$DATABASE

o también podríamos escribir:

SELECT
   (1 + 0.00) / 3
FROM
   RDB$DATABASE

Multiplicación

Al igual que en la división, la escala del resultado es la suma de las escalas de los operandos.

Suma y Resta

La escala del resultado es la mayor de las dos escalas.

SELECT
   123.45 + 678.9
FROM
   RDB$DATABASE

El resultado de esta suma es 802,35 y como puedes ver la escala es 2. ¿Por qué la escala es 2? porque las escalas de los sumandos son 2 y 1. Como 2 es mayor que 1 entonces la escala del resultado es 2.

La precisión siempre es 18.

Artículos relacionados

Entendiendo NUMERIC y DECIMAL

Confusiones comunes al declarar una columna como NUMERIC o DECIMAL

Determinando la precisión y la escala de una columna NUMERIC

Eligiendo el tipo de datos numérico que se usará en una columna

El índice del blog Firebird21

El foro del blog Firebird21

Eligiendo el tipo de dato numérico que se usará en una columna

13 comentarios

Cuando defines que los datos que se guardarán en una columna serán de tipo numérico debes decidir también cual de los tipos numéricos que posee el Firebird emplearás. Estas son tus opciones:

  • SMALLINT
  • INTEGER
  • BIGINT
  • NUMERIC
  • DECIMAL
  • FLOAT
  • DOUBLE PRECISION

Como ves, tienes 7 posibilidades, cada una de ellas será la más apropiada para alguna circunstancia, así que ahora debes decidirte por una de esas 7 alternativas.

¿Necesitas guardar números exactos o números aproximados?

A veces, el número que guardas en un columna debe ser exacto, no se admite por ningún motivo que sea aproximado. Ese es el caso del dinero. Allí, tendrás que saber hasta el último centavo cuanto dinero te debe un cliente. O cuanto dinero le cobraste a ese cliente. No puedes responder con algo como: “aproximadamente 100 dólares”, sino que debes responder con el monto exacto.

Otras veces, un número aproximado es apropiado, por ejemplo: ¿cuánto pesó la última bolsa de harina que se puso en la balanza? si respondes con: 52 kilogramos y 245 gramos estará ok, si no eran 245 gramos sino 246 gramos ó 247 gramos no hay problema. Lo mismo que si en lugar de ser 245 gramos eran en realidad 245,1304 gramos. No hay problema.

Necesitarás guardar valores exactos cuando estés tratando con cantidades (dinero, unidades, cajas, precios, etc.) o sea algo que se puede contar y podrás usar valores aproximados cuando puedas medirlo (longitud, volumen, peso, presión, voltaje, etc.)

¿Puedes contarlo? entonces necesitarás números exactos

¿Puedes medirlo? entonces podrás usar números aproximados

¿Cuáles son los tipos de datos numéricos exactos?

  • SMALLINT
  • INTEGER
  • BIGINT
  • NUMERIC
  • DECIMAL

Estos tipos de datos pueden guardar números enteros y también números con decimales. La precisión (representada con la letra “p”) es la cantidad de dígitos significativos. La escala (representada con la letra “s”) es la cantidad de dígitos que se encuentran a la derecha de la coma decimal.

Por ejemplo: DECIMAL(8, 3) significa ppppp.sss

Aquí, hay 8 dígitos en total y como 3 de ellos corresponden a la parte decimal eso implica que hay 5 en la parte entera.

¿Cuáles son los tipos de datos numéricos aproximados?

  • FLOAT
  • DOUBLE PRECISION

Estos tipos de datos guardan números decimales y números con exponentes.

Los últimos decimales de estos números pueden variar cuando se hacen operaciones matemáticas, por eso nunca se los debe usar para guardar en ellos cantidades que deben ser exactas.

Rangos de valores

TIPO DE DATOS       DESDE              HASTA                 .
SMALLINT            -32768             32767 
INTEGER             -2147483648        2147483647 
BIGINT              -2 * 10 ^ 63       2 * 10 ^ 63 - 1 
NUMERIC             -2 * 10 ^ 63       2 * 10 ^ 63 - 1 
DECIMAL             -2 * 10 ^ 63       2 * 10 ^ 63 - 1 
FLOAT               1,175 * 10 ^ -38   3,402 * 10 ^ 38 
DOUBLE PRECISION    2,225 * 10 ^ -308  1,797 * 10 ^ 308

Ejemplo que muestra el error de precisión cuando se usa FLOAT

Este ejemplo nos mostrará por qué no debemos guardar valores que deben ser exactos en una columna de tipo FLOAT

UPDATE
   PRODUCTOS
SET
   PRD_PREVTA = 1.23456
WHERE
   PRD_IDENTI = 215

Este comando UPDATE guarda el número 1.23456 en la columna PRD_PREVTA (precio de venta) de la tabla PRODUCTOS para el producto cuyo identificador es 215.

Ahora, queremos ver el precio de venta que guardamos con el comando UPDATE. Deberíamos ver 1.23456 ¿será eso lo que veremos?

SELECT
   PRD_PREVTA
FROM
   PRODUCTOS
WHERE
   PRD_IDENTI = 215

La imagen de abajo es lo que obtenemos al ejecutar este SELECT.

PREVTA

(haciendo click en la imagen la verás más grande)

Pues no, no obtuvimos 1.23456 sino un número un poco mayor, un número diferente.

Mucho cuidado con esto, cuando se trata de dinero esta clase de errores son totalmente inaceptables.

Artículo relacionado:

https://firebird21.wordpress.com/2013/03/04/entendiendo-numeric-y-decimal/