Überlappung Zeitbereich

Anwendungen & Erweiterungen SQL
1

Hallo zusammen

Nehmen wir einmal folgende Tabelle als Basis:

CREATE TABLE public.tab01 (
  id           serial NOT NULL,
  start_event  timestamp without time zone,
  end_event    timestamp without time zone,
  /* Keys */
  CONSTRAINT tab01_pkey
    PRIMARY KEY (id),
  /* Checks */
  CONSTRAINT check_start_after_end
    CHECK (start_event < end_event)
) WITH (
    OIDS = FALSE
  );

Ist Folgendes möglich: Bei Anlegen/Ändern eines Datensatzes soll überprüft werden, ob es beim Zeitbereich zu einer Überlappung mit einem bestehenden Datensatz kommt.

2

ja, verwende dazu bitte timestamprange. Damit kannst Du überlappende Einträge verhindern.

test=*# create table ped(id serial, von_bis tsrange, exclude using gist(von_bis with &&));
CREATE TABLE
test=*# insert into ped(von_bis) values ('[2019-01-01 10:00:00,2019-01-02 11:00:00)');
INSERT 0 1
test=*# insert into ped(von_bis) values ('[2019-02-01 10:00:00,2019-02-02 11:00:00)');
INSERT 0 1
test=*# insert into ped(von_bis) values ('[2019-01-01 11:00:00,2019-01-01 15:00:00)');
FEHLER:  kollidierender Schlüsselwert verletzt Exclusion-Constraint »ped_von_bis_excl«
DETAIL:  Schlüssel (von_bis)=(["2019-01-01 11:00:00","2019-01-01 15:00:00")) kollidiert mit vorhandenem Schlüssel (von_bis)=(["2019-01-01 10:00:00","2019-01-02 11:00:00")).

Du kannst auch sowas machen:

test=# create extension btree_gist;
CREATE EXTENSION
test=*# create table hotel (zimmer int, von_bis daterange, exclude using  gist(zimmer with =, von_bis with &&));
CREATE TABLE
test=*# insert into hotel values(1, '[2019-01-10, 2019-01-20)');
INSERT 0 1
test=*# insert into hotel values(2, '[2019-01-10, 2019-01-20)');
INSERT 0 1
test=*# insert into hotel values(2, '[2019-01-15, 2019-01-25)');
FEHLER:  kollidierender Schlüsselwert verletzt Exclusion-Constraint »hotel_zimmer_von_bis_excl«
DETAIL:  Schlüssel (zimmer, von_bis)=(2, [2019-01-15,2019-01-25)) kollidiert mit vorhandenem Schlüssel (zimmer, von_bis)=(2, [2019-01-10,2019-01-20)).
test=*#

beachte die extra extension hierbei.

3

Dein zweites Beispiel

test=# create extension btree_gist;
CREATE EXTENSION
test=*# create table hotel (zimmer int, von_bis daterange, exclude using  gist(zimmer with =, von_bis with &&));
CREATE TABLE
test=*# insert into hotel values(1, '[2019-01-10, 2019-01-20)');
INSERT 0 1
test=*# insert into hotel values(2, '[2019-01-10, 2019-01-20)');
INSERT 0 1
test=*# insert into hotel values(2, '[2019-01-15, 2019-01-25)');
FEHLER:  kollidierender Schlüsselwert verletzt Exclusion-Constraint »hotel_zimmer_von_bis_excl«
DETAIL:  Schlüssel (zimmer, von_bis)=(2, [2019-01-15,2019-01-25)) kollidiert mit vorhandenem Schlüssel (zimmer, von_bis)=(2, [2019-01-10,2019-01-20)).
test=*#

setzt voraus, dass ich einen zweiten Datensatz für "zimmer’ erstelle. Bei mir jedoch wäre ‘zimmer’ immer eindeutig, unabhängig vom Zeitbereich.

Dein erstes Beispiel werde ich noch ausprobieren, habe bis dato keine Erfahrungen mit ‘tsrange’. Wäre das Vorgehen mit dem Spaltentyp ‘tsrange’ generell meinem Ansatz mit zwei Spalten (‘start_event’, ‘end_event’) vorzuziehen. Ich frage deshalb, da ich irgendwann die Daten mittels Skript aus der Datenbank ziehen will und dort schon eine Austeilung bräuchte. Aber ich vermute einmal, dass sich aus einem tsrange-Spaltentyp in einer Abfrage sehr einfach zwei ‘timestamp without time zone’-Datentypen generieren lassen.

4

ja, geht auch:

test=*# create table ped2 (von date, bis date, exclude using gist(daterange(von, bis,'[)') with &&));
CREATE TABLE
test=*# 
test=*# 
test=*# insert into ped2 values ('2019-01-01','2019-01-10');
INSERT 0 1
test=*# insert into ped2 values ('2019-01-15','2019-01-25');
INSERT 0 1
test=*# insert into ped2 values ('2019-01-18','2019-01-22');
FEHLER:  kollidierender Schlüsselwert verletzt Exclusion-Constraint »ped2_daterange_excl«
DETAIL:  Schlüssel (daterange(von, bis, '[)'::text))=([2019-01-18,2019-01-22)) kollidiert mit vorhandenem Schlüssel (daterange(von, bis, '[)'::text))=([2019-01-15,2019-01-25)).
test=*#
5

und ja, auch aus einem tsrange oder daterange kannst Du mit lower() und upper() die Werte holen.

6

Klasse Informationen. Herzlichen Dank!!

Ich vermute einmal, dass die Zeichen ‘[)’ die Grenzen des Intervalls bedeuten. Hast Du einen Tipp, wo das alles dokumentiert ist?

7

Dir scheint bis jetzt die Doku entgangen zu sein, oder?

https://www.postgresql.org/docs/current/rangetypes.html

Wohin kann ich nun die Rechnung schicken?

8

Dir scheint bis jetzt die Doku entgangen zu sein, oder?

Ich ahnte nicht, dass die Beschreibung der Erweiterungen auch in der Postgres-Dokumentation zu finden sind.

Wohin kann ich nun die Rechnung schicken?

Wer viel gibt, wird auch viel bekommen - oder so ähnlich. Falls Du mal in meiner Nähe (Grenze D/CH) bist, bin ich selbstverständlich bereit, die Kosten eines Abendessens zu übernehmen. :slight_smile:

9

hehehe, klingt nach einer Einladung. Mal sehen … ansonsten war die Frage eher ein Joke.

10

ansonsten war die Frage eher ein Joke.

Meine Einladung war kein Joke. :slight_smile:

Bis dato bleibt mir nur ein Dankeschön! Ich weiss dein Wissen und den Aufwand zu schätzen.