PostgreSQL의 계산 / 계산 / 가상 / 파생 열

PostgreSQL의 계산 / 계산 / 가상 / 파생 열

---

PostgreSQL은 MS SQL Server와 같은 계산 / 계산 열을 지원합니까? 문서에서 아무것도 찾을 수 없지만이 기능이 다른 많은 DBMS에 포함되어 있기 때문에 뭔가 빠질 수 있다고 생각했습니다.

예 : http://msdn.microsoft.com/en-us/library/ms191250.aspx

---

SQL 표준에 정의되어 있고 DB2, MySQL 및 Oracle을 포함한 일부 RDBMS에서 구현 된대로 Postgres 11 생성 열 까지 지원되지 않습니다. SQL Server 의 유사한 "계산 된 열" 도 아닙니다 .

STORED생성 된 열은 Postgres 12에 도입되었습니다 . 간단한 예 :

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

db <> 여기 바이올린

VIRTUAL생성 된 열은 다음 반복과 함께 올 수 있습니다. 관련 :

• 함수 호출에 대한 속성 표기법이 오류를 제공함

그때까지는 가상 생성 열과 매우 유사하게 보이고 작동 하는 속성 표기법 ( )을 사용하여 함수로VIRTUAL 생성 된 열을 에뮬레이션 할 수 있습니다 . 그것은 역사적인 이유로 Postgres에 존재하는 약간의 구문 이상이며 사건에 적합합니다. 이 관련 답변에는 코드 예제가 있습니다 .tbl.col

• 공통 쿼리를 열로 저장 하시겠습니까?

그러나 표현식 (열처럼 보임)은에 포함되지 않습니다 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를 수동으로 관리 할 수 ​​있습니다.

---

그래 넌 할수있어!! 솔루션은 쉽고 안전하며 성능이 뛰어나야합니다.

저는 postgresql을 처음 접했지만 뷰 와 쌍을 이루는 표현식 index 를 사용하여 계산 된 열을 만들 수있는 것 같습니다 (보기는 선택 사항이지만 삶을 조금 더 쉽게 만듭니다).

내 계산이라고 가정 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;

Now any queries using some_table_augmented will be able to use some_string_field_md5 without worrying about how it works..they just get good performance. The view doesn't copy any data from the original table, so it is good memory-wise as well as performance-wise. Note however that you can't update/insert into a view, only into the source table, but if you really want, I believe you can redirect inserts and updates to the source table using rules (I could be wrong on that last point as I've never tried it myself).

Edit: it seems if the query involves competing indices, the planner engine may sometimes not use the expression-index at all. The choice seems to be data dependant.

---

One way to do this is with a trigger!

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

The trigger is fired before the row is updated or inserted. It changes the field that we want to compute of NEW record and then it returns that record.

---

PostgreSQL 12 supports generated columns:

PostgreSQL 12 Beta 1 Released!

Generated Columns

PostgreSQL 12 allows the creation of generated columns that compute their values with an expression using the contents of other columns. This feature provides stored generated columns, which are computed on inserts and updates and are saved on disk. Virtual generated columns, which are computed only when a column is read as part of a query, are not implemented yet.

---

Generated Columns

A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables.

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

db<>fiddle demo

---

I have a code that works and use the term calculated, I'm not on postgresSQL pure tho we run on PADB

here is how it's used

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    ;

---

Well, not sure if this is what You mean but Posgres normally support "dummy" ETL syntax. I created one empty column in table and then needed to fill it by calculated records depending on values in row.

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

1. It is so dummy I suspect it is not what You are looking for.
2. Obviously it is not dynamic, you run it once. But no obstacle to get it into trigger.

---

A lightweight solution with Check constraint:

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

참고URL : https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql