Tablas temporales de postgresql

Almacenar datos en tablas temporales de PostgreSQL  en lugar de manipular o trabajar con las tablas permanentes puede ser muy util en tus tareas diarias. Supongamos que deseas manipular la estructura de una tabla pero no tienes permisos DDL o DML a una tabla, tu solucion hacer una tabla temporal para que te permita hacer lo que gustes.

PostgreSQL  posee dos tipos de tablas temporales que pueden ser muy útiles. Este articulo te muestra las como crear estas tablas y recomendaciones que deberias tomar en cuenta para su uso.

Que son las tablas temporales de postgresql ?

Las tablas temporales de PostgreSQL son muy distintas a las usadas comun mente, debido a que su vida util es finita. Como se crea es muy parecida a las tablas creadas con la clausula Create Table, para tu base de datos y tus procedimientos deberias conocer estas tablas.

Una tabla temporal, es una tabla que se crea en memoria cuya vida util esta limitada por la transaccion ejecutada. PostgreSQL elimina automáticamente las tablas temporales al final de una sesión o transacción.

Tipos de tablas temporales de postgresql

En PostgreSQL existen dos tipos de tablas temporales:

1. Tablas temporales locales: Son tablas temporales que sólo están disponibles dentro de la sesión actual y son automáticamente eliminadas al final de la sesión.
2. Tablas temporales globales: Son tablas temporales que están disponibles en todas las sesiones y persisten hasta que son explícitamente eliminadas.

En ambos tipos de tablas temporales, los datos son automáticamente eliminados al final de la transacción, a menos que se especifique lo contrario mediante la cláusula ON COMMIT.

Sintaxis general

La sintaxis general para crear una tabla temporal en PostgreSQL  es la siguiente:

sqlCopy codeCREATE TEMPORARY TABLE [IF NOT EXISTS] temp_table_name (
  column_name1 data_type1 [CONSTRAINT constraint_name1],
  column_name2 data_type2 [CONSTRAINT constraint_name2],
  ...
) [ON COMMIT {DELETE ROWS | PRESERVE ROWS}];

Donde:

• IF NOT EXISTS es opcional y se usa para evitar la creación de una tabla temporal si ya existe una tabla con el mismo nombre.
• temp_table_name es el nombre de la tabla temporal.
• column_name es el nombre de la columna y data_type es el tipo de datos de la columna.
• CONSTRAINT es opcional y se usa para agregar restricciones a la columna.
• ON COMMIT especifica si los datos deben ser preservados o eliminados al final de la transacción. Las opciones son DELETE ROWS o PRESERVE ROWS. Por defecto, los datos son eliminados al final de la transacción.

Ejemplos de tablas temporales

Aquí hay un ejemplo de código para crear y utilizar ambos tipos de tablas temporales en PostgreSQL:

1. Tabla temporal local:

-- Creación de una tabla temporal local
BEGIN;
CREATE TEMPORARY TABLE temp_table (
  id serial primary key,
  data text
);

-- Inserción de datos en la tabla temporal
INSERT INTO temp_table (data) VALUES ('Datos de prueba 1');
INSERT INTO temp_table (data) VALUES ('Datos de prueba 2');

-- Consulta de datos de la tabla temporal
SELECT * FROM temp_table;

-- Finalización de la transacción y eliminación de la tabla temporal
COMMIT;

2. Tabla temporal global:

-- Creación de una tabla temporal global
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_global (
  id serial primary key,
  data text
) ON COMMIT PRESERVE ROWS;

-- Inserción de datos en la tabla temporal
INSERT INTO temp_table_global (data) VALUES ('Datos de prueba 1');
INSERT INTO temp_table_global (data) VALUES ('Datos de prueba 2');

-- Consulta de datos de la tabla temporal
SELECT * FROM temp_table_global;

-- Eliminación de la tabla temporal global
DROP TABLE temp_table_global;

Como pudismo ver en los ejemplos anteriores las diferencia entre una tabla temporal de session y de transaccion esta indicada al crear la tabla con el comando On Commit Preserve Rows;

Recomendaciones para su uso

Aquí hay algunas recomendaciones para trabajar con tablas temporales en PostgreSQL :

1. Utilice tablas temporales locales para tareas que requieren un rendimiento máximo, ya que los datos se almacenan en memoria.
2. Utilice tablas temporales globales para tareas que requieren persistencia de los datos más allá de la sesión actual.
3. Evite usar tablas temporales en situaciones en las que se esperan grandes cantidades de datos, ya que esto puede agotar la memoria disponible.
4. Considera el uso de índices en tablas temporales si necesitas realizar búsquedas y filtrados de datos de manera eficiente.
5. Mantén un control riguroso sobre la eliminación de tablas temporales para evitar consumir recursos innecesarios y mejorar la gestión de memoria.
6. Monitoriza regularmente la memoria y otros recursos del sistema para detectar cualquier problema o cuello de botella relacionado con el uso de tablas temporales.

En conclusion

En conclusión, las tablas temporales en PostgreSQL utilizan memoria del servidor y también pueden utilizar disco, dependiendo del tamaño y la cantidad de datos en la tabla. Además, el uso de tablas temporales puede afectar el rendimiento del sistema si se utiliza inadecuadamente, por ejemplo, si se crean demasiadas tablas temporales o si se almacenan grandes cantidades de datos en ellas. Es importante tener en cuenta estos factores y utilizar tablas temporales de manera adecuada para obtener el mejor rendimiento y evitar problemas de memoria y recursos en el sistema.