Вычисляемые/вычисляемые/виртуальные/производные столбцы в PostgreSQL

Ответ 1

Сгенерированные до Postgres 11 столбцы не поддерживаются - как определено в стандарте SQL и реализовано некоторыми СУБД, включая DB2, MySQL и Oracle. Ни аналогичных "вычисляемых столбцов" SQL Server.

STORED сгенерированные столбцы представлены в Postgres 12. Тривиальный пример:

CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

дБ <> скрипка здесь

VIRTUAL сгенерированные столбцы могут идти со следующей итерацией. Связанные с:

• Нотация атрибута для вызова функции дает ошибку

До этого вы можете эмулировать сгенерированные VIRTUAL столбцы с помощью функции с использованием нотации атрибутов (tbl.col), которая выглядит и работает почти как виртуальный сгенерированный столбец. Это немного странная синтаксическая особенность, которая существует в Postgres по историческим причинам и подходит для случая. Этот связанный ответ имеет примеры кода:

• Сохранить общий запрос как столбец?

Однако выражение (похожее на столбец) не включено в SELECT * FROM tbl. Вы всегда должны перечислять это явно.

Может также поддерживаться соответствующим индексом выражения - при условии, что функция IMMUTABLE. Подобно:

CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

альтернативы

В качестве альтернативы вы можете реализовать аналогичную функциональность с VIEW, опционально в сочетании с индексами выражений. Тогда SELECT * может включать сгенерированный столбец.

Вычисляемые "сохраненные" (STORED) столбцы могут быть реализованы с помощью триггеров функционально идентичным способом.

Материализованные представления являются тесно связанной концепцией, реализованной начиная с Postgres 9.3.
В более ранних версиях можно управлять MV вручную.

Ответ 2

ДА, вы можете! Решение должно быть простым, безопасным и эффективным...

Я новичок в postgresql, но, похоже, вы можете создавать вычисленные столбцы, используя индекс выражения, в паре с view (представление необязательно, но делает жизнь немного проще).

Предположим, что мое вычисление md5(some_string_field), тогда я создаю индекс как:

CREATE INDEX some_string_field_md5_index ON some_table(MD5(some_string_field));

Теперь любые запросы, действующие на md5(some_string_field), будут использовать индекс, а не вычислять его с нуля. Например:

SELECT MAX(some_field) FROM some_table GROUP BY MD5(some_string_field);

Вы можете проверить это с помощью explain.

Однако на этом этапе вы полагаетесь на пользователей таблицы, которые точно знают, как построить столбец. Чтобы облегчить жизнь, вы можете создать VIEW в расширенной версии исходной таблицы, добавив в вычисляемое значение новый столбец:

CREATE VIEW some_table_augmented AS 
   SELECT *, MD5(some_string_field) as some_string_field_md5 from some_table;

Теперь любые запросы, использующие some_table_augmented, смогут использовать some_string_field_md5, не беспокоясь о том, как это работает. Они просто получают хорошую производительность. В представлении не копируются данные из исходной таблицы, поэтому он хорош как по памяти, так и по производительности. Обратите внимание, однако, что вы не можете обновлять/вставлять в представление только в исходную таблицу, но если вы действительно этого хотите, я считаю, что вы можете перенаправить вставки и обновления в исходную таблицу, используя правила (я мог ошибаться в этом последнем пункте, поскольку я никогда не пробовал это сам).

Изменить: похоже, если запрос включает конкурирующие индексы, механизм планировщика иногда может вообще не использовать индекс выражения. Выбор, по-видимому, зависит от данных.

Ответ 3

Один из способов сделать это - с помощью триггера!

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();

Триггер запускается перед обновлением или вставкой строки. Он меняет поле, которое мы хотим вычислить в записи NEW, а затем возвращает эту запись.

Ответ 4

PostgreSQL 12 поддерживает сгенерированные столбцы:

Выпущена PostgreSQL 12 Beta 1!

Сгенерированные столбцы

PostgreSQL 12 позволяет создавать сгенерированные столбцы, которые вычисляют их значения с помощью выражения, используя содержимое других столбцов. Эта функция обеспечивает сохраненные сгенерированные столбцы, которые вычисляются при вставках и обновлениях и сохраняются на диске. Виртуальные сгенерированные столбцы, которые вычисляются только тогда, когда столбец читается как часть запроса, еще не реализованы.

Сгенерированные столбцы

Сгенерированный столбец - это специальный столбец, который всегда вычисляется из других столбцов. Таким образом, это для столбцов, что представление для таблиц.

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) STORED
);

ДБ <> Fiddle Demo

Ответ 5

У меня есть код, который работает и использует термин, рассчитанный, я не на postgresSQL, который мы запускаем на PADB

вот как он используется

create table some_table as
    select  category, 
            txn_type,
            indiv_id, 
            accum_trip_flag,
            max(first_true_origin) as true_origin,
            max(first_true_dest ) as true_destination,
            max(id) as id,
            count(id) as tkts_cnt,
            (case when calculated tkts_cnt=1 then 1 else 0 end) as one_way
    from some_rando_table
    group by 1,2,3,4    ;

Ответ 6

Ну, не уверен, что это то, что Вы имеете в виду, но Posgres обычно поддерживает "фиктивный" синтаксис ETL. Я создал один пустой столбец в таблице, а затем нужно было заполнить его вычисляемыми записями в зависимости от значений в строке.

UPDATE table01
SET column03 = column01*column02; /*e.g. for multiplication of 2 values*/

1. Это так глупо, я подозреваю, что это не то, что Вы ищете.
2. Очевидно, что он не динамический, вы запускаете его один раз. Но нет никаких препятствий, чтобы получить его в спусковой крючок.

Ответ 7

Легкое решение с ограничением проверки:

CREATE TABLE example (
    discriminator INTEGER DEFAULT 0 NOT NULL CHECK (discriminator = 0)
);