[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생성 된 열은 다음 반복 중 하나와 함께 제공 될 수 있습니다. (아직 Postgres 13에는 없습니다).

관련 :


그때까지는 가상 생성 열과 매우 유사하게 보이고 작동 하는 속성 표기법 ( )을 사용하여 함수로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;

이제 사용하는 모든 쿼리 는 작동 방식에 대해 걱정하지 않고 some_table_augmented사용할 수 있습니다 some_string_field_md5. 그냥 좋은 성능을 얻습니다. 뷰는 원래 테이블의 데이터를 복사하지 않으므로 성능 측면뿐만 아니라 메모리 측면에서도 좋습니다. 참고 그러나 당신은 단지 소스 테이블에보기로 / 삽입, 업데이트 할 수 있지만, 당신이 정말로 원하는 경우, 당신이 사용하는 소스 테이블에 삽입 및 업데이트 리디렉션 할 수 있다고 생각 규칙을 (내가 마지막 포인트로에 잘못 될 수 나는 그것을 직접 시도한 적이 없다).

편집 : 쿼리에 경쟁 인덱스가 포함되어있는 경우 플래너 엔진이 때때로 표현식 인덱스를 전혀 사용하지 않을 수 있습니다. 선택은 데이터에 따라 달라집니다.


답변

이를 수행하는 한 가지 방법은 방아쇠를 사용하는 것입니다!

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레코드 를 계산하려는 필드를 변경 한 다음 해당 레코드를 반환합니다.


답변

PostgreSQL 12는 생성 된 열을 지원합니다.

PostgreSQL 12 베타 1 출시!

생성 된 열

PostgreSQL 12에서는 다른 열의 내용을 사용하는 표현식으로 값을 계산하는 생성 된 열을 생성 할 수 있습니다. 이 기능은 삽입 및 업데이트시 계산되고 디스크에 저장되는 저장된 생성 열을 제공합니다. 쿼리의 일부로 열을 읽을 때만 계산되는 가상 생성 열은 아직 구현되지 않았습니다.


생성 된 열

생성 된 열은 항상 다른 열에서 계산되는 특수 열입니다. 따라서 열에 대한보기가 테이블에 대한 것입니다.

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

db <> 바이올린 데모


답변

글쎄, 이것이 당신이 의미하는 바인지 확실하지 않지만 Posgres는 일반적으로 “더미”ETL 구문을 지원합니다. 테이블에 하나의 빈 열을 만든 다음 행의 값에 따라 계산 된 레코드로 채워야했습니다.

UPDATE table01
SET column03 = column01*column02; /*e.g. for multiplication of 2 values*/
  1. 당신이 찾고있는 것이 아니라고 생각합니다.
  2. 분명히 동적이 아니며 한 번 실행합니다. 그러나 그것을 방아쇠에 넣는 데 장애물은 없습니다.

답변

나는 작동하고 계산 된 용어를 사용하는 코드가 있습니다. 나는 우리가 PADB에서 실행하는 순수한 postgresSQL이 아닙니다.

사용 방법은 다음과 같습니다.

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    ;


답변

체크 제약 조건이있는 경량 솔루션 :

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