Columnas calculadas/calculadas/virtuales/derivadas/generadas en PostgreSQL
¿ PostgreSQL admite columnas calculadas como MS SQL Server?
No puedo encontrar nada en los documentos, pero la función está incluida en muchos otros DBMS, por lo que tal vez me falta algo.
Postgres 12 o más reciente
STOREDLas columnas generadas se introducen con Postgres 12, tal como se define en el estándar SQL y se implementan en algunos RDBMS, incluidos DB2, MySQL y Oracle. O las "columnas calculadas" similares de SQL Server.
Ejemplo trivial:
CREATE TABLE tbl (
int1 int
, int2 int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);
violín
VIRTUALLas columnas generadas pueden venir con una de las próximas iteraciones. (Aún no en Postgres 16).
Relacionado:
- La notación de atributos para la llamada a función da error
Postgres 11 o mayor
No se admiten hasta 11 "columnas generadas" de Postgres.
Puede emular VIRTUALcolumnas generadas con una función que utiliza notación de atributos ( tbl.col) que se ve y funciona de manera muy similar a una columna generada virtual . Esto es un poco extraño en la sintaxis que existe en Postgres por razones históricas y resulta que encaja en el caso. Esta respuesta relacionada tiene ejemplos de código :
- ¿Almacenar consulta común como columna?
Sin embargo , la expresión (que parece una columna) no está incluida en a SELECT * FROM tbl. Siempre debes enumerarlo explícitamente.
También se puede admitir con un índice de expresión coincidente , siempre que la función sea IMMUTABLE. Como:
CREATE FUNCTION col(tbl) ... AS ... -- your computed expression here
CREATE INDEX ON tbl(col(tbl));
Alternativas
Alternativamente, puede implementar una funcionalidad similar con VIEW, opcionalmente junto con índices de expresión. Luego SELECT *puede incluir la columna generada.
Las columnas calculadas "persistentes" ( STORED) se pueden implementar con activadores de una manera funcionalmente equivalente.
Las vistas materializadas son un concepto relacionado, implementado desde Postgres 9.3 .
En versiones anteriores se podían gestionar los MV manualmente.
¡¡Sí tu puedes!! La solución debe ser fácil, segura y eficaz...
Soy nuevo en postgresql, pero parece que puedes crear columnas calculadas usando un índice de expresión , junto con una vista (la vista es opcional, pero hace la vida un poco más fácil).
Supongamos que mi cálculo es md5(some_string_field), luego creo el índice como:
CREATE INDEX some_string_field_md5_index ON some_table(MD5(some_string_field));
Ahora, cualquier consulta que actúe MD5(some_string_field)utilizará el índice en lugar de calcularlo desde cero. Por ejemplo:
SELECT MAX(some_field) FROM some_table GROUP BY MD5(some_string_field);
Puedes comprobar esto con explicación .
Sin embargo, en este punto usted confía en que los usuarios de la tabla sepan exactamente cómo construir la columna. Para hacer la vida más fácil, puede crear una VIEWversión aumentada de la tabla original, agregando el valor calculado como una nueva columna:
CREATE VIEW some_table_augmented AS
SELECT *, MD5(some_string_field) as some_string_field_md5 from some_table;
Ahora, cualquier consulta que utilice some_table_augmentedpodrá utilizarse some_string_field_md5sin preocuparse por cómo funciona... simplemente obtienen un buen rendimiento. La vista no copia ningún dato de la tabla original, por lo que es buena tanto en términos de memoria como de rendimiento. Sin embargo, tenga en cuenta que no puede actualizar/insertar en una vista, solo en la tabla fuente, pero si realmente lo desea, creo que puede redirigir las inserciones y actualizaciones a la tabla fuente usando reglas (podría estar equivocado en ese último punto ya que Yo nunca lo he probado).
Editar: parece que si la consulta involucra índices competitivos, es posible que a veces el motor del planificador no use el índice de expresión en absoluto. La elección parece depender de los datos.
¡Una forma de hacerlo es con un disparador!
CREATE TABLE computed(
one SERIAL,
two INT NOT NULL
);
CREATE OR REPLACE FUNCTION computed_two_trg()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $BODY$
BEGIN
NEW.two = NEW.one * 2;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER computed_500
BEFORE INSERT OR UPDATE
ON computed
FOR EACH ROW
EXECUTE PROCEDURE computed_two_trg();
El disparador se activa antes de que se actualice o inserte la fila. Cambia el campo que queremos calcular del NEWregistro y luego devuelve ese registro.
PostgreSQL 12 admite columnas generadas:
¡PostgreSQL 12 Beta 1 lanzado!
Columnas generadas
PostgreSQL 12 permite la creación de columnas generadas que calculan sus valores con una expresión utilizando el contenido de otras columnas. Esta característica proporciona columnas generadas almacenadas, que se calculan en inserciones y actualizaciones y se guardan en el disco. Las columnas virtuales generadas, que se calculan sólo cuando una columna se lee como parte de una consulta, aún no están implementadas.
Columnas generadas
Una columna generada es una columna especial que siempre se calcula a partir de otras columnas. Por lo tanto, es para las columnas lo que una vista es para las tablas.
CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) STORED
);
demostración de db<>violín
