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

Anuncios

Guardando las columnas BLOB en tablas separadas

5 comentarios

Las columnas de tipo BLOB nos permiten guardar en ellas contenido muy grande: típicamente mayor a 32765 bytes. ¿Por qué ese número? Porque una columna de tipo VARCHAR puede guardar hasta 32765 bytes entonces usaríamos una columna BLOB cuando necesitamos un espacio de almacenamiento mayor.

El contenido de las columnas de tipo VARCHAR puede ser indexado, el contenido de las columnas de tipo BLOB no puede ser indexado.

Entonces, si estamos seguros de que la columna siempre contendrá menos de 32765 bytes lo conveniente es que sea de tipo VARCHAR, para poder indexarla cuando lo deseemos.

Ok, ya estudiamos el caso y decidimos que necesitamos una columna de tipo BLOB, ¿en cuál tabla la guardamos?

Aquí tenemos dos posibilidades:

  1. Guardarla en la misma tabla que los demás datos
  2. Guardarla en una tabla separada

Por ejemplo, tenemos una tabla de EMPLEADOS y queremos guardar las fotografías de los empleados. O tenemos una tabla de VENTAS y queremos guardar las facturas escaneadas.

Si elegimos la posibilidad 1. entonces debemos olvidarnos por completo de escribir algo como SELECT * FROM EMPLEADOS, o como SELECT * FROM VENTAS, porque el tiempo que se demorará en obtener el conjunto resultado puede ser muy grande.

Por el contrario, si elegimos la posibilidad 2. nunca tendremos ese problema ya que la columna BLOB se encuentra en otra tabla y para leer su contenido debemos específicamente escribir un JOIN.

Elegir la posibilidad 1. tiene las ventajas de que nuestros SELECTs son más sencillos de escribir y que usamos menos espacio en el disco duro pero tiene la desventaja de que los resultados se obtienen más lentamente. Y nuestra prioridad siempre debe estar enfocada en conseguir la máxima velocidad posible.

Ejemplo. Guardar las fotografías de los empleados

BLOB1

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

En la Captura 1. vemos la estructura de la tabla EMPLEADOS.

BLOB2

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

Y en la Captura 2. vemos la estructura de la tabla EMPLEADOSFOT, que es la tabla donde guardamos las fotografías de los empleados.

Evidentemente al usar dos tablas estamos ocupando más espacio en el disco duro porque tenemos dos columnas (EMF_IDENTI y EMF_IDECAB en nuestro ejemplo) que no necesitaríamos si usáramos una sola tabla.

Al usar dos tablas cuando consultamos a la tabla de EMPLEADOS no recuperamos las fotografías, para recuperarlas debemos escribir un JOIN similar a éste:

SELECT
   E.EMP_IDENTI,
   E.EMP_NOMBRE,
   E.EMP_APELLD,
   F.EMF_FOTOGR
FROM
   EMPLEADOS    E
LEFT JOIN
   EMPLEADOSFOT F
      ON E.EMP_IDENTI = F.EMF_IDECAB

Empleamos la cláusula LEFT JOIN para poder obtener los datos de todos los empleados, inclusive los de aquellos que no tienen fotografías.

Conclusión:

Si necesitas tener columnas de tipo BLOB entonces lo conveniente y lo recomendable es guardarlas en tablas separadas. Relacionarás la tabla padre y la tabla hija con un identificador común para escribir la cláusula JOIN cuando necesites recuperar la columna de tipo BLOB, tal como se vio en el SELECT de arriba.

Si guardas los datos y la columna de tipo BLOB en una sola tabla entonces tus SELECTs serán más sencillos de escribir y ahorrarás espacio en el disco duro. Pero tus consultas serán más lentas.

Y tu prioridad debería ser tener consultas rápidas, ya que en esta época usar unos pocos bytes más no importa.

Artículos relacionados:

Usando columnas de tipo BLOB

El índice del blog Firebird21

 

¿CHAR o VARCHAR? ¿cuál es mejor?

4 comentarios

Cuando declaras una columna alfanumérica puedes elegir entre CHAR y VARCHAR. ¿Cuál de esos tipos de datos es mejor?

Guardando columnas CHAR en .DBF y en Paradox

En las tablas .DBF y en las tablas de Paradox, si declaras una columna como CHAR y guardas en esa columna menos caracteres que los declarados se la completa con espacios en blanco.

Ejemplo:

Si una columna de una tabla .DBF está declarada como CHAR(20) y guardas en esa columna:

‘HOLA’, se le agregan 16 espacios en blanco, para completar los 20 declarados

‘ASUNCIÓN’, se le agregan 12 espacios en blanco para guardar los 20 declarados

‘AMERICA’ se le agregan 13 espacios en blanco para completar los 20 declarados

Guardando columnas CHAR en Firebird

Firebird no agrega espacios en blanco al final porque cuando se guarda una fila, la fila completa (con todas sus columnas CHAR, VARCHAR, INTEGER, DATE, TIME, etc.) es comprimida usando el algoritmo RLE.

Se hace así para ahorrar espacio en el disco duro porque la fila comprimida ocupa menos espacio que la fila sin comprimir.

Ejemplo:

Una columna de una tabla Firebird está declarada como CHAR(20) y guardas en esa columna:

‘HOLA’, ocupa 4 caracteres

‘ASUNCIÓN’, ocupa 8 caracteres

‘AMÉRICA’, ocupa 7 caracteres

Como puedes ver Firebird solamente guarda los datos significativos, no agrega espacios en blanco al final.

Guardando columnas VARCHAR en Firebird

Las columnas de tipo VARCHAR ocupan 2 bytes más que los declarados porque se usan 2 bytes para conocer la longitud de la cadena guardada.

Ejemplo:

Una columna de una tabla Firebird está declarada como VARCHAR(20) y guardas en esa columna:

‘HOLA’, ocupa 6 caracteres (2 que indican la longitud más 4 de la palabra ‘HOLA’)

‘ASUNCIÓN’, ocupa 10 caracteres (2 que indican la longitud más 8 de la palabra ‘ASUNCIÓN’)

‘AMÉRICA’, ocupa 9 caracteres (2 que indican la longitud más 7 de la palabra ‘AMÉRICA’)

Ahorrando espacio en el disco duro

Si lo que necesitas es ahorrar espacio en el disco duro entonces CHAR es más eficiente que VARCHAR porque como has visto cada columna CHAR ocupa 2 bytes menos que su correspondiente columna VARCHAR.

Espacio ocupado en la memoria RAM

En ambos casos la columna ocupa el espacio declarado.

Ejemplo:

Una columna CHAR(20) ocupa 20 bytes en la memoria RAM

Una columna VARCHAR(20) ocupa 20 bytes en la memoria RAM

Consultando columnas CHAR y VARCHAR

Cuando ejecutas un comando SELECT sobre una columna CHAR Firebird le agrega los espacios en blanco necesarios para completar la longitud declarada.

Cuando ejecutas un comando SELECT sobre una columna VARCHAR Firebird ignora a los dos primeros caracteres (los que indican la longitud) y devuelve el resto de la cadena.

Por lo tanto, al usar CHAR se ahorran 2 bytes pero al hacer el SELECT de esa columna se pierde tiempo en rellenarla con los espacios en blanco faltantes.

Decidiendo entre CHAR y VARCHAR

Como hemos visto, al declarar una columna como CHAR se ahorra espacio en el disco duro (2 bytes menos que si la declaramos como VARCHAR) pero las consultas que involucran a columnas CHAR son más lentas que las que involucran a columnas VARCHAR porque las columnas CHAR deben ser rellenadas con espacios en blanco antes de ser mostradas.

Esos 2 bytes que se ahorran al declarar a la columna como CHAR, en esta época y con los discos duros gigantescos disponibles, pueden ser irrelevantes y no degradarán el rendimiento de la Base de Datos. La excepción es cuando la cantidad de caracteres a almacenar es pequeña, por ejemplo declarar una columna como VARCHAR(3) es un error porque en realidad se guardan en el disco duro 5 bytes (2 de la longitud más 3 declarados)  cuando usando CHAR(3) solamente necesitaríamos 3 bytes y además al ser la longitud tan pequeña (sólo 3 bytes) VARCHAR no sería más rápido que CHAR.

Por lo tanto:

  • Si la cantidad de caracteres a guardar en una columna es fija y menor que 80, usar CHAR
  • Si la cantidad de caracteres a guardar en una columna es fija o es variable y esa cantidad es menor o igual que 10, usar CHAR
  • Si la cantidad de caracteres a guardar en una columna es variable y es mayor que 10, usar VARCHAR
  • Si la cantidad de caracteres a guardar en una columna es variable y mayor que 10.000 suele ser preferible usar un BLOB de texto

Ejemplos donde se debería usar CHAR

  • Guardar el código del Estado (‘AK’, ‘AZ’, ‘NY’, ‘SC’, ‘TX’). Todos los códigos tienen 2 caracteres
  • Guardar el sexo (‘F’, ‘M’). Todos tienen 1 caracter
  • Guardar el prefijo telefónico (‘021’, ‘022’, ‘028’, ‘0293’). Todos los prefijos tienen 3 caracteres ó 4 caracteres

Ejemplos donde se debería usar VARCHAR

  • Guardar el nombre de la persona (‘ANA PAULA’, ‘CYNTHIA ELIZABETH’, ‘PATRICIA ADRIANA’). La cantidad de caracteres es variable
  • Guardar la dirección del proveedor (‘COLÓN 1234’, ‘HERNANDARIAS 3455’, ‘ESTADOS UNIDOS 56789’). La cantidad de caracteres es variable

Artículo relacionado:

El índice 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