[SQL] PostgreSQL의 배제에 함께 인접한 / 중첩 항목 예방

PostgreSQL의 배제에 함께 인접한 / 중첩 항목 예방

나는 상점의 PostgreSQL 9.2.4 날짜 / 시간 범위를 임의의 데이터베이스를 만드는 오전. I 날짜 / 시간 범위가 겹치지, 비 인접하도록 강제이 데이터베이스에 제한을 두려는 (범위 인접한 두 번의 연속적인 범위로 표현 될 수 있기 때문에).

이를 위해, 나는이 요지 인덱스 제약 조건을 배제 사용하고 있습니다. 여기에 내가 현재 가지고있는 제약 조건은 다음과 같습니다

ADD CONSTRAINT overlap_exclude EXCLUDE USING GIST (
    box(
        point (
            extract(EPOCH FROM "from") - 1,
            extract(EPOCH FROM "from") - 1
        ),
        point (
            extract(EPOCH FROM "to"),
            extract(EPOCH FROM "to")
        )
    ) WITH &&
);

및 TIME ZONE없이 모두 TIMESTAMP, 그리고 날짜 / UTC에 저장 시간 (내 응용 프로그램에서이 컬럼에 데이터를 삽입하기 전에 UTC로 변환하고, 나는 "UTC"내 데이터베이스의 시간대 설정을 가지고는 postgresql.conf에서에서 열 ).

내가있을 수 있습니다 생각하고 문제는, 그러나,이 제약 1 초보다 작은 시간 단위가없는 것을 (잘못된) 가정을하고 있다는 것입니다.

내가 저장하고있는 특정 데이터를 들면, 나는 단지 두 번째 해상도를 필요, 저것을 주목할 필요가있다. 그러나, 나는 내가 여전히 SQL 유형 타임 스탬프 이후이 처리해야 할 수도 있다고 생각하고 timestamptz 1 초에 비해 모두 높은 해상도입니다.

내 질문 중 하나입니다 : 단순히 가정 초 해상도에 문제가, 내가 다루는이 제약 조건을 변경할 수있는 방법이있는 경우 즉, 내 모든 애플리케이션의 요구를 (또는 원), 또는 이후 분수 - 중 - 초 A의 강력한 방법?

해결법

1. ==============================

   1.다양한 유형이 포함되거나 배제 될 수있는 하부 및 상부 경계의 구성. 일반적인 사용 사례 (및 범위 유형에 대한 기본값) 낮은 포함하고 상한 제외하는 것입니다.

   다양한 유형이 포함되거나 배제 될 수있는 하부 및 상부 경계의 구성. 일반적인 사용 사례 (및 범위 유형에 대한 기본값) 낮은 포함하고 상한 제외하는 것입니다.

   중복되는 범위를 제외하면 분명한 것 같다. 매뉴얼에 좋은 코드 예제가있다

   또한, 인접 연산자를 사용하는 다른 제외 제약 조건을 만들 - | - 인접 항목도 제외합니다. GIN 현재이 지원되지 않습니다으로 모두 GIST 인덱스를 기반으로해야합니다.

   청결을 유지하기 위해, 나는)를 포함하여 낮은 및 다양한 기능을 사용하여 CHECK 제약 조건을 가진 모든 항목에 대해) 상단을 제외한 경계를 (강제 것입니다 :

   CREATE TABLE tbl (
      tbl_id serial PRIMARY KEY
    , tsr tsrange
    , CONSTRAINT tsr_no_overlap  EXCLUDE USING gist (tsr WITH &&)
    , CONSTRAINT tsr_no_adjacent EXCLUDE USING gist (tsr WITH -|-)
    , CONSTRAINT tsr_enforce_bounds CHECK (lower_inc(tsr) AND NOT upper_inc(tsr))
   );

   DB <> 바이올린 여기 (올드 SQL 바이올린)

   불행하게도,이 논리적으로, 하나는 충분 모두 제외 제약 조건을 구현하기 위해 두 개의 동일한 GIST 인덱스를 작성합니다. 즉, (적어도 포스트 그레스 11까지) 현재 구현의 단점이 될 것으로 보인다.

2. ==============================

   2.당신은 9.2에서 소개 된 다양한 유형 제외 다시 작성할 수 있습니다. 더 나은 아직, 당신은 범위를 가진 두 개의 필드를 대체 할 수있다. 기본적으로 사용 사례에 금액을 예로 들어, 여기에 "범위에 제약"을 참조하십시오 :

   당신은 9.2에서 소개 된 다양한 유형 제외 다시 작성할 수 있습니다. 더 나은 아직, 당신은 범위를 가진 두 개의 필드를 대체 할 수있다. 기본적으로 사용 사례에 금액을 예로 들어, 여기에 "범위에 제약"을 참조하십시오 :

   http://www.postgresql.org/docs/current/static/rangetypes.html

3. ==============================

   3.넌 OK 존재를 고려 :

   넌 OK 존재를 고려 :

   select 
     extract ('epoch' from now())
     , extract ('epoch' from now()::timestamp(0))
   

from https://stackoverflow.com/questions/19504727/preventing-adjacent-overlapping-entries-with-exclude-in-postgresql by cc-by-sa and MIT license